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 ANBIMA - Associação Brasileira das Entidades dos Mercados Financeiro e de Capitais , but I couldn't make a connection with this link.

 

[alansidman]

I personally would use Power Query to get data from a web site. Once loaded into a spreadsheet, then

 

[alansidman]

I didn't finish my comments in post 2. In this manner, you can upload daily by just clicking on the refresh button on the Data Tab. Look at the link in my signature.

 

[Peralva]

I had never used this resource before. I just tried it and I got the formula = Table.TransformColumnTypes(#"INDICADORES_20201118_09_33#XLS1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}})

Since it also uses the name of the sheet (INDICADORES_20201118_09_33), I think I'll have the same problem when I try to update it tomorrow.

 

[alansidman]

Use the web address without the .xls file name and it should populate and give you the option to select the correct file/table.

 

[Fluff]

Cross posted Capture data from a web workbook that changes the sheet name daily - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.