Macro to automatically download file

electricdevil

New Member
Joined
Jun 25, 2019
Messages
3
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
Modify the parts of the code where indicated.

Code:
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

New Member
Joined
Jun 25, 2019
Messages
3
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

New Member
Joined
Jun 25, 2019
Messages
3
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

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
Can use something like:

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

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

Watch MrExcel Video

Forum statistics

Threads
1,099,648
Messages
5,469,929
Members
406,674
Latest member
MrSTruct

This Week's Hot Topics

Top