Excel Hints

Results 1 to 8 of 8

Thread: IE Download

  1. #1

    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]
    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?

  2. #2
    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.

  3. #3
    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.
    Last edited by Ripster; 08-18-2011 at 07:05 AM.

  4. #4
    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.

  5. #5
    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.

  6. #6
    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.

  7. #7
    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.

  8. #8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •