VBA - responseXML to a QueryTable?

[mmohon]

I'm using the API of a website to pull some data down. I get a good responseXML from it.

Is there a way I can drop that responseXML data into a querytable, just as if I was importing data from an xml file?

 

[Norie]

How exactly are you getting the responseXML?

Have you tried writing it to a file then using the 'normal' methods to import it as a query table?

 

[mmohon]

Set objhttp = CreateObject("MSXML2.ServerXMLHTTP")
   URL = my url
   objhttp.Open "GET", URL, False
   objhttp.setRequestHeader "ApiKey", mykey
   objhttp.send

From there I can do msgbox(responseText) and see what's returned. It's all the XML data. I was hoping I could just drop the responseXML into a table.

 

[Norie]

The only way I can see it being done with a query table would be if the response was written to a file.

By the way, what website/API are you using - I only have local XML files to test on so might be missing something really obvious.

 

[mmohon]

How could I drop that to a file then?

I could see:

Export to a file.xml
Import from the file into a query table
break connection
delete file.xml

Does that seem possible?

 

[Norie]

Just write it to a file using VBA File I/O methods.

Something like this perhaps.

              Dim FF As Long
 
       FF = FreeFile()
 
       Open "C:\apiresponse.xml" For Output As FF
            Write #FF, oXMLHTTP.responseXML.XML
       Close #FF

That will output responseXML to a file but I don't have anything to test it with, so I don't know if it's any use.

Can you post the website/API?