Parse HTML code, create a table

[bg18461]

I have a function that locates a table on a webpage and pulls the html code into one cell in a worksheet. Basically we can call this one cell a text file. I need to parse through this text file (cell A1) to create a table. This text file only has info for 1 table, the table always has 12 columns, the rows are variable. I would like it to then write back this parsed text file back into excel as a table, say starting in cell A2 on the same worksheet. Please I need some help with this.

P.S. The html code that is extracted to cell A1 uses all the standard tags, TABLE, TR, TD, etc...

 

[CautionMP]

bg18461,
You could parse the HTML yourself...

Here is a sample that uses Internet Explorer to navigate to a page, find a specific table, then iterates through the rows and cells dumping the innerText to the active worksheet.

Sub WebTableToSheet()
  'Tested using IE7,  Excel 2000 SP1, and Windows XP
  Dim objIE As Object
  Dim varTables, varTable
  Dim varRows, varRow
  Dim varCells, varCell
  Dim lngRow As Long, lngColumn As Long
  Dim strBuffer As String
  
  Set objIE = CreateObject("InternetExplorer.Application")
 
  With objIE
    .AddressBar = False
    .StatusBar = False
    .MenuBar = False
    .Toolbar = 0
    .Visible = True
    .Navigate "[URL unfurl="true"]http://finance.yahoo.com/q/hp?s=%5EDJI"[/URL]
  End With
  

  While objIE.Busy
  Wend
  While objIE.Document.ReadyState <> "complete"
  Wend
  
  Set varTables = objIE.Document.All.tags("TABLE")
  For Each varTable In varTables
    'Use the innerText to see if this is the table we want.
    If varTable.innerText Like "DateOpenHighLowCloseVolumeAdj Close*" Then
      Set varRows = varTable.Rows
      lngRow = 2 'This will be the first output row
      For Each varRow In varRows
        Set varCells = varRow.Cells
        lngColumn = 1 'This will be the output column
        For Each varCell In varCells
          ActiveSheet.Cells(lngRow, lngColumn) = varCell.innerText
          lngColumn = lngColumn + 1
        Next varCell
        lngRow = lngRow + 1
      Next varRow
    End If
  Next varTable
  
Cleanup:
  Set varCell = Nothing: Set varCells = Nothing
  Set varRow = Nothing: Set varRows = Nothing
  Set varTable = Nothing: Set varTables = Nothing
  objIE.Quit
  Set objIE = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[cresbydotcom]

I do this kind of thing for ordering (Farnell & RS Components - in the UK).

They change their website every so often so it is a bit hit and miss.

Basically I highlight the area of interest on the web page, copy manually and then in the spreadsheet the macro does the rest.

In VBA - first paste (no special pastes) into a location (HTML and all), then inspect the cells for words that matter like "total" (in case they move about.) and reference from there. Then write text values from cells to my columns/sheets until another "word" is found like "order reference"

Then on another button I sort-out totals because they add-up differently for shipping etc. and copy the lot into clipboard.

With the data in the clipboard it can be pasted in a Word Macro or whatever.

It is clunky but no simple macro will cope if the source format changes.

Copy Special (text) from the website does not work for me. And I did not want to have to unpick their perl/php generated DHTML. Though in another spreadsheet I do read in VBA the whole "text" (sans HTML) on TV schedules from a webpage and look for delimiter words in one humungous string. But that text is more regular.

there is a tide in the affairs of man that you Cnut ignore.................