Macro to automatically download file


New Member
I really need help in finding a macro to download a pdf file online, e.g. The link of the download URL will change everyday based on the date. I will be saving it into a drive C://Downloads. Also, is there any way to also automatically convert the pdf file into excel after it downloads? Thanks. Really appreciate if somebody could come with a code and to explain which places for me to edit the code.


Well-known Member
Modify the parts of the code where indicated.

Public Sub DownloadFile()
' Need to add references to
' the following object models
' via Tools => References:
'   1) Microsoft XML v6.0
'   2) Microsoft ActiveX Data Objects
  Const lngOK = 200&
  Dim objXmlHttp As New MSXML2.XMLHTTP60
  Dim objStream As New ADODB.Stream
  Dim strTargetPath As String
  Dim strSourceUrl As String
  On Error GoTo ErrorHandler

' URL of the file to download (change this!):
  strSourceUrl = ""
' Path to download the file to (change this!)
  strTargetPath = "C:\Users\MyUserName\Desktop\test.pdf"
  objXmlHttp.Open "GET", strSourceUrl, False
  If objXmlHttp.Status = lngOK Then
    objStream.Type = adTypeBinary
    objStream.Write objXmlHttp.responseBody
    objStream.SaveToFile strTargetPath, adSaveCreateOverWrite
    MsgBox "File download successful." & vbCrLf & strTargetPath, vbInformation
    Err.Raise vbObjectError + 513, "DownloadFile", "HTTP error."
  End If
  On Error Resume Next
  Set objXmlHttp = Nothing
  Set objStream = Nothing
  Exit Sub
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub


New Member
Thanks so much, it works but there is a problem: as the file url (eg "Jun26.pdf", "Jun27.pdf") will change everyday according to the date, it wont be able to automatically download the file for me daily. Is there any way to change this?


New Member
Oh yes, and also would it be possible to automatically change the file name every time the file is downloaded to reflect the day that it is downloaded? Many thanks.


Well-known Member
Can use something like:

strSourceUrl = "" & Format(Date, "mmmdd") & ".pdf"

strTargetPath = "C:\Users\MyUserName\Desktop\" & Format(Date, "mmmdd") & ".pdf"

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...