Copy website table and paste into excel

[texrx]

hello, i am trying to copy an internet data table in excel. I can open the page, but I can't copy the data and paste it into excel. Could anyone help me?

Sub extract_table()
Dim IE As Object
Dim i As Long
Set IE = CreateObject("InternetExplorer.application")
IE.Visible = True
IE.Navigate "https://fnet.bmfbovespa.com.br/fnet/publico/visualizarDocumento?id=53959"
Do While IE.Busy
Loop



End Sub
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">hello, i am trying to copy an internet data table in excel. I can open the page, but I can't copy the data and paste it into excel. Could anyone help me?</body>

 

[Domenic]

The following macro needs to be placed in a regular module. The macro will first copy the contents of the specified URL to the clipboard, and then paste the contents of the clipboard to a newly created worksheet within the workbook running the code.

Note that you'll need to set a reference to 3 libraries. First, go the Visual Basic Editor (Alt+F11), then select Tools, and then select References. Under 'Available references', check/select Microsoft Internet Controls, and Microsoft HTML Object Library. For the third one, you probably won't find it listed under 'Available references'. You'll need to browse and select the appropriate file, which would be FM20.dll. So click on the Browse button, and then locate and select the file. On my computer the file is located in c:\windows\syswow64 . Then, once you're finished, click on OK.

Let me know if you have any questions.

Hope this helps!

'Force the explicit declaration of variablesOption Explicit


Sub GetMonthlyReport()


    'Set a reference (VBE > Tools > References) to the following libraries:
    '   1) Microsoft Internet Controls
    '   2) Microsoft HTML Object Library
    '   3) Microsoft Forms Object Library
    
    'Declare the variables
    Dim ie As SHDocVw.InternetExplorer
    Dim doc As MSHTML.HTMLDocument
    Dim body As MSHTML.HTMLElementCollection
    Dim clipBoard As MSForms.DataObject
    Dim resultsSheet As Worksheet
    
    'Create an instance of Internet Explorer
    Set ie = New SHDocVw.InternetExplorer
    
    'Navigate to the URL
    With ie
        .Visible = True
        .navigate "https://fnet.bmfbovespa.com.br/fnet/publico/visualizarDocumento?id=53959"
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    'Create a new instance of the html document
    Set doc = New MSHTML.HTMLDocument
    
    'Set the Internet Explorer document
    Set doc = ie.document
    
    'Set the body of the html document
    Set body = doc.frames(0).document.getElementsByTagName("body")(0)
    
    'Create a new instance of the clipboard
    Set clipBoard = New MSForms.DataObject
    
    'Copy the body html to the clipboard
    clipBoard.SetText body.outerHTML
    clipBoard.PutInClipboard
    
    'Add a new worksheet to this workbook for the results
    Set resultsSheet = ThisWorkbook.Worksheets.Add
    
    'Paste the contents of the clipboard to the results sheet, and format
    With resultsSheet
        .Range("A1").PasteSpecial
        With .UsedRange
            .WrapText = False
            .EntireColumn.ColumnWidth = 20
        End With
        .Activate
        .Cells(1).Select
    End With
       
    'Clear objects from memory
    Set ie = Nothing
    Set doc = Nothing
    Set body = Nothing
    Set clipBoard = Nothing
    Set resultsSheet = Nothing
    
End Sub

 

[texrx]

excellent, thank you very much!
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">excellent, thank you very much!</body>

 

[Domenic]

You're very welcome. Glad I could help.

Cheers!