+ Reply to Thread
Results 1 to 13 of 13

VBA to Import HTML Table Locally Stored HTML file to excel

  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    VBA to Import HTML Table Locally Stored HTML file to excel

    Kindly help me on how to import html table to excel using VBA without using the query capability of excel.

    the html table format goes like this

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Here's one way:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Thanks, you're generous as always.

  4. #4
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Kyle,

    I having trouble extracting the table of the following files.

    QUOTE_A.zip

    For TIMESALES.html
    There is error in this line of the code:

    Please Login or Register  to view this content.
    For the QOUTES_A.html
    How to separate the multiple tables, imported to a sheet same formatting using queries.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Re: VBA to Import HTML Table Locally Stored HTML file to excel


    Hi,

    by affecting the collection getElementsByTagName("TABLE") to a variable,
    you can search in debug mode in the Local variables VBE window the right table.

    But notice that Element #1 in a collection from that window is the item zero in VBA …

  6. #6
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Got it!!! Thanks Marc

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    When tables are within a main table, easy way is to directly copy main table !

    Code to paste in worksheet module (just amend FILE constant) :

    PHP Code: 
    Sub Demo4QUOTE()
         Const 
    FILE$ = "D:\Tests4Noobs\QUOTE_A.html"
        
    If Dir(FILE) = "" Then Beep: Exit Sub

        With CreateObject
    ("HTMLFile")
            .
    Write CreateObject("Scripting.FileSystemObject").OpenTextFile(FILE).ReadAll

            
    If .frames.clipboardData.setData("Text", .getElementsByTagName("TABLE")(0).outerhtmlThen
                Application
    .ScreenUpdating False
                Cells
    (1).CurrentRegion.Clear
                Me
    .Paste [B2]
                .
    frames.clipboardData.clearData "Text"
                
    With [B2].CurrentRegion:  .WrapText False:  .Columns("G:H").AutoFit:  End With
            End 
    If
        
    End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  8. #8
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Kyle,

    I'm getting an error If I set this to variable
    Please Login or Register  to view this content.
    I am importing several html files (table) to each of the corresponding sheets.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Change:
    Please Login or Register  to view this content.
    To:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Thanks Kyle,

    This code works only for the specific sheet.

    Question, how can I loop to each sheets based of the values of range at C2:C277)?

    Please Login or Register  to view this content.

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Well how would Excel know what the sheet name is from the value in the cell, presumably the sheet name isn't the same as the file path

  12. #12
    Registered User
    Join Date
    10-02-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    The file path goes like this C:\TRANSACTIONS\QUOTES_ALL\AAA.html (this range is located in colum C)
    And I named all the sheets to AAA (located in Column A)

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: VBA to Import HTML Table Locally Stored HTML file to excel

    Maybe:
    PHP Code: 
    Sub QUOTES_ALL_IMPORT()

        
    Dim oCell As ObjectoRow As Object
        Dim html 
    As String
        Dim x 
    As LongAs Long
        Dim lRow 
    As Long
            
        Dim filePath 
    As String
        Dim LISTED_COMP 
    As Variant
        Dim C_LISTED_COMP 
    As Range
        
        Dim htm 
    As Object
        
        Set htm 
    CreateObject("htmlFile")
        
            
        
    LISTED_COMP Sheets("LISTED_COMP").Range("A2:C277").Value
             
        
    For lRow LBound(LISTED_COMPTo UBound(LISTED_COMP)
        
                
    Open LISTED_COMP(lRow3) For Input As #1
                     
    html Input(LOF(1), 1)
                
    Close
        
        
    'STOCK DATA
        
            With htm
                x = 1: y = 8
                .body.innerhtml = html
                For Each oRow In .getelementsbytagname("table")(5).Rows
                    For Each oCell In oRow.Cells
                        Sheets(LISTED_COMP(lRow, 1)).Cells(x, y).Value = oCell.innertext
                        y = y + 1
                    Next oCell
                    y = 8
                    x = x + 1
                Next oRow
            End With
        
        Next lRow

    End Sub 

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. HELP Import data from attached HTML file to excel
    By ExcelUser2707 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-25-2013, 09:24 AM
  2. Import HTML table to Excel... without line breaks
    By amsgwp in forum Excel General
    Replies: 7
    Last Post: 08-02-2013, 02:14 PM
  3. HTML Table import
    By Ryan Hafey in forum Excel General
    Replies: 2
    Last Post: 08-03-2006, 01:10 AM
  4. auto import a table into excel from a html
    By ceemo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2006, 11:50 AM
  5. [SOLVED] Import HTML table
    By Bill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2006, 01:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1