I am trying to import a table of a web page to a spreadsheet. It works
well
in Excel 2000. I even have a macro for the import. But after moving to
Excel
xp, the macro does not work anymore. Here is the coding of my macro:

Sub Get_page(conn)
With ActiveSheet.QueryTables.Add(Connection:=conn,
Destination:=ActiveCell)
.Name = "HKEX stock lots"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
' .QueryType = xlWebQuery
.WebSelectionType = xlSpecifiedTables
.WebTables = "4"
.WebFormatting = xlNone
.Refresh
End With
End Sub


I called Get_page with
Get_page("URL;http://www.hkex.com.hk/tradinfo/stockcode/
eisdgemw.htm"). But
it failed to import the table I want. Instead, it insert the whole
page into
ActiveCell. I commented the ".QueryType=xlWebQuery" because it failed
with
invalid argument or parameter.


I tried to import by handle instead of the macro. I use Date - Import
External Data - New Web Query - put the url in Address - Go - the
page
displayed - I select the table I want - Import. - OK. But it failed
with
"File cannot be opened because Reference to undefined entity 'nbsp'
Line 14,
Position 568". The table does have some text in some of its cells
which is standard HTML.


Same web page does not have any problem in Excel 2000. Can anybody
help?

I would record the macro and post the results if you need more help. In
Excel 2003 do the following

1) Start Recording from Worksheet menu Tools - Macro - Start Recording
2) Perform Webquery from worksheet menu Data - Import External Data -= New
webquery. then place your URL in the address box and press go.
3) Choose the table on the webpage and press Import button
4) Stop Recording from worksheet menu Tools - Macro - Stop Recording.
5) Modfiy the recorded macro as necessary to work with the rest of you code.

"senderj" wrote:

I am trying to import a table of a web page to a spreadsheet. It works
well
in Excel 2000. I even have a macro for the import. But after moving to
Excel
xp, the macro does not work anymore. Here is the coding of my macro:

Sub Get_page(conn)
With ActiveSheet.QueryTables.Add(Connection:=conn,
Destination:=ActiveCell)
.Name = "HKEX stock lots"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
' .QueryType = xlWebQuery
.WebSelectionType = xlSpecifiedTables
.WebTables = "4"
.WebFormatting = xlNone
.Refresh
End With
End Sub


I called Get_page with
Get_page("URL;http://www.hkex.com.hk/tradinfo/stockcode/
eisdgemw.htm"). But
it failed to import the table I want. Instead, it insert the whole
page into
ActiveCell. I commented the ".QueryType=xlWebQuery" because it failed
with
invalid argument or parameter.


I tried to import by handle instead of the macro. I use Date - Import
External Data - New Web Query - put the url in Address - Go - the
page
displayed - I select the table I want - Import. - OK. But it failed
with
"File cannot be opened because Reference to undefined entity 'nbsp'
Line 14,
Position 568". The table does have some text in some of its cells
which is standard HTML.


Same web page does not have any problem in Excel 2000. Can anybody
help?

Ron,

Thanks alot. Your coding works great. With some mod, I am able to put
the data of the web table into my sheet. But it needs a lot of coding.
My original QueryTables.Add method automatically put the web tables
into Excel cells. Anyway, now that I can put the web table as a string
in my coding. Is there a way to automatically parse the cells of the
table into Excel cells?

There are many statements in your coding that are very useful when
accessing a web page from Excel macro, but I am not able to find
further info in the Excel help or VB help. I like to know more about
the classes and methods of the ie object (e.g. getElementsByTagname,
innertext etc)?