VBA to automate Internet Explorer Open/Save As Dialogue Box?

[FFClutch]

Greetings all,
I'm trying to fully automate the process of logging in to a website, checking some buttons, and downloading an Excel file. I've used various snippets of code from other posts and got all that working up to the point where the Open/Save As dialogue box pops up. That I can't get by. I would want to do this without SendKeys as I'd want it to be able to run overnight while the computer is locked.

This seems to be something that people have asked about quite a bit as I've looked at a number of threads on this site and a few others that offer potential solutions, but I haven't found any that I've been able to implement. Here's an example thread from which I was trying to modify the code but having no success. I'm by no means a VBA expert so the problem might be that I just don't know how to correctly adapt that.

To start, I'm using Windows 8, Excel 2010, and Internet Explorer 10. The code I have so far opens Internet Explorer, navigates to a website, enters my username and password, submits the form, navigates to another page on the site, checks some radio buttons and a checkbox, and submits the form. It's at this point that the Open/Save As box pops up and I haven't been able to get any further. I would like it to Save As and put the file in a specific folder. My next step after getting the file to save would be to have Excel Open the file that just saved and copy the information in the file to the workbook this VBA is stored in and then run some operations on it.


Here's the code I have so far:

Sub Downloader()


Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim clip As DataObject


'create a new instance of ie
Set ieApp = New InternetExplorer


'you don’t need this, but it’s good for debugging
ieApp.Visible = True


'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "https://omx.ordermotion.com"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
           
    Set ieDoc = ieApp.Document


'fill in the login form – View Source from your browser to get the control names
    With ieDoc.forms(0)
        .UserName.Value = "username"
        .Password.Value = "password"
        .submit
    End With
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
    
'now that we’re in, go to the page we want
    ieApp.Navigate "https://omx.ordermotion.com/en/daily_sales_flash.asp?_t=1406234730046"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop


    Set ieDoc = ieApp.Document
    
'select the radio boxes and submit
    ieDoc.getElementById("ResultType... yesterday").Click
    ieDoc.getElementById("ReportType... by Promotion and Keycode").Click
    ieDoc.getElementById("FormatExcel").Click
    With ieDoc.forms(0)
        .submit
    End With
  
  
'This is the point at which the Open/Save As Dialogue box pops up.


'Next I would want Internet Explorer to use the browser's Back button to go back one page and then click the logout element on the previous page.
 
'Next I would want Internet Explorer to close


'Next I would want Excel to open the file that was just saved


'From here I would just use some copy and paste functions to move data between the new file and the workbook this macro is saved in.
   
End Sub

Any help would be appreciated as I think this might be beyond my VBA abilities.

 

[John_w]

Try the code at http://www.mrexcel.com/forum/excel-...-visual-basic-applications-3.html#post2805320. You'll probably need to modify the Windows API code which handles the IE windows (window class names, captions, windows hierarchy, etc.) to work on Windows 8/IE10.

I notice that the page is .asp - with this you may be able to download the file invisibly without automating the IE10 download windows, by sending form data in a XMLhttp POST request. For examples of this technique, see http://www.mrexcel.com/forum/excel-...n-internet-explorer-web-site.html#post3404965 and http://www.mrexcel.com/forum/excel-questions/749860-msxmlhttp-post-paramaters.html. You may need to automate IE to get the URL of the file you want to download.

 

[jackson89]

Need some help!!!! i've been trying to code a macro using twebst. But the problem with twebst is that it doesn't write a code when a link is clicked that open the webpage in a new window. Now how do i navigate my code which as been written for the login page to a new page. I want to download the data from the new page unto a excel sheet. Below is the code that was generated.
Sub OpenTwebstMacro()
Dim core As ICore
Set core = New OpenTwebstLib.core

Dim browser As IBrowser
Set browser = core.StartBrowser("http:xxxxx/login.aspx")

Call browser.FindElement("input text", "id=UserName").InputText("xxxx")
Call browser.FindElement("input password", "id=Password").InputText("xxxx")
Call browser.FindElement("input submit", "id=LoginButton").Click
Call browser.FindElement("a", "id=ctl00_TreeView1t5").Click
Call browser.FindElement("select", "id=ctl00_ContentPlaceHolder1_ddlAccyear").Select("2014-2015")
Call browser.FindElement("input submit", "id=ctl00_ContentPlaceHolder1_btnCurrentEnrollment").Click ' here the new window opens up
' I want to navigate to the new webpage that has been opened start extracting my required data

Sheets("Run").Select


End Sub