QueryTable for url works in 2000, but not in xp

S

senderj

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?
 
J

Joel

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.
 
S

senderj

Thanks Joel. This is exactly what I did to obtain the code related to
webquery. But I found that my macro works in excel 2000, not in excel
xp. However, I've further test my macro and found that it doesn't work
only when I access the URL I want (as found in my first post). Other
URLs work ok. I further examined the html of the url and found that
there is a <?xml version="1.0"?> before <html>. I saved the source
code, removed the <?xml> tag, stored it in my web site and retrieved
it with my marco and it worked. Does Excel macro syntax provide any
means to ignore this <?xml> tag??
 
J

Joel

I found an article at the microsoft webpage that may be related to your problem

http://support.microsoft.com/kb/303487

I would make sure you have all the latest updates to windows XP on your PC
as well as your internet explorer.

I don't think there is a way to for the query to ignore the XML Statement.
You could download the URL onto your PC using FTP, open the files a remove
the XML, and then run the URL from your PC. Lots of work. Better to just
open the URL with an internet explorer.

Does the URL open with your webbrowser? An alternative of the query would
be to open an Internet explorer application and get the table from internet
application.

I would also try to eliminate so extra lines that the macro recorded added
to your code.

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
 
R

ron

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?
Click to expand...

You could try something like this. The line

y = Len(doc_tables)

tells you there are 8 tables on the web page (0 through 7). Once you
know this you can comment this line out. In the line where you assign
the table innertext to a variable, you might have to try different
table numbers to get the specific table you are interested in. Once
your table is assigned to a variable you can do whatever you want.
Using text parsing functions like "instr" and "mid" are often the best
way to get the specific information you are interested in. I've just
put in lines to view the variable content in a window and then assign
it to a cell...Ron

Sub Get_Table_Contents()

' Open IE
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://www.hkex.com.hk/tradinfo/stockcode/
eisdgemw.htm"
.Top = 50
.Left = 530
.Height = 400
.Width = 400

' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop
End With

Set doc_tables = ie.Document.getElementsByTagname("table")

' How many tables are there?
y = Len(doc_tables)

'Assign table of interest to a variable
my_var = doc_tables(4).innertext

' Look at variable content in immediate window
Debug.Print my_var

' Enter variable content into worksheet
Range("A1") = my_var

End Sub
 
S

senderj

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)?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do you importing unicode from web 2
Recorded Excel Macro not working 1
How to obtain the row count of a QueryTable from a Web query 4
Web query - one table multiple pages of data 1
Error message in Web Query written in VBA 1
Get external data by dynamical url -> no update 3
Macro to retrieve stock quotes in Excel 2007 1
How to call Excel Macro in VB6.0 2

Top