Open a Document in IE and be able to store it as a variable in vbs

Question

I am trying to write a vbs program where I navigate to a web page, click on a link that opens a file in excel, then run a macro in the excel file. I know how to navigate to a web page and click on a link. I need help figuring out how I store the excel file in a way that the vbs program can manipulate it.

Set IE = WScript.CreateObject("InternetExplorer.Application", "IE_")
IE.Visible = True
IE.Navigate ("https://www.whateverWebsite.com/")

Dim LinkHref    
LinkHref = "analyze" 'the key word that will be in the link to click
Dim a

For Each a In IE.Document.GetElementsByTagName("a") ' for every element whose tag name starts with 'a' for "a href" pull out its contents
    If InStr((a.GetAttribute("href")), LinkHref)>0 Then 'checks to see if the link that is set contains the string stored in LinkHref
        a.Click 'click the link
    End If
Next

Dim objExcel  
Set objExcel = CreateObject("Excel.Application")

Now how do I attach the excel file, that I opened with the link, to objExcel?

Any form of *manipulate it* will for all intents and purposes be done on a local copy, whether in memory, as a temp file in the Internet Termp Files folder or as a download. Are you trying to save it locally or PUT it back in an altered state? — , May 14 '15 at 16:49
I am trying to open the excel file, run a macro on the file, then save it back to the server. So it will not be saved on my local machine. — BlindingFog, May 14 '15 at 17:41
Suggest you look at the HTTP PUT verb. Good information in [PUT vs POST in REST](http://stackoverflow.com/questions/630453/put-vs-post-in-rest). — , May 14 '15 at 17:58
So what is the command in vbs to accomplish doing a POST, I think, so I can open the excel file and save it back to the server? — BlindingFog, May 14 '15 at 18:04
You are probably going to want to abandon the InternetExplorer.Application in favor of something like MSXML2.XMLHTTP60 in order to manipulate the [XMLHttpRequest object](https://msdn.microsoft.com/en-us/library/ms535874(v=vs.85).aspx). As an XMLHTTP object operates invisibly, you'll need a good tool like [Fiddler](http://www.telerik.com/download/fiddler) to see what you are doing. There is a wealth of relevant information on the [REST API](http://stackoverflow.com/questions/19553476/how-to-make-rest-call-with-vba-in-excel). — , May 14 '15 at 19:26

Ansgar Wiechers · Accepted Answer · 2015-05-15T14:54:44.893

You can attach to a running Excel instance via GetObject:

Set xl = GetObject(, "Excel.Application")

If you have several instances launched, that will only get you the first one, though. You'd have to terminate the first instance to get to the second one.

With that said, a better approach would be to have Excel open the URL directly:

Set xl = CreateObject("Excel.Application")
For Each a In IE.Document.GetElementsByTagName("a")
    If InStr(a.href, LinkHref) > 0 Then
        Set wb = xl.Workbooks.Open(a.href)
        Exit For
    End If
Next

Open a Document in IE and be able to store it as a variable in vbs

1 Answers1