Using VBA to download a file from a website

[NomalAnomaly]

Hello,

These days I'm creating an automation which should open a website, log in and download a CSV file from a certain location within the website.

The only problem I have is with the download box which appears right after clicking on the "download" button in the website. It is a regular browser download box with Open / Save / Cancel options.

I am trying to use VBA's SendKeys function to hit left twice and then return, which should choose the "Open" option.

Here is the relevant code fragment:


Dim EnterKey, LeftKey As String

EnterKey = "~"
LeftKey = "{LEFT}"

Application.SendKeys LeftKey, False
Application.SendKeys LeftKey, False
Application.SendKeys EnterKey, False


When I run the script the website opens and the download box appears, but the "Open" button is not clicked.

Any ideas...?

Thank you in advance,
NormalAnomaly

[Aussiebear]

Shredude is normally very good with this type of issue. Can you wait for a bit on this?

[Shred Dude]

I never use SendKeys.

If you can review the HTML Source of the site you're automating, I'm sure you can find the name of the control in question and be able to "click" it directly via your code.

If you can point us to the site in question, I'd be glad to have a look.

[NomalAnomaly]

Hey, thanks for the reply.

Unfortunately, this website is related to my day job and contains information which I cannot share. However, I can be a little more specific with my request.

The first thing I do is exactly what you said - I search for the relevant control (in this case - a download button) and when I find it I click on it. The code looks like this:


For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.ID = "eomReportDetails_UNRWG_EndofMonthReportDetails_btnExportAll" Then
oHTML_Element.Click
Exit For
End If
Next


Up until here, everything works fine. The code recognizes the button and clicks on it. After the click, I get the well-known dialog box which I stated in my original post. The controls in this dialog box do not appear in the in the page source, they are controlled by the browser rather than the website.

And due to this reason I use the SendKeys. I saw no other option. Please enlighten me...

By the way, you can easily reproduce this with any website that offers files for downloading.

Thanks again,
NormalAnomaly

[Shred Dude]

Have you tried obtaining a reference to the Pop-Up window?

You could try code like below to see if you can address the elements within the pop up window directly. This needs to run from within a SHEET module to support the WithEvents pieces.

Pause the code where you get the PopUp reference so you can explore the document elements to find the correct ID reference for the EnterButton. Make the appropriate changes and give it a try.

Good luck with it.


'Reference to Microsoft Internet Controls
'Reference to Microsoft HTML Object Library

'PLACE THIS CODE IN A SHEET MODULE

Private WithEvents ie As InternetExplorer
Private WithEvents iePopup As InternetExplorer
Private Sub ie_NewWindow2(ppDisp As Object, Cancel As Boolean)
Set iePopup = New InternetExplorer
Set ppDisp = iePopup
End Sub

Public Sub getFile()
Dim URL As String

URL = "http://myprivatecompanywebiste.com"
Set ie = New InternetExplorer 'CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate URL
Do While .Busy Or .ReadyState <> 4: DoEvents: Loop

.document.getelementbyid("eomReportDetails_UNRWG_EndofMonthReportDetails_btnExportAll").Click

Do Until .ReadyState = READYSTATE_COMPLETE _
And Not .Busy _
And (Not iePopup Is Nothing)
DoEvents
Loop

While iePopup.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend

With iePopup
'try to "Click" the "EnterButton" in the PopUp window...

.document.getelementbyid("Enterbutton").Click

'see where that gets you...

End With


End With 'ie

ie.Quit
Set ie = Nothing

End Sub

[NomalAnomaly]

Thank you for the comprehensive reply. I will try this later on and post another reply to say if it works or not.

[suisgrand]

Hi NomalAnomaly, every reader,

is it working? I am running more or less the same code, also in order to download a file from my company java web server. But my program won't work:dunno .
My code would be very similar to this one below:

Public Sub LaunchtheWebGasDay()

Dim objIE As Object 'InternetExplorer
Set objIE = CreateObject("internetexplorer.application") '= New InternetExplorer 'Create Internet Explorer Object
objIE.Visible = True
objIE.Navigate 'transcanada.com then customer express then gasdaysummaryreport
' i am not allowed to post a link yet so...

Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop 'Wait for page to load
objIE.Document.forms(0)("ActionType").Value = ".csv"
objIE.Document.forms(0)("SubmitButton").Click 'Click submit

Do Until objIE.ReadyState = READYSTATE_COMPLETE: Loop 'Wait for page to load
Set objIE = Nothing

End Sub

My browser (IE8) pops up a prompt asking me if I want to download the website once my program submit a request to download the file.
Sure, why not specifying this website as a "trusted site"? Tried that but then i have the following error: "automation error - the object invoked has disconnected from its clients" on the sixth line.

Did i miss something here guys?

Thanks for your help:-)