HI,
I need help to retrieve data from website API xml and input in to work sheet. my problem is the data is within 2-3 layer in REST API so I need to search through different layers to get my data, here is what I have:
https://***x.lims.com/api/v2/processes/ attcahed is the screen shot:
Have to retrieve data for all samples so it has to be dynamic. Basically I have about 20 UDF’s ( user defined fields) which I need to extract from API and its values and I have no idea how to do this?
any help willUDF fields.docxUDF fields.docx be appreciated.
Here is what i have done so far: its on a button, it retrieves data form one API and saves it to a local drive and then the call "test" retrieves data from file and puts it into sheet but I have long way to go..
if there is another way to bring data to sheet directly instead of saving it to file would be great!
Thanks so much for help!
Sub get_data(up_http, down_http)
'Sub get_data(up_http)
Dim xmlhttp: Set xmlhttp = CreateObject("msxml2.xmlhttp.6.0")
xmlhttp.Open "get", up_http, False
xmlhttp.Send
MsgBox xmlhttp.ResponseText
Dim fso: Set fso = CreateObject("scripting.filesystemobject")
Dim newfile: Set newfile = fso.createtextfile(down_http, True)
newfile.write (xmlhttp.ResponseText)
newfile.Close
Set newfile = Nothing
Set xmlhttp = Nothing
End Sub
Sub Button1_Click()
get_data "https://***x.lims.com/api/v2/artifacts/151C-1102PA1", "C:\Users\***xx\Documents\test.xml"
Call import
End Sub
-------------------------------------
Sub import()
ActiveWorkbook.XmlImport URL:="C:\Users\***x\Documents\***x.xml", _
ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")
End Sub