What is the Method for Downloading a Web Page Due to the Retirement of IE 11 in VBA

Question

I have an extensive VBA macro in Excel that is used to open a IE URL and download the page. It uses CreateObject("InternetExplorer.Application") and the URLDownloadToFile function of "urlmon". The purpose of this macro is to do the following:
Takes the URLs from the Export sheet, calls SSRS (Reporting Services) to display the report, and downloads the report to a file with the path and name provided on the Export sheet. This results in a set of files, one for each report, with parameters, chart, and data table in the file.

Here are the relative code snippets from the macro:

If Cells(ActiveCell.row, 3).Value = "" Then
    Call IE_Automation0(URL & "&rs:Command=Render&rs:Format=EXCEL&rc:Toolbar=false", Cells(7, 9).Value + IIf(Right(Cells(7, 9).Value, 1) = "\", "", "\") + Cells(ActiveCell.row, 2).Value) ' change 20120327        
Else
    Call IE_Automation0(URL & "&rs:Command=Render&" & theFormat & "&rc:Toolbar=false", Cells(ActiveCell.row, 3).Value + IIf(Right(Cells(ActiveCell.row, 3).Value, 1) = "\", "", "\") + Cells(ActiveCell.row, 2).Value) ' change 20120327
End If

The following is in the IE_Automation0:

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' Send the form data To URL As POST binary request
IE.Navigate theURL

Call downloadFile(theURL, theSaveAsFilename + theExtenstion) ' download the SSRS file and save it

This is in the downloadFile function which calls the URLDownloadToFile function of urlmon:

returnVal = URLDownloadToFile(0, target, strSavePath, 0, 0)

What do I need to do to change my code so that I can do the same functions but using Edge, Chrome, or Firefox due to IE being removed in June of 2022.

If you want to automate modern browsers with VBA, you need to use SeleniumBasic. SeleniumBasic is a Selenium based browser automation framework for VB.Net, VBA and VBScript. You can refer to [this answer](https://stackoverflow.com/questions/70371312/connecting-edge-chrome-to-vba/70375655#70375655) about how to use SeleniumBasic. But I don't find similar function like `URLDownloadToFile` to download file, you can check if the file can be downloaded by pressing **Ctrl+S**, if so, you can use `SendKeys` to simulate key press. — Yu Zhou, Feb 17 '22 at 06:23
Can an Excel workbook using Selenium be shared between users, or would they all need to download Selenium? — FlyFish, Feb 21 '22 at 23:27

Svein Arne Hylland · Answer 1 · 2023-02-17T11:08:28.690

A bit late to the party. But if anyone needs a solution, this might still work.

Declare the DLL in a module.

Private Declare Function ShellExecute _
  Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hwnd As Long, _
  ByVal Operation As String, _
  ByVal Filename As String, _
  Optional ByVal Parameters As String, _
  Optional ByVal Directory As String, _
  Optional ByVal WindowStyle As Long = vbMinimizedFocus _
  ) As Long

Then create the Function to use :

Function download(URL, ToFile) As Boolean
On Error GoTo feil
download = False
' Parameter for funsjon overføres
    strFileURL = URL                    ' eks "http://norsktipping.n3sport.no/default.aspx?event=GETMATCHINFO&NTMatchId=209562"
    strHDLocation = ToFile              ' eks "file.htm"
 
' Hent filen
    Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")
 
    objXMLHTTP.Open "GET", strFileURL, False
    sv = objXMLHTTP.send()
    
    If objXMLHTTP.Status = 200 Then
      Set objADOStream = CreateObject("ADODB.Stream")
      objADOStream.Open
      objADOStream.Type = 1 'adTypeBinary
 
      objADOStream.Write objXMLHTTP.ResponseBody
      objADOStream.Position = 0    'Set the stream position to the start
 
      Set objFSO = CreateObject("Scripting.FileSystemObject")
        If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
      Set objFSO = Nothing

      objADOStream.SaveToFile strHDLocation
      objADOStream.Close
      Set objADOStream = Nothing
    download = True
    End If
 
    Set objXMLHTTP = Nothing
 Exit Function

feil:
If ActiveWorkbook.ReadOnly Then MsgBox ("Workbook is in read only mode! This result in error when using the dll for web.")
 
download = False
Resume Next
End Function

Then you can call for any file URL you want to download like this :

Sub testDownloadOfImage()

sv = download("http://s3-eu-west-1.amazonaws.com/db-comics/24_1984_web", "C:\Temp\TestImage.bmp")

End Sub

What is the Method for Downloading a Web Page Due to the Retirement of IE 11 in VBA

1 Answers1