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

Peralva

New Member
Joined
Nov 18, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:

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

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,008
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I personally would use Power Query to get data from a web site. Once loaded into a spreadsheet, then
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,008
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Nov 18, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 26, 2007
Messages
6,008
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,764
Messages
5,574,103
Members
412,569
Latest member
kiteifitswindy
Top