![]() |
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