QueryTables.Add and XML

[Sönke Schreiber]

Hello NG,

we encounter a complex problem when importing XML data into an Excel
worksheet. From an external application we generate XML files that follow
this structure:

<?xml version="1.0" encoding="ISO-8859-1"?>
<Workbook xmlns="urn:schemas-microsoft-comffice:spreadsheet"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">

<Worksheet ss:Name="Liste">
<Table>
<Row>
<Cell> <Data ss:Type="String">Data 1</Data> </Cell>
<Cell> <Data ss:Type="String">Data 2</Data> </Cell>
<Cell> <Data ss:Type="String">Data 3</Data> </Cell>
<Cell> <Data ss:Type="String">Data 4</Data> </Cell>
<Cell> <Data ss:Type="String">Data 5</Data> </Cell>
<Cell> <Data ss:Type="String">Data 6</Data> </Cell>
<Cell> <Data ss:Type="String">Data 7</Data> </Cell>
<Cell> <Data ss:Type="String">Data 8</Data> </Cell>
<Cell> <Data ss:Type="String">Data 9</Data> </Cell>
<Cell> <Data ss:Type="String">Data 10</Data> </Cell>
<Cell> <Data ss:Type="String">Data 11</Data> </Cell>
<Cell> <Data ss:Type="String">Data 12</Data> </Cell>
</Row>
<Row>
<Cell> <Data ss:Type="String">Data 1</Data> </Cell>
<Cell> <Data ss:Type="String">Data 2</Data> </Cell>
<Cell> <Data ss:Type="String">Data 3</Data> </Cell>
<Cell> <Data ss:Type="String">Data 4</Data> </Cell>
<Cell> <Data ss:Type="String">Data 5</Data> </Cell>
<Cell> <Data ss:Type="String">Data 6</Data> </Cell>
<Cell> <Data ss:Type="String">Data 7</Data> </Cell>
<Cell> <Data ss:Type="String">Data 8</Data> </Cell>
<Cell> <Data ss:Type="String">Data 9</Data> </Cell>
<Cell> <Data ss:Type="String">Data 10</Data> </Cell>
<Cell> <Data ss:Type="String">Data 11</Data> </Cell>
<Cell> <Data ss:Type="String">Data 12</Data> </Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

Then we automatically import the data contained in such files into an Excel
worksheet using the following VBA code that was developed by recording a
macro (in Excel 2003 SP2 - Office 2003 Standard Edition):

' Daten importieren
With ActiveSheet.QueryTables.Add(Connection:= _
"Finder;" & xml_source,
Destination:=Worksheets("Liste").Range(einfuege_pos))
.Name = "Liste"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

ActiveSheet.QueryTables(1).Delete

When running the following Excel versions this works smoothly:

Excel XP (Office XP Professional)
Excel 2003 (Office 2003 Standard) SP2
Excel 2003 (Office 2003 Prof.) SP2 Release 11.6560.6568

But one customer uses this Excel version

Excel 2003 (Office 2003 Prof.) Release 11.8012.6568

and encounters an VBA error:

Run-time error 1004 - application-defined or object-defined error

in line

With ActiveSheet.QueryTables.Add(Connection:= _
"Finder;" & xml_source,
Destination:=Worksheets("Liste").Range(einfuege_pos))

Unfortunately we can not command our customers to use a specific Excel
version. So there are some questions that are needed to be answered:

1. What is the reason of this VBA error?
2. Does this error directly depend on an Excel version? If so, is there any
compatibility setting?
3. Is there any way to import XML data into an Excel worksheet that can be
used with all Excel versions newer than 2000?

Is anybody out there who is able to answer these questions?

Thanks in advance.

Kind regards from Germany

Soenke Schreiber