How to automate saving file from Internet Explorer 11 in VBA on 64-bit machine

Question

Have an Excel file at this location: https://ufr.osd.state.ma.us/DM/GetDoc.aspx?DocId=165350&Library=EFILEDMProd

You can see there's a hyperlink titled "Download" at the top. Need to automate the saving of this file using VBA in Excel through Internet Explorer 11 on a 64-bit machine. Ideally, I'd also like to save the file in a specific path and with a specific file name ("2018-042389332.xls", in this case). Trouble is that I cannot find a way to direct download the file, so I'm left to deal with the File Save dialog box that is tricky to interact with. So far I've tried using SendKeys with inconsistent results.

Here's the relevant part of the code I'm using. So far it works to click on the "Download" button, pulls up the File Save dialog at the bottom, and in some instances, follows through with saving the file as the default file name.

    IE.Document.getElementById("LinkButton2").Click
    SendKeys "{F6}", True
    SendKeys "{TAB}", True
    SendKeys "{DOWN}", True
    SendKeys "{DOWN}", True
    SendKeys "{ENTER}", True

    SendKeys "{TAB}", True
    SendKeys "{TAB}", True
    SendKeys "{TAB}", True
    SendKeys "{ENTER}", True

Answer 1

Instead of interacting with the file dialog, make IE download the file directly. Then use a function that returns the name of the latest file in a folder (IE's download folder) so you know which one is the file you just downloaded.

Function LatestFile(StrDefaultFolder As String) As String

Dim LatestDate As Date

StrCurrentFile = Dir(StrDefaultFolder)

Do While Len(StrCurrentFile) > 0

    StrFullFileName = StrDefaultFolder & StrCurrentFile

    If FileDateTime(StrFullFileName) > LatestDate Then
        LatestDate = FileDateTime(StrFullFileName)
        LatestFile = StrCurrentFile
    End If

    StrCurrentFile = Dir
Loop

End Function

Now, you can copy-paste and rename the file as you like:

Sub MoveAndRenameFile()

StrLastFile = LatestFile("C:\Users\YourUserName\Downloads\")

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile StrLastFile, "C:\SomeFolder\NewFilename.xlsx"

End Sub

Answer 2

To get more consistent results I would try to wait a second after each send keys, try inserting this after each sendkey:

Application.Wait (Now + TimeValue("0:00:01"))

Do you need to be using internet explorer? If you have the direct download link this would be way easier

Dim HttpReq As Object, myURL As String
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
    myURL = *direct download link*
    HttpReq.Open "GET", myURL, False, "", ""
    HttpReq.send
    myURL = HttpReq.responseBody
    If HttpReq.Status = 200 Then
        Set oStrm = CreateObject("ADODB.Stream")
        oStrm.Open
        oStrm.Type = 1
        oStrm.Write HttpReq.responseBody
        oStrm.SaveToFile *File Path with file name and extension*,1 ' 1 = no overwrite, 2 = overwrite
        oStrm.Close
    End If

Answer 3

you need to use URLDownloadToFile function for direct downloading the file using VBA code.

Here is the sample code that you directly try to run from your Excel VBA module.

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Sub download_HK_picture()
imgsrc = "https://dornsife.usc.edu/assets/sites/298/docs/ir211wk12sample.xls"
dlpath = "C:\"
URLDownloadToFile 0, imgsrc, dlpath & "ir211wk12sample.xls", 0, 0
End Sub

Further, you can modify the code as per your own requirement.

Reference:

(1) Download Files with VBA URLDownloadToFile