Using VBScript to interact with Apptio API and download response to TSV

Question

I'm currently trying to automate a feed from a source system (Apptio) to a destination system (PCM). I'm essentially trying to use the built in API to download a .tsv file to a shared drive location and then trigger an import job to process that file into PCM.

I'm a little stuck on calling the API and getting the file to download however and have tried a few approaches, one of which will work I think but isn't very reliable.

I have tried calling the following VBScript from CMD prompt using wscript.exe...

Dim oXMLHTTP
Dim oStream
dim urlstr

Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.3.0")

urlstr = "https://SITE.apptio.com/biit/api/v1.tsv?date=Apr%3AFY2013&dataPath=-%40CSITE.co.uk%253AService%2BCosting%2Bv2%2FReports%2F.DateGoesHere%2FCostModels%2FDefault%2F.View%253Atab%253AService%2BCosting%2F.View%253AApptio%2BSandbox%2F.View%253"

urlstr = urlstr & "AABM%2FABM%2BOutput%2F%21GROUPBY%255B%257BABM%2BOutput.Account%2BProcess%257D%252C%257BABM%2BOutput.Channel%257D%252C%257BABM%2BOutput.Product%257D%255D%2F.Summary%2F%21NEWCOLUMN%255B%257BLTM%2BFixed%257D%253DPreviousYear%28Fixed%29%255D%255B%253DCurrency%28%2524_%29%255D%2F%21NEWCOLUMN%255B%257BLTM%2BSemi%2BVariable%257D%253DPreviousYear%28Semi%2BVariable%29%255D%255B%253DCurrency%28%2524_%29%255D%2F%21NEWCOLUMN%255B%257BLTM%2BVariable%257D%253DPreviousYear%28Variable%29%255D%255B%253DCurrency%28%2524_%29%255D%2F%21NEWCOLUMN%255B%257BLTM%2BCost%257D%253DPreviousYear%28Cost%29%255D%255B%253DCurrency%28%2524_%29%255D%2F%21SORT%255B%257BLTM%2BCost%257D%257Cdesc%255D%2F%21LIMIT%255B0%252C2147483647%252Cadd_total%255D%2F%21LIMIT_COLUMNS%255B%257BABM%2BOutput.Account%2BProcess%257D%252C%257BABM%2BOutput.Channel%257D%252C%257BABM%2BOutput.Product%257D%252C%257BLTM%2BFixed%257D%252C%257BLTM%2BSemi%2BVariable%257D%252C%257BLTM%2BVariable%257D%252C%257BLTM%2BCost%257D%255D%255B%255D%255B%255D%255B'orderByIncludeList%255D"

oXMLHTTP.Open "GET", urlstr, False
oXMLHTTP.Send

If oXMLHTTP.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write oXMLHTTP.responseBody
    oStream.SaveToFile "\\csdatg08\FinanceTransformationFM\Transformation\Josh\ABM Transfer\Apptio\DataFeed\Output.tsv"
    oStream.Close
End If

However this gives me an error of:

(12, 1) msxml3.dll: Access is denied.

Which I presume is something to do with the fact I'm calling an API. I've tried using the URL of a graphic hosted on the same server instead and that will download the the specified location withotu a problem.

I've also tried pasting the URl directly into a browser. The URL prompts for user authentication (which I'd expect), but then the save as dialog box pops up which shows the URL is correct.

so.. I then tried a script to launch an instance of IE, allow the logon prompt, wait for the save as dialog and then send keys to save the file.

Whilst this does work - I don't trust it. I>E what happens if the process is run on different version of I.E, what happens is another save as dialogue box is open ....

So I'm looking to see if anybody knows of a better method before we go down the full blown SQL link route which will be much mroe expensive and probably excessive for what we are doing.

Thanks

(Second code as follows)

Dim objExplorer : Set objExplorer = CreateObject("InternetExplorer.Application") 
objExplorer.Navigate "https://SITE.apptio.com/biit/api/v1.tsv?date=Apr%3AFY2013&dataPath=-%40CSITE.co.uk%253AService%2BCosting%2Bv2%2FReports%2F.DateGoesHere%2FCostModels%2FDefault%2F.View%253Atab%253AService%2BCosting%2F.View%253AApptio%2BSandbox%2F.View%253AABM%2FABM%2BOutput%2F%21GROUPBY%255B%257BABM%2BOutput.Account%2BProcess%257D%252C%257BABM%2BOutput.Channel%257D%252C%257BABM%2BOutput.Product%257D%255D%2F.Summary%2F%21NEWCOLUMN%255B%257BLTM%2BFixed%257D%253DPreviousYear%28Fixed%29%255D%255B%253DCurrency%28%2524_%29%255D%2F%21NEWCOLUMN%255B%257BLTM%2BSemi%2BVariable%257D%253DPreviousYear%28Semi%2BVariable%29%255D%255B%253DCurrency%28%2524_%29%255D%2F%21NEWCOLUMN%255B%257BLTM%2BVariable%257D%253DPreviousYear%28Variable%29%255D%255B%253DCurrency%28%2524_%29%255D%2F%21NEWCOLUMN%255B%257BLTM%2BCost%257D%253DPreviousYear%28Cost%29%255D%255B%253DCurrency%28%2524_%29%255D%2F%21SORT%255B%257BLTM%2BCost%257D%257Cdesc%255D%2F%21LIMIT%255B0%252C2147483647%252Cadd_total%255D%2F%21LIMIT_COLUMNS%255B%257BABM%2BOutput.Account%2BProcess%257D%252C%257BABM%2BOutput.Channel%257D%252C%257BABM%2BOutput.Product%257D%252C%257BLTM%2BFixed%257D%252C%257BLTM%2BSemi%2BVariable%257D%252C%257BLTM%2BVariable%257D%252C%257BLTM%2BCost%257D%255D%255B%255D%255B%255D%255BorderByIncludeList%255D" 
objExplorer.ToolBar = 1 
objExplorer.StatusBar = 1 
objExplorer.Width = 800 
objExplorer.Height = 800 
objExplorer.Left = 1 
objExplorer.Top = 1 
objExplorer.Visible = 1 

wscript.echo "window open"
wscript.sleep 6000

Set wshShell = CreateObject("WScript.Shell") 

Do 
    ret = wshShell.AppActivate("File Download") 
    If ret = True Then 
        wshShell.SendKeys "%S" 
        Exit Do 
    End If 
    WScript.Sleep 500 
Loop 
wscript.echo "Save Selected"

Do 
    ret = wshShell.AppActivate("Save As") 
    If ret = True Then 
        wshShell.SendKeys "%S" 
        Exit Do 
    End If 
    WScript.Sleep 500 
Loop
wscript.echo "File Saved"

References so far:

how to handle IE Download dialog with VB Script? VBScript to detect an open messagebox and close it http://www.pctools.com/guides/scripting/detail/149/?act=reference