IE Download

**Ripster** · 08-15-2011, 11:16 AM

VBA:

Dim sURL As String 
Set ie = New InternetExplorer 
Dim ieDoc As Object 
sURL = "https://.." 
 
ie.Visible = True 
ie.navigate sURL 
Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Loop 
     
    With ie.document.forms("logonForm") 
        .NQUser.Value = "user" 
        .NQPassword.Value = "password" 
        .submit 
    End With 
     
    Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Loop 
        Set ieDoc = ie.document 
        ieDoc.all(307).Click 
        Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Loop 
            ieDoc.all(1364).Click 
            Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Loop 
                ieDoc.all(1370).Click

VBA tags courtesy of www.thecodenet.com

This goes to the website, enters username & password, logs in, Clicks a button (307), clicks a download button (1364) and then clicks a button (1370) from a menu that comes up after clicking "Download".

The "Download" link that is clicked is javascript that creates a list with different choices. When you click one of the items on the list a new browser window opens and that opens a prompt to save, open, or cancel the download.

The problem I am having is when vba clicks the menu item a new page opens like its supposed to but it then promptly disappears without ever giving the option to save a file. I do not have a direct link to the file I want to download. My assumption is that the link is created dynamically based on previously selected options.

What can I do to prevent the new window from being closed and to save the file?

**stanl** · 08-15-2011, 02:01 PM

Hard to say w/out the html source. Does each menu option have it's own href? Is the jscript source a function in the document head or body? If you just put a pause message in your script right after choosing the menu option, does the pop-up not disappear.

I have handles urls like this in the past by spawning another script as a separate thread, let it look for the window title of the jscript pop-up, deal with it then close and return to the first script.

Another trick I have used is to create my IE object as InternetExplorer.Application.1 - then reference the Newwindow2 event and load the jscript window myself setting a second IE Object to that window.

**Ripster** · 08-18-2011, 06:42 AM

This is not my website so I unfortunately cannot change the code. There is jscript in the website header. There is also references to external jscripts. I can post the header if you would like but it is very long. I don't know javascript so reading it is only helpful to a point.

The html source for the item I am clicking to start the download is:

HTML Code:

<a class="NQWMenuItem" name="SectionElements" href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&amp;_scid=DsZsoFas5E8&amp;ViewID=d\x253adashboard\x257ep\x253aapnl74vr5ch30r06\x257er\x253a869hck781l4bksg9&amp;Action=Download&amp;SearchID=tf0ds3t0n47g9igkk3p3hv2nnm&amp;PortalPath=/shared/IR\x2520ITS\x2520Sourcing\x2520and\x2520Supply\x2520Chain/_portal/Supplier\x2520Dashboard&amp;Page=Supplier\x2520Open\x2520Orders&amp;ViewState=312b0nq6c4v0p9eeib7dlkif0a&amp;ItemName=R40GSS-\x2520Supplier\x2520Open\x2520Order\x2520Report&Format=mht&Extension=.xls'); return false">Download to Excel</a>

Creating a second IE tab to call the jscript directly is something I considered but I don't know how to do that. I was also wondering if there was a way to grab the url before IE navigated to it and then call that in VBA using the navigate function so it would be part of the IE object I declared. I am still fairly new to VBA so I don't know how to go about doing either of those things.

Thank you for your help! It is much appreciated.

**stanl** · 08-19-2011, 01:43 PM

My advice. Open the site in Firefox with the Firebug add-on: it will track and display the exact GET/POST to get the file. Then you open a WinHttp.WinHttpRequest.5.1 Object, then use an ADO Stream to save the file after performing the GET to the full URL Firebug displays (you probably have most of it in the source you posted). Sorry if you felt I mislead you earlier about opening 2 IE Objects. If you think about it all that is happening is the web page is collecting parameters and performing a GET to send you the data. Obviously the URL is sensitive or else you could post more information.

**Ripster** · 08-22-2011, 12:36 PM

I got the URL created through firebug and put it into a new browser tab only to get an error. After reading through the javascript it looks like before sending out the file it checks to see where the URL was called from and if it wasn't called from the parent page with the same session it gives a page saying that the session has expired. This is rather unfortunate. I'm at a bit of a loss on this one. I wish I knew why the page was closing without prompting for a download. Is it possible to directly control the program through shell instead of as an object in vba? I think the way it is created and controlled may be causing the abnormal behavior.

**stanl** · 08-23-2011, 07:17 AM

Quote:

Originally Posted by: Ripster


	I got the URL created through firebug

Are you using Firebug with Firefox or IE, because the IE plugin doesn't do half of what you need. If you open the URL in Firefox with Firebug in a separate window you shouls see a top menu ranging from console to net. You will be concentrating on the net tab and under that the POST options where Firebug tells you the specific parameters that are posted to the site. If one of those involves a JSESSIONID that is something you can get from the main page of the URL. I'm really in the dark here since I cannot test what you are trying to accomplish.

**Ripster** · 08-31-2011, 06:15 AM

Thank you for your help. I got it working. I've used firebug with firefox in the past but never used it for java. I'm very impressed. I got everything working.

**stanl** · 08-31-2011, 11:26 AM

Quote:

Originally Posted by: Ripster


	Thank you for your help. I got it working. I've used firebug with firefox in the past but never used it for java. I'm very impressed. I got everything working.

Kudos! I'm using Firebug more as websites and webservices increasingly rely on JSON, and in spite of what a lot of people have posted saying that JSON is compatible with VBA/VB/VBScript using a Dictionary Object as an intermediary... it just ain't so... of course, that is way OT here. Once again, glad you got it working. Stan

08-15-2011, 11:16 AM	#1
Ripster VBAX Newbie Joined: Aug 2011 Posts: 4 Kb Entries: 0 Articles: 0	IE Download VBA: Dim sURL As String Set ie = New InternetExplorer Dim ieDoc As Object sURL = "https://.." ie.Visible = True ie.navigate sURL Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Loop With ie.document.forms("logonForm") .NQUser.Value = "user" .NQPassword.Value = "password" .submit End With Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Loop Set ieDoc = ie.document ieDoc.all(307).Click Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Loop ieDoc.all(1364).Click Do While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Loop ieDoc.all(1370).Click VBA tags courtesy of www.thecodenet.com This goes to the website, enters username & password, logs in, Clicks a button (307), clicks a download button (1364) and then clicks a button (1370) from a menu that comes up after clicking "Download". The "Download" link that is clicked is javascript that creates a list with different choices. When you click one of the items on the list a new browser window opens and that opens a prompt to save, open, or cancel the download. The problem I am having is when vba clicks the menu item a new page opens like its supposed to but it then promptly disappears without ever giving the option to save a file. I do not have a direct link to the file I want to download. My assumption is that the link is created dynamically based on previously selected options. What can I do to prevent the new window from being closed and to save the file?
Local Time: 05:58 PM Local Date: 05-22-2013

08-15-2011, 02:01 PM	#2
stanl VBAX Regular Joined: Jan 2005 Posts: 1,108 Kb Entries: 0 Articles: 0	Hard to say w/out the html source. Does each menu option have it's own href? Is the jscript source a function in the document head or body? If you just put a pause message in your script right after choosing the menu option, does the pop-up not disappear. I have handles urls like this in the past by spawning another script as a separate thread, let it look for the window title of the jscript pop-up, deal with it then close and return to the first script. Another trick I have used is to create my IE object as InternetExplorer.Application.1 - then reference the Newwindow2 event and load the jscript window myself setting a second IE Object to that window.
Local Time: 05:58 PM Local Date: 05-22-2013 Location:

08-18-2011, 06:42 AM	#3
Ripster VBAX Newbie Joined: Aug 2011 Posts: 4 Kb Entries: 0 Articles: 0	This is not my website so I unfortunately cannot change the code. There is jscript in the website header. There is also references to external jscripts. I can post the header if you would like but it is very long. I don't know javascript so reading it is only helpful to a point. The html source for the item I am clicking to start the download is: HTML Code: <a class="NQWMenuItem" name="SectionElements" href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&_scid=DsZsoFas5E8&ViewID=d\x253adashboard\x257ep\x253aapnl74vr5ch30r06\x257er\x253a869hck781l4bksg9&Action=Download&SearchID=tf0ds3t0n47g9igkk3p3hv2nnm&PortalPath=/shared/IR\x2520ITS\x2520Sourcing\x2520and\x2520Supply\x2520Chain/_portal/Supplier\x2520Dashboard&Page=Supplier\x2520Open\x2520Orders&ViewState=312b0nq6c4v0p9eeib7dlkif0a&ItemName=R40GSS-\x2520Supplier\x2520Open\x2520Order\x2520Report&Format=mht&Extension=.xls'); return false">Download to Excel</a> Creating a second IE tab to call the jscript directly is something I considered but I don't know how to do that. I was also wondering if there was a way to grab the url before IE navigated to it and then call that in VBA using the navigate function so it would be part of the IE object I declared. I am still fairly new to VBA so I don't know how to go about doing either of those things. Thank you for your help! It is much appreciated. Last edited by Ripster : 08-18-2011 at 07:05 AM.
Local Time: 05:58 PM Local Date: 05-22-2013

08-19-2011, 01:43 PM	#4
stanl VBAX Regular Joined: Jan 2005 Posts: 1,108 Kb Entries: 0 Articles: 0	My advice. Open the site in Firefox with the Firebug add-on: it will track and display the exact GET/POST to get the file. Then you open a WinHttp.WinHttpRequest.5.1 Object, then use an ADO Stream to save the file after performing the GET to the full URL Firebug displays (you probably have most of it in the source you posted). Sorry if you felt I mislead you earlier about opening 2 IE Objects. If you think about it all that is happening is the web page is collecting parameters and performing a GET to send you the data. Obviously the URL is sensitive or else you could post more information.
Local Time: 05:58 PM Local Date: 05-22-2013 Location:

08-22-2011, 12:36 PM	#5
Ripster VBAX Newbie Joined: Aug 2011 Posts: 4 Kb Entries: 0 Articles: 0	I got the URL created through firebug and put it into a new browser tab only to get an error. After reading through the javascript it looks like before sending out the file it checks to see where the URL was called from and if it wasn't called from the parent page with the same session it gives a page saying that the session has expired. This is rather unfortunate. I'm at a bit of a loss on this one. I wish I knew why the page was closing without prompting for a download. Is it possible to directly control the program through shell instead of as an object in vba? I think the way it is created and controlled may be causing the abnormal behavior.
Local Time: 05:58 PM Local Date: 05-22-2013

08-31-2011, 06:15 AM	#7
Ripster VBAX Newbie Joined: Aug 2011 Posts: 4 Kb Entries: 0 Articles: 0	Thank you for your help. I got it working. I've used firebug with firefox in the past but never used it for java. I'm very impressed. I got everything working.
Local Time: 05:58 PM Local Date: 05-22-2013