asked on

Excel-VBA -IE Automation - Download an excel file from a URL

Hi Experts,

Hoping someone can help me with a solution to automate a manual daily task
I need top download a file from a http (url - report server) site and save it to a folder at regular intervals ( hourly )

The manual process is as below
1) Go to the website/url using IE
2) Click on a drop-down item and select "Export to Excel" (Actions --> Export (option 3) --> Excel (option 5) --> Its a sub-menu see html below
3) A pop-up appears to Open or Save the .xlsx file
4) Click on Save button
5) Save it to a folder location on my pc.

I cant use any 3rd party tools or software as I cant download it due to restrictions in my organisation.

Preferably excel/vba solution or if there is an inbuilt windows solution to capture and record actions above and then schedule it at regular intervals.

Thanks

HTML:

            <td class="ms-toolbar sqlrv-ToolBarCell" nowrap="nowrap">
      <div id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu">
                              <span style="display:none"><menu type='ServerMenu' id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ctl00"><ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ReportBuilder_forRendering" type="option" onMenuClick="STSNavigate2(event,'http://project/PWA/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderViewer&ReportPath=http%3a%2f%2fproject%2fPWA%2fReports%2fPSD+Extract.rdl');" text="Open with Report Builder" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_Print_forRendering" type="option" onMenuClick="$find('m_sqlRsWebPart_ctl00_ReportViewer').invokePrintDialog();" text="Print" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_Export" type="submenu" text="Export"><ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_Export_Label" type="label" menuGroupId="2147483647"></ie:menuitem><ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_XML" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("XML");" text="XML file with report data" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_CSV" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("CSV");" text="CSV (comma delimited)" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_TAB" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("TAB");" text="TAB delimited" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_PDF" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("PDF");" text="PDF" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_MHTML" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("MHTML");" text="MHTML (web archive)" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_EXCEL" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("EXCEL");" text="Excel" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_IMAGE" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("IMAGE");" text="TIFF file" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_WORD" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("WORD");" text="Word" menuGroupId="2147483647"></ie:menuitem>
                              <ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_IMAGE (JPEG)" type="option" onMenuClick="$find("m_sqlRsWebPart_ctl00_ReportViewer").exportReport("IMAGE (JPEG)");" text="Image (JPEG)" menuGroupId="2147483647"></ie:menuitem>

Noah

Hi!

You may refer to this example:

Sub DownloadFile()
    
    'Set the file URL
    fileUrl = "http://example.com/file.xlsx"
    
    'Set the file name and path to save
    filePath = "C:\Folder\file.xlsx"
    
    'Create a new InternetExplorer object
    Set IE = CreateObject("InternetExplorer.Application")
    
    'Make IE visible (for testing)
    IE.Visible = True
    
    'Navigate to the file URL
    IE.Navigate fileUrl
    
    'Wait for IE to finish loading the page
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop
    
    'Find the Export menu and click it
    Set exportMenu = IE.Document.getElementById("m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_Export")
    exportMenu.Click
    
    'Find the Excel export option and click it
    Set excelOption = IE.Document.getElementById("m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_EXCEL")
    excelOption.Click
    
    'Wait for the Save As dialog to appear
    Application.Wait (Now + TimeValue("0:00:02"))
    
    'Set the Save As dialog window handle
    Set hwnd = FindWindow(vbNullString, "Save As")
    
    'If the Save As dialog is found, set the file name and path, and click the Save button
    If hwnd <> 0 Then
        Set saveAsDialog = New CDialogHandler
        saveAsDialog.hWnd = hwnd
        saveAsDialog.SetDlgItemText &H472, filePath
        saveAsDialog.ClickDlgItem &H6, True
    End If
    
    'Close IE
    IE.Quit
    
End Sub

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.