ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   macro works in Excel 2k but not in Excel xp (https://www.excelbanter.com/excel-programming/309352-macro-works-excel-2k-but-not-excel-xp.html)

Sender Jones

macro works in Excel 2k but not in Excel xp
 
I have a macro that works well in Excel 2k but not in Excel xp. The macro
use QueryTables to read a web page from a local stock exchange web site.
Here is the related part of it:

conn1 = "URL;http://www.hkex.com.hk/tradinfo/stockcode/eisdwarr.htm"
Get_page (conn1)

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 = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebTables = 5
.WebFormatting = xlNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh
.BackgroundQuery = False
End With
End Sub

When running in Excel 2k it works. When running in Excel xp, it is not able
to parse the html into rows and columns. I've examine the page and found
that there is an <?xml version="1.0"? tag at the beginning of the page,
before <html. I stored the page in local disk, remove the xml tag and
change the macro to read the local file, then it works. Obviously, I cannot
change the web site. Is there a way to make my macro ignore the xml tag? Any
parm in the QueryTables that can do this. I've read the Excel help but
couldn't find any. Please help.





All times are GMT +1. The time now is 01:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com