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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Can use something like:

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

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

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top