Consulting

Results 1 to 2 of 2

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

  1. #1
    VBAX Newbie
    Joined
    Nov 2020
    Posts
    1
    Location

    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:

    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.
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,364
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •