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

[Peralva]

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/informacoes/indicadores/arqs/indicadores.xls

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



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.

[GTO]

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:


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