excel – How to create a macro that enters a website and takes data from the last day?

Question:

Good afternoon,

I need to access a website and get the data from the last day and copy it to a folder in excel, the website is highlighted in the macro below, but I don't know how to proceed to select the data and copy it to my spreadsheet.

Sub AcessarSite()
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.application")
    With IE
        .Visible = True
        .Navigate ("http://www.cepea.esalq.usp.br/br/indicador/acucar.aspx")
        While .Busy Or .ReadyState <> 4:
            DoEvents:
        Wend
    'selecionar dados e copiá-los em células


        While .Busy Or .ReadyState <> 4:
            DoEvents:
        Wend
        Debug.Print .LocationURL
    End With
End Sub

Thanks!!!

Answer:

You can scan the site looking for the Ids of getElementsById(). From what I saw on the site, the Ids are imagenet-indicador1 and imagenet-indicador2.

I haven't tested the code, but it would be something like this, doing a simple CTRL+C and CTRL+V.

    Set tables = document.getElementsById("imagenet-indicador1")
    Set table = tables(0)
    Set clipboard = New MSForms.DataObject

    clipboard.SetText table.outerHTML
    clipboard.PutInClipboard
    ActiveSheet.Paste

You can also try importing the page, and manipulating the data directly in the spreadsheet:

Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.cepea.esalq.usp.br/br/indicador/acucar.aspx", Destination:= _
        Range("$A$1"))
        .Name = "acucar_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
Scroll to Top
AllEscort