extract data tables from html files into excel worksheet

[abhay_547]

I got the below macro from a old thread. I have the lines on the html page files saved on c drive, which are supposed to be in a table format but they are not and there is no separator between each column data except some undefined spaces as shown in below image, so how can i import it as a proper table .i.e. into separate columns in the excel file, also i have multiple such html files so can i import multiple files into one worksheet at one go.

Sub WebScraping()

    Dim x As Integer, p As Integer, MyTable As Object    'MSHTML.HTMLTable


    ActiveSheet.UsedRange.Clear


    On Error GoTo ErrH


    s = Timer


    With CreateObject("InternetExplorer.Application")


        .Navigate "http://www.beb.de/internet/servlet/content/5868/StartpageEnglish/Gasstorage/Capacities/Storageutilization/Harsefeld.html"


        Do While .Busy: Loop


        'Loop through all the elements in the document
        For Each MyTable In .Document.getElementsByTagName("table")
            With MyTable
                If .className = "inntertab" Then
                    ReDim q(.Rows.Length - 1, 5)
                    For x = 0 To .Rows.Length - 1
                        For p = 0 To .Rows(x).Cells.Length - 1
                            q(x, p) = .Rows(x).Cells(p).innerText
                        Next
                    Next
                End If
            End With


        Next


        [A1].Resize(x, p) = q


ErrH:         .Quit


    End With


    Set MyTable = Nothing: Erase q


    MsgBox Round(Timer - s, 2)


End Sub

 

[Paul Ked]

Cross posted here http://www.vbaexpress.com/forum/showthread.php?61817-Import-HTML-Data-into-excel

 

[Paul Ked]

But the code is different!!

 

[abhay_547]

But the code is different!!

yep, was simultaneously checking with some other code and had cross posted but I can close either one of them if it's required and follow only one thread.

 

[abhay_547]

I got the below macro from a old thread. I have the lines on the html page files saved on c drive, which are supposed to be in a table format but they are not and there is no separator between each column data except some undefined spaces as shown in below image, so how can i import it as a proper table .i.e. into separate columns in the excel file, also i have multiple such html files so can i import multiple files into one worksheet at one go.

Sub WebScraping()

    Dim x As Integer, p As Integer, MyTable As Object    'MSHTML.HTMLTable


    ActiveSheet.UsedRange.Clear


    On Error GoTo ErrH


    s = Timer


    With CreateObject("InternetExplorer.Application")


        .Navigate "http://www.beb.de/internet/servlet/content/5868/StartpageEnglish/Gasstorage/Capacities/Storageutilization/Harsefeld.html"


        Do While .Busy: Loop


        'Loop through all the elements in the document
        For Each MyTable In .Document.getElementsByTagName("table")
            With MyTable
                If .className = "inntertab" Then
                    ReDim q(.Rows.Length - 1, 5)
                    For x = 0 To .Rows.Length - 1
                        For p = 0 To .Rows(x).Cells.Length - 1
                            q(x, p) = .Rows(x).Cells(p).innerText
                        Next
                    Next
                End If
            End With


        Next


        [A1].Resize(x, p) = q


ErrH:         .Quit


    End With


    Set MyTable = Nothing: Erase q


    MsgBox Round(Timer - s, 2)


End Sub

can anyone advise on this