0

I'm trying to pull data from an excel file found on this website: https://www2.asx.com.au/listings/how-to-list/listing-requirements/foreign-entity-data

Which directs users to: https://www2.asx.com.au/content/dam/asx/documents/listings/foreign-entity-data/2021/asx-foreign-entity-report-20210930.xlsx

I've tried to use the following code to retrieve it, which I then plan to manipulate the data but it returns encoded / special characters:

Sub test()

Dim myURL As String
myURL = "https://www2.asx.com.au/content/dam/asx/documents/listings/foreign-entity-data/2021/asx-foreign-entity-report-20210930.xlsx"

Dim HttpReq As Object
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
HttpReq.Open "GET", myURL, False
HttpReq.send

Debug.Print (HttpReq.ResponseText)

End Sub

I'm trying to avoid opening/reading the file via a directory to the file download destination because it will be used by other users and would rather not have it downloaded every time to be used.

Could someone please explain why it returns in special characters and how I can fix it?

Many thanks Tom

Default001
  • 45
  • 6
  • You will need to [write it to a temp file](https://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer), manipulate the data then delete the temp file later on. You can save it to the temp folder (use `Environ("Temp")` to get the path). – Raymond Wu Oct 28 '21 at 05:10

0 Answers0