Download Report from a Website

[nianchi111]

Hi Friends,

I have a website where I have to download 100s of reports. I have created a macro that will open the website, log in, and update the date range and then download the report one by one.

the current challenge is that once after downloading at the bottom of the IE a dialog box appears with Open, Save and Save As.

I need to Save the downloaded file to a folder with the names from column A.

Looking forward to a positive response.

Thanks,
Vimal

 

[John_w]

See:

Using UIAutomationClient to automate the Save As file download in IE11

Here is VBA code which automates the complete 'Save As' file download procedure in IE11, when a download is offered by IE's Download Notification Bar. To use for your own web site downloads you would automate IE and do whatever is necessary to make the Download Notification Bar appear at the...

www.mrexcel.com

 

[nianchi111]

Sub GoToWebsiteTest()
Dim eventChange As Object
Dim appIE As InternetExplorerMedium
Set appIE = New InternetExplorerMedium
sURL = "https://Test"
With appIE
.navigate sURL
.Visible = True
End With
Do While appIE.Busy Or appIE.readyState <> 4
DoEvents
Loop
Set eventChange = appIE.document.createEvent("HTMLEvents")
eventChange.initEvent "change", True, False
With appIE.document.getElementsByName("Reports")(0)
.selectedIndex = 3
.dispatchEvent eventChange
End With
Do While appIE.Busy Or appIE.readyState <> 4
DoEvents
Loop
appIE.document.forms(0).all("DateFrom").Value = Range("C2").Value
appIE.document.forms(0).all("DateTo").Value = Range("C4").Value
appIE.document.forms(0).all("Shop").Focus
appIE.document.forms(0).all("Shop").Click
appIE.document.forms(0).all("Shop").selectedIndex = 2
appIE.document.forms(0).submit
Set appIE = Nothing
End Sub

This is the Code I have created searching so many websites and the last part once the report is downloaded it has to be saved in the folder with the name which is mentioned in cell A2.

 

[John_w]

As shown in the example Test routine in my linked posted, call the IE_Download_File_Using_UIAutomation function immediately after the line which causes IE's download notification bar to appear (the submit line in your case), with the associated arguments, like this:

    Dim saveAsFullName As String
    Dim replaceExistingFile As Boolean
    Dim downloadResult As String, downloadStatus As Boolean
    
    saveAsFullName = ActiveSheet.Range("A2").Value          'Save in this folder with the file name provided by web site
    If Right(saveAsFullName, 1) <> "\" Then saveAsFullName = saveAsFullName & "\"   'Folder must end with "\"
    replaceExistingFile = True
    
    downloadStatus = IE_Download_File_Using_UIAutomation(IE.hwnd, saveAsFullName, replaceExistingFile, downloadResult)
    Debug.Print "Download result = " & downloadResult
    Debug.Print "Download saveAsFullName = " & saveAsFullName
    Debug.Print "Download status = " & downloadStatus

 

[nianchi111]

As shown in the example Test routine in my linked posted, call the IE_Download_File_Using_UIAutomation function immediately after the line which causes IE's download notification bar to appear (the submit line in your case), with the associated arguments, like this:

    Dim saveAsFullName As String
    Dim replaceExistingFile As Boolean
    Dim downloadResult As String, downloadStatus As Boolean
   
    saveAsFullName = ActiveSheet.Range("A2").Value          'Save in this folder with the file name provided by web site
    If Right(saveAsFullName, 1) <> "\" Then saveAsFullName = saveAsFullName & "\"   'Folder must end with "\"
    replaceExistingFile = True
   
    downloadStatus = IE_Download_File_Using_UIAutomation(IE.hwnd, saveAsFullName, replaceExistingFile, downloadResult)
    Debug.Print "Download result = " & downloadResult
    Debug.Print "Download saveAsFullName = " & saveAsFullName
    Debug.Print "Download status = " & downloadStatus

Thank you so much !!!