QueryTables - Excel 2000 to 2002 migration

phil_valentine@hotmail.com · 02-23-2006, 07:50 AM

Hi, I'm hoping someone here can help.

I have some vba code that I wrote to retreive data from a web page. I
wrote it using QueryTables when I had Excel 2000 installed and all is
happy.
However I have now upgraded to Excel 2002 and when the code doesn't
work.
To start debugging I tried to get the QueryTable manually and it
doesn't work. Instead I get the following error:

File cannot be opened because:
Reference to undefined entity 'nbsp'.
Line 83, Position 52.
<td align="top" rowspan="3" width="40"> </td>
[OK]

If I click on the OK button the webpage is then loaded with the html
code for the webpage, rather than the table I tried to import.

If I uninstall Excel 2002 and reinstall Excel 2000 then the query runs
fine, so I know nothing has changed on the webpage. My question is has
something changed in the way that Excel now access the Query?

Any suggestions anyone?

Thanks,

Phil

**Phil_V** · 02-24-2006, 06:12 AM

If anyone can take a look I would, appreciate it, this is the query that doesn't work for me under Excel XP, but works fine under 2000:

Sub check_date()
' Checks the 'online' date against the last time the query was run to see if worth while
  Dim new_date As String
  
  'Create worksheet for the query
  Worksheets.Add.Move after:=Worksheets(2)
  With Worksheets(3)
    With .QueryTables.Add(Connection:= _
        "URL;http://ccgi.kjcgames.com/1024/dataStore/markets.php" _
        , Destination:=.Range("A1"))
        .Name = "markets.php"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With
    new_date = Mid(Cells(6, 2), InStr(Cells(6, 2), "updated") + 8)  'Get date and time of update
    'Delete Scratch sheet
    Application.DisplayAlerts = False
    .Delete
    Application.DisplayAlerts = True
  End With
  rubish = MsgBox(new_date)
End Sub

Thanks all,

Phil