How to create a macro that enters the site and takes the data from the last day?

Asked

Viewed 1,241 times

3

Good afternoon,

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 don’t know how to proceed to select the data and copy them in 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

Thank you!!!

excel vba

  • Take a look here: http://answall.com/questions/45142/macro-para-acessar-site-com-login

2 answers

1


You can scan the site looking for the id of getElementsById(). From what I saw on the site the id is imagenet-indicator1 and imagenet-indicator2.

I didn’t 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 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

  • Nice thank you, but build error appears in Msforms.Dataobject, I believe that by not having the microsoft Forms 2.0 library, but I can not put it in my excel. There is another way?

  • I edited the answer, I think this option helps you.

  • Help me yes, thank you very much, but I can’t even get only the first table right?

  • Depending on how the site was built, yes, but in this case I could not. But take a look here, you might be able to improve my https://msdn.microsoft.com/en-us/library/bb223048(v=office.12). aspx

  • Thanks friend, but the link is broken!

  • I don’t know paste link here kkk, search for Querytable.Webselectiontype Property, this is part of the code I posted, if I change this property maybe I can only get to the tables of this page.

  • kkkk was worth!!!!!

Show 2 more comments

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

It is possible to automatically update another spreadsheet with the data of the last date as soon as I give "play" in the macro?

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.