• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Download a latest file from a web URL

Marc L

Excel Ninja
Hi,​
unclear ! According to the link, which file to download, always the first ?​
 

Samir79

New Member
Hi,
Sorry, if i wasn't clear.
I will like to download the latest file lets say this one 'FBIL GOI STRIP Prices as on 07-October-2019'. The files in the link keep on the updating. If you notice, the filename remains the same except the date.
I would like to download the latest one to a local folder.
I know that this can be done for a folder on a local machine but from a URL not really sure how.
Any help here will be very helpful.
 

Marc L

Excel Ninja
So always the first …​
Your need is to just download an old xls workbook format to 'a local folder', nothing to do on this workbook ?​
What are the OS of the computer operating the download and the Excel version (32 / 64) ?​
 

Samir79

New Member
Yes. When you say old, i hope you meant the latest Workbook specified by date in the mentioned URL.
The Macro should look for the File name & the date in the URL and then download it.
Im running on Windows 10 and Office 2016 64 bit.
 

Marc L

Excel Ninja
That just means downloading the first link as it is - so the old xls workbook format - to 'a local folder', right ?​
 

Samir79

New Member
Yes.
I have done it using the link of the particular file and it works.
But what i will need is to look for the latest file as per Date as these files keep on updating in the URL.
 

Marc L

Excel Ninja
So when a new file comes in the webpage, where is it located : at the end of the list or as the first one ?!​
 

Marc L

Excel Ninja
A starter demonstration downloading the first Archives hyperlink to 'a local folder' (edit v3) :​
Code:
Sub Demo1()
       Const A = "<a href=""../../", C = ">Archives<", D = "https://fbil.org.in/", T = "Download"
         Dim L&, U$, N%, B() As Byte
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", D & "modules/Archives/downloads.aspx", False
        .setRequestHeader "DNT", "1"
         On Error Resume Next
        .send
         L = .Status = 200
      If L Then
           L = InStr(.responseText, C)
        If L Then
           L = InStr(L, .responseText, A) + Len(A)
           U = Replace(Mid(.responseText, L, InStr(L, .responseText, """") - L), "amp;", "")
          .Open "GET", D & U, False
          .setRequestHeader "DNT", "1"
          .send
           N = .Status = 200
              On Error GoTo 0
           If N Then
              B = .responseBody
              U = ThisWorkbook.Path & "\Samir79 " & Split(U, "=")(2)
              N = FreeFile
              Open U For Binary As #N
              Put #N, , B
              Close #N
              MsgBox U, vbInformation, T & "ed from " & D
           Else
              MsgBox "Error", vbExclamation, T
           End If
        Else
            MsgBox "No '" & C & "' in the webpage", vbExclamation, T & " aborted"
        End If
      Else
        MsgBox "Can't reach the webpage", vbCritical, T
      End If
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

Samir79

New Member
Will Try this.
Is it possible that the code can try to look for a file name with the latest date ?
Thanks
 
Last edited by a moderator:

Marc L

Excel Ninja
Maybe as it depends on what you call 'a file name with the lastest date' and as a starter you can amend the code to fit your need …​
 
Top