I'm using Excel 2003 on Windows XP. All the code referring to HTML I tried to use or tweak just doesn't work because it is probably written for the newer versions.
What I am doing at this stage By clicking a link from Excel I'm sending a YouTube API request, which activates the browser (Google Chrome) where I get the result as text (JSON Data) on a webpage. I copy the content of the webpage and paste it into Excel which imports it in the format one line per cell. I have created a macro to 'extract' all the data, one property per column to a range and the arrays as separated text. The time the macro uses is about 2-3 seconds per file at the maximum of 50 entries which could probably be reduced since only a part of the data is useful to me and the option of copying several such files would also not be needed.
What I want to do
I want to bypass activating the browser and copying the JSON Data before pasting it into Excel. I've seen something similar done with JavaScript (Parse) but I don't know how I would use it in my case.
So, I actually have nothing to offer so far (It might have something to do with the Microsoft HTML Object Library, Innerhtml... WebQuery doesn't work.), but if someone could point me in the right direction, I would appreciate it and make the effort of investigating further.
I want to run a macro from Excel which will follow the hyperlink of a YouTube API request and paste the result (JSON Data) into column A of the ActiveSheet bypassing the browser.
Or To simplify: I want to paste the text from file https://www.w3.org/TR/PNG/iso_8859-1.txt to the ActiveSheet in Excel, or to a word document, or to a variant variable using VBA bypassing the browser. I have no clue where to start looking.
For example:
Sub Test()
Dim sUrl As String
sUrl = "https://www.w3.org/TR/PNG/iso_8859-1.txt"
With CreateObject("Msxml2.ServerXMLHTTP")
.Open "GET", sUrl, False
.send
Debug.Print .responseText
Debug.Print .getAllResponseHeaders
End With
End Sub