Home Excel VBA - Copy table data from website and put into excel?
Reply: 2

Excel VBA - Copy table data from website and put into excel?

Mark OB
1#
Mark OB Published in 2017-12-07 12:16:21Z

I am trying to copy a table from a website and put this into excel. My table can be identified by the elementID "VisibleReportContentctl32".

For some reason, my code produces this error: 'object required'

on this line: Range("A1").Text = dat

Here's my full code:

Option Explicit
Public Sub GetSSRSData()

    Dim IE            As Object: Set IE = New InternetExplorerMedium
    Dim TR_Elements   As Object
    Dim TR            As Object ' Table Row
    Dim TD_Elements   As Object
    Dim TD            As Object ' Table Data
    Dim RowNumb       As Integer
    Dim Columns       As Integer
    Dim ColumnNumb    As Integer
    Dim x As Integer
    Dim dat As String

    With IE
        .Visible = True
        .Navigate ("http://gbrlon02-sql-17/Reports/Pages/Report.aspx?ItemPath=%2fCutlass+Reports%2fManagement+Reporting%2fForwardOrdersSticksOnly+-+ForecastVariance+(Monthly+Report)")

         Do While IE.Busy Or IE.ReadyState <> 4
             DoEvents
         Loop

         If IsObject(.Document.getElementById("VisibleReportContentctl32_ctl09")) Then
            dat = IE.Document.getElementById("VisibleReportContentctl32_ctl09").innerHTML
            Range("A1").Text = dat
         Else
            MsgBox "doesn't"
         End If
   End With
End Sub

please can someone show me where I am going wrong?

Eduard
2#
Eduard Reply to 2017-12-07 13:49:38Z

Use [Power Query][Table from web] is simple and stable

ryguy72
3#
ryguy72 Reply to 2017-12-08 20:04:34Z

That URL doesn't work for me. Your code should look something like this...

Option Explicit 
Sub gethtmltable() 
    Dim objWeb As QueryTable 
    Dim sWebTable As String 
     'You have to count down the tables on the URL listed in your query
     'This example shows how to retrieve the 2nd table from the web page.
    sWebTable = 2 
     'Sets the url to run the query and the destination in the excel file
     'You can change both to suit your needs
    Set objWeb = ActiveSheet.QueryTables.Add( _ 
    Connection:="URL;http://www.vbaexpress.com/kb/default.php", _ 
    Destination:=Range("A1")) 

    With objWeb 

        .WebSelectionType = xlSpecifiedTables 
        .WebTables = sWebTable 
        .Refresh BackgroundQuery:=False 
        .SaveData = True 
    End With 
    Set objWeb = Nothing 
End Sub 

You just need to identify which specific table you want to import data from.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.25135 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO