HTML table into Excel

[gamendorf]

I am having trouble figuring out how to copy a table from a web page into excel. I have looked everywhere online that I can think of,but to no avail.

Here is the what the table looks like in the source code:




<div class=”AccClass” id =
“AccId”><span id=”AccSpan”><table cellspacing=”4”>***tr and td code***<\table></span></div>





This is the only occurrences of class “AccClass” andids “AccId” and “AccSpan”.
Also, this isthe only table on the page, and all of the table code exists on a single lineof HTML. (I know that that is probably a horrible way of coding a table, but I’mjust the user…)

Here is a brief summary of my VBA code so far:

Sub
HTMLtable_to_Excel()

Dim ObjIe as Object

Dim HTMLDoc as HTMLDocument

Set ObjIe = CreateObject("InternetExplorer.Application")


ObjIe.Navigate(MyURL)

Set HTMLDoc = ObjIe.document

***WhateverVBA Code is needed to complete the task (currently empty because I have no idea how to do it! ***

End Sub






I will gladly provide any more information if this is not specific enough. Thanks in advance for any advice!

[shrivallabha]

Try the following (I am assuming you have Excel 2007+)
1. Start Macro Recorder
2. Data > From Web
3. It will ask for URL (I used: http://www.temperatureworld.com/ctable1.htm)
4. It will point the table with a dark border.
5. Select the Table
6. Follow the rest of the instructions.
7. Stop Recording.
Then you will get following macro:
Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.temperatureworld.com/ctable1.htm", Destination:=Range("$A$1") _
)
.Name = "ctable1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

[gamendorf]

Thanks for the quick response.

Is there any way to modify that so that the cell formatting will be preserved? Some of the cells are highlighted with

<td><span style="background:yellow">text</span></td>

Also, can it be adapted to work on an Internet Explorer object that is already in existence? I would prefer not to hard code the URL. I have already devised a function that will "hook" and IE instance that is already open and set it to the ObjIe object in my original code.

[shrivallabha]

Frankly, I do not have any experience in handling IE through VBA. So I am not the right person to answer it!

The way I'd try to work it out is:
1. Keep one hidden sheet where above query will reside and then
2. Either reference it in your formatted sheet
3. or Process it and then paste data.