Capture data from a web workbook that changes the sheet name daily

Hello everyone,

I need help to capture some information from a .xls file that is updated every day on a web site.

The data I need is on this link: https://www.anbima.com.br/informacoe...ndicadores.xls

I recorded a macro to paste this information into another workbook I have, and it works fine on the first day:

Code:

---

Sub Anbima()

    ActiveWorkbook.Queries.Add Name:="INDICADORES_20201117_09_35#XLS", Formula _
        := _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(Web.Contents(""https://www.anbima.com.br/informacoes/indicadores/arqs/indicadores.xls (remove the spaces)""), null, true)," & Chr(13) & "" & Chr(10) & "    #""INDICADORES_20201117_09_35#XLS1"" = Source{[Name=""INDICADORES_20201117_09_35#XLS""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""INDICADORES_20201117_09_35#XLS1"",{{""Column1"", type text}, {""Column2"", t" & _
        "ype text}, {""Column3"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    'ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=INDICADORES_20201117_09_35#XLS;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [INDICADORES_20201117_09_35#XLS]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "INDICADORES_20201117_09_35_XLS"
        .Refresh BackgroundQuery:=False
    End With
End Sub

---

However, the name of the sheet that contains this information changes everyday. On the example above, its name is "INDICADORES_20201117_09_35_XLS" (meaning the file was last updated on Nov/17/2020 at 09:35 am). I can deal with the '20201117' part on my macro, but there's no way to find out what will be the new time that will replace the '09_35'. For example, on Nov/04/2020 the file was updated on 09:39 am, so the name of the sheet was INDICADORES_20201104_09_39_XLS .

Question:

How can I rewrite the code above to force it to capture the data from the .xls file without using the name of the sheet?

Alternatively, the data I need is also displayed here https://www.anbima.com.br/informacoes/indicadores/ , but I couldn't make a connection with this link.

Greetings Peralva,

Not sure how one would predict the latest save date/time of the worksheet, but at least as it seems to me in checking the link a couple of times, the workbook (WB) remains a one-sheet WB, that is uploaded to the website with a consistent filename. If that is the case, how about just downloading the WB and working on it from within th e PC?

In a Standard Module:

Code:

---

Option Explicit
 
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
 
Public Sub Example()
Dim sFolderPath As String
Dim sFullname As String
Dim WB As Workbook
 
  sFolderPath = Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Downloads\"
 
  'NOTE: I did NOT worry about overwriting the file.
  If DownloadFile("https://www.anbima.com.br/informacoes/indicadores/arqs/indicadores.xls", sFolderPath & "indicadores.xls") Then
    Set WB = Workbooks.Open(sFolderPath & "indicadores.xls", , True)
   
    'import data needed here
   
    'save full name for optional Kill
    sFullname = WB.FullName
   
    WB.Saved = True
    WB.Close False
   
    'Optional - delete workbook if not needed
    Kill sFullname
   
  Else
    MsgBox "I goofed something and could not download the file", vbInformation, vbNullString
  End If
End Sub
 
Private Function DownloadFile(URL As String, LocalFilename As String) As Boolean
  DownloadFile = CBool(Not URLDownloadToFileA(0, URL, LocalFilename, 0, 0))
End Function

---

Hope that helps,

Mark