Excel VBA to answer a dialogue box in Internet Explorer

[EdwardAveyard]

Hello. This is my first post. I'm sorry if this is a repetition. I have searched, but there are so many different dialogue boxes that it's hard to narrow it down to the sort that you're dealing with.

I am designing a macro to download about 300 CSV files from a secure website. (Unfortunately, I can't share the actual link with you, but it is a URL that loads a CSV file.) This is how far I've got. I'm doing this for one file first, and then doing the loop later on (which should be easy enough). It's the comment in capital letters where I'm stuck.


Sub VisitWebsite()
'Open the CSV file from the website
Dim ie As Object
Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
Application.DisplayAlerts = False
ie.NAVIGATE "CSV link"
ie.Visible = True
While ie.busy
DoEvents
Wend
'THIS IS WHERE I'M STUCK. THE DIALOGUE BOX COMES UP, AND I DON'T KNOW HOW TO AUTOMATE THE ANSWER.
'Copy and paste the data across
Windows("costs.csv").Activate
Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Cost Savings Calculator VBA.xlsm").Activate
Range("C1").Select
ActiveSheet.Paste
'Close down the file
Windows("costs.csv").Activate
ActiveWindow.Close
End Sub

The dialogue box that opens from Internet Explorer has the title "View Downloads" and the only options are "Open and "Save". I want to select "Open". How do I do this?

Thank you in advance for your help.

 

[John_w]

instead of:

ie.NAVIGATE "CSV link"

and the rest of the IE automation code, try opening (downloading) the .csv file directly with Excel:

Workbooks.Open "CSV link"

 

[EdwardAveyard]

Thanks very much. I appreciate the help.