# Macro to automatically download file



## electricdevil (Jun 25, 2019)

I really need help in finding a macro to download a pdf file online, e.g. www.example.com/JUN%%JUN19.pdf. 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.


----------



## ParamRay (Jun 25, 2019)

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 = "http://s2.q4cdn.com/235752014/files/doc_downloads/test.pdf"
  
' Path to download the file to (change this!)
  strTargetPath = "C:\Users\MyUserName\Desktop\test.pdf"
  
  objXmlHttp.Open "GET", strSourceUrl, False
  objXmlHttp.send
  
  If objXmlHttp.Status = lngOK Then
    objStream.Open
    objStream.Type = adTypeBinary
    objStream.Write objXmlHttp.responseBody
    objStream.SaveToFile strTargetPath, adSaveCreateOverWrite
    objStream.Close
    MsgBox "File download successful." & vbCrLf & strTargetPath, vbInformation
  Else
    Err.Raise vbObjectError + 513, "DownloadFile", "HTTP error."
  End If
  
ExitHandler:
  On Error Resume Next
  objStream.Close
  Set objXmlHttp = Nothing
  Set objStream = Nothing
  Exit Sub
  
ErrorHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub
```


----------



## electricdevil (Jun 25, 2019)

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?


----------



## electricdevil (Jun 26, 2019)

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.


----------



## ParamRay (Jun 26, 2019)

Can use something like:


```
strSourceUrl = "http://example.com/path/" & Format(Date, "mmmdd") & ".pdf"

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


----------

