+ Reply to Thread
Results 1 to 9 of 9

VBA - IE - click on "Open" in an internet explorer "file download" window

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    Paris
    MS-Off Ver
    Excel 2010
    Posts
    5

    VBA - IE - click on "Open" in an internet explorer "file download" window

    Hello,

    It has been a long time ive been searching solutions to my problem, so now i need your help...
    Im looking for a way to create a macro from an excel file who will:
    1/Open internet explorer,
    2/connect to a website and login/password,
    3/do an export of data, filling a form with a selection of criterias (the website allows to export a new excel file B containing data that i want to copy/paste in my initial excel file A. Those data are dynamic as i select criterias, so the excel file B is not stored somewhere in a database).

    I did steps 1 and 2, but im blocked at the 3rd one. I selected the criterias I wanted for the export, and i clicked on the "OK" button which triggers the export, but then a new Internet Explorer webpage opens (blank), containing the IE window "File download - open, save, cancel?". My problem is that i cant get to the button "Open".

    I found some scripts on the web using API functions findwindow, findwindowex.. but which dont work. (the following code always returns "window not found"):



    Please Login or Register  to view this content.
    (NB: Of course i declared the findwindow function before)

    I tried changing the window name (my internet explorer is in french so i tried "TÚlÚchargement de fichiers" instead of "File Download" but that was unsuccessful).

    I also tried to focus on the second IE window, and use sendkeys to select the "Open" button from the IE File download window, unsuccessful:


    Please Login or Register  to view this content.
    Anyway, i wonder if theres not a way to avoid the opening of this second IE windows... In the HTML code of the first IE webpage, on the "OK" button, there is this "onclick" attribute:

    Please Login or Register  to view this content.
    Maybe i could use that, but i dont know how?

    Thank you very much in advance for your answers, any help will be great!!

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: VBA - IE - click on "Open" in an internet explorer "file download" window

    Ok, the short answer to this is there isn't a reliable way to do what you want. The only way I've really seen people doing this is through send keys - which as you've found is flakey at best.

    I don't know how much use it will be, but typically you wouldn't do this through internet explorer. You would use something like the WinHTTP request library to send the same requests a browser does, that way you download the document directly from the server rather than using internet explorer. This approach, however tends to be more tricky since you have to look at and understand the underlying request that the browser sends to the site. I'm afraid I can't really be of more help since I'd need to see the actual requests (which I can't since I can't log in).

    As an attempt, rather than clicking the link to download the report, try this:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    Paris
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA - IE - click on "Open" in an internet explorer "file download" window

    Hello!

    Thank you for your answer!
    yes, i had heard about winhttp. I tried the code you gave me (selecting first Microsoft winhttp services version 5.1 Reference).
    But i get an error on the line:
    Please Login or Register  to view this content.
    by the way, what is the ADODB Object exactly?
    Thank you

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: VBA - IE - click on "Open" in an internet explorer "file download" window

    Sorry, my fault, change it to:
    Please Login or Register  to view this content.
    You don't need to set a reference since the code uses late binding

    The stream is for reading/writing binary data. Since responseBody is binary, you need a tool to turn it into an actual file that the operating system understands. So all it does, is take the data and writes it to a file with the name that you've given it.

  5. #5
    Registered User
    Join Date
    01-17-2013
    Location
    Paris
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA - IE - click on "Open" in an internet explorer "file download" window

    Haha! thank you i changed position to 0 and it worked. I mean a csv has been created under the name i had given it
    but its empty, this is what i get inside:
    Please Login or Register  to view this content.
    I should get data coming from the criterias i selected on the website... as you can see below (snapshot of my website):
    Clic sur OK.png

    by the way i would like just to open the file and not save it somewhere, how can i do that?

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: VBA - IE - click on "Open" in an internet explorer "file download" window

    You can't, you can open it once it's saved though

    Ok, that message means that you need to send something else with the request for it to work. What happens if you navigate in your browser to whatever the url is and "download/export_report.asp?ID=641"

    You need to see what the actual request is that's being sent - it's in the "Network" tab of developer options, you'll need to click start capturing and then doubleclick on the url that returns the document.

    Can you post the results here?

  7. #7
    Registered User
    Join Date
    01-17-2013
    Location
    Paris
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA - IE - click on "Open" in an internet explorer "file download" window

    When i navigate to url & download/export_report.asp?ID=641, (from the previous page i gave you the snapshot earlier), i get an error (HTTP 500 Internal error to the server)..

    URL of the first page is url & slx_gen_report.asp?Action=Criteria&ID=641. When i click on the OK button, a new IE page opens with url & download/export_report.asp? ID=641, which is blank, and i get the IE window File download - open/save/cancel inside it.
    report.jpg

    Sorry, this isnt easy as i cannot give my login/password...

  8. #8
    Registered User
    Join Date
    01-17-2013
    Location
    Paris
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VBA - IE - click on "Open" in an internet explorer "file download" window

    Maybe i can find the answer in the asp page? im going to look at the export_report.asp script.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: VBA - IE - click on "Open" in an internet explorer "file download" window

    It's probably a permissions issue, you need to send the session cookie back to the server. Without going through the steps I asked you to though, it's impossible to say for certain

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1