How to create a macro that comes into the site and get the data from the last day?

3

Good afternoon,

I need to access a website and grab the data from the last day and copy them to a folder in excel, the site is highlighted in the macro below but I do not know how to proceed to select the data and copy them in my worksheet .

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

Thank you !!!

    
asked by anonymous 20.02.2017 / 20:57

2 answers

1

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

I did not test the code, but it would be something like that, 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 to import the page, and manipulate the data directly in the worksheet:

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
    
21.02.2017 / 20:26
0

I was able to copy only the desired table in my spreadsheet with the code below:

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
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 = xlSpecifiedTables
        .WebTables = "1"
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

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

    IE.Quit

End Sub

Is it possible to automatically update another worksheet with the data of the last date as soon as I play in the macro?

    
21.02.2017 / 22:12