Results 1 to 5 of 5

Thread: Macro to automatically download file

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to automatically download file

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

  2. #2
    Board Regular ParamRay's Avatar
    Join Date
    Aug 2014
    Location
    England, UK
    Posts
    1,185
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to automatically download file

    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
    Windows 10, Excel 365

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to automatically download file

    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?

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to automatically download file

    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.

  5. #5
    Board Regular ParamRay's Avatar
    Join Date
    Aug 2014
    Location
    England, UK
    Posts
    1,185
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to automatically download file

    Can use something like:

    Code:
    strSourceUrl = "http://example.com/path/" & Format(Date, "mmmdd") & ".pdf"
    
    strTargetPath = "C:\Users\MyUserName\Desktop\" & Format(Date, "mmmdd") & ".pdf"
    Windows 10, Excel 365

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •