Home » Vinsight API  »  Client Code Examples  »  VB Script Example

VB Script Example

' save this in a .vbs file and run it using cscript
' eg:
' cls&&cscript test.vbs


Dim xlsx: xlsx = "xlsx" ' "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Dim csv: csv = "text/csv"
Dim json: json = "application/json"

Dim domain: domain = "https://app.vinsight.net"
Dim apikey: apikey = "1234567890"

Public Function getWebRequest(method, URL, acceptType, data)
        
    Dim progID
	'progID = "MSXML2.XMLHTTP.3.0"
	'progID = "Msxml2.ServerXMLHTTP.6.0"
    progID = "WinHttp.WinHttpRequest.5.1"
	
    Dim xmlHttp: Set xmlHttp = CreateObject(progID)

    Dim async: async = False
    
    xmlHttp.Open method, domain & URL, async
    xmlHttp.setRequestHeader "User-Agent", progID
    xmlHttp.setRequestHeader "Accept", acceptType
    xmlHttp.setRequestHeader "Host", "applocal.vinsight.net"
    xmlHttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"

    xmlHttp.send(data)

    Set getWebRequest = xmlHttp

End Function

Dim accept: accept = xlsx

'example with query short enough to fit in url
'Dim odataQuery: odataQuery = ""
'Dim url: url = "/SalesOrderItems?api-key=" & apikey & "&$filter=Quantity gt 2"
'Dim resp: Set resp = getWebRequest("GET", url, accept, "")

'Example with long query, too long for url
'so POST query in body with "method=GET" in url to tunnel the http verb
Dim odataQuery: odataQuery = "$query=[" & _
    "{""key"":""$filter"",""value"":""(Order/InvoiceDate ge datetime'2017-10-31T11:00:00Z' and Order/InvoiceDate lt datetime'2017-11-30T10:59:59.999Z')" & _
    " and (StockItem/ProductCategories/Contains(';my-category:')) ""}," & _
    "{""key"":""$orderby"",""value"":""Quantity""}]"
Dim url: url = "/SalesOrderItems?method=GET&api-key=" & apikey
Dim resp: Set resp = getWebRequest("POST", url, accept, odataQuery)


'Print output
Set fso = CreateObject ("Scripting.FileSystemObject")
Set stdout = fso.GetStandardStream (1)
'Set stderr = fso.GetStandardStream (2)
stdout.WriteLine "Status: " & resp.Status
    

If resp.Status = 200 And accept = xlsx Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write resp.responseBody
    oStream.SaveToFile "c:\files\file.xlsx"
    oStream.Close
Else
   stdout.WriteLine "ResponseText: " & resp.responseText	
End If