PDA

View Full Version : Problem importing data from an xml file



ukdane
06-16-2009, 03:13 AM
I have an xml file, which was generated in Excel.
When I import it back into excel at a later date, not all the data is being returned.
Below is the code that reimports the xml file.

Sub TEST()

Dim strFileName As String

strFileName = "mypath\test.XML"


If Dir(strFileName) <> "" Then


ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;" & strFileName, _
Destination:=Range("$A$1"))
.Name = "ImportedXML"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.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

Else
End If
End Sub

Through a process of elimination, I have discovered that the code above Imports x details from the xml file. x SHOULD be the same as what was exported from excel in the first instance, however if the xml data contains a number, it imports the details twice (with the second instance being called #agg). This means that when the file is imported back, it is short of the details by 1 (for every instance a number is returned).

I can't provide the exact workbook, as it contains sensitive detail, however I have provided the code above, and below is an example of an xml file.

I hope someone can help answer my problem, as I need to find a solution as quickly as possible.
Thanks.



<?xml version="1.0" ?>
- (file:///C:/Documents%20and%20Settings/robin.lees/Desktop/test.XML#) <data>
- (file:///C:/Documents%20and%20Settings/robin.lees/Desktop/test.XML#) <General>
<ComboBoxClaimtype>TEXT</ComboBoxClaimtype>

<TextBoxClaimAmount>10000</TextBoxClaimAmount>

<TBGnrlCNo>TEXT</TBGnrlCNo>

<TBGnrlDDate>01-03-2007</TBGnrlDDate>

<OptionInsuranceYes>0</OptionInsuranceYes>

<OptionInsuranceNo>1</OptionInsuranceNo>

<TextBoxInsuranceCompany>Insurance Company...</TextBoxInsuranceCompany>

<ComboBoxBranch>TEXT</ComboBoxBranch>

<TBGnrlEmail>EMAIL</TBGnrlEmail>

<TBGnrlName>NAME</TBGnrlName>

<ComboBoxInsuranceCompany>TEXT</ComboBoxInsuranceCompany>

<TBGnrlCLink>123456789101112</TBGnrlCLink>

<TBGnrlDescription>TEXT</TBGnrlDescription>

<TBGnrlDepartment>TEXT</TBGnrlDepartment>

<TBGnrlCDate>01-10-2008</TBGnrlCDate>

</General>


- (file:///C:/Documents%20and%20Settings/robin.lees/Desktop/test.XML#) <Claimant>
<OptionButton1>1</OptionButton1>

<OptionButton2>0</OptionButton2>

</Claimant>


- (file:///C:/Documents%20and%20Settings/robin.lees/Desktop/test.XML#) <Transport>
<ComboBoxTrans5>N/A</ComboBoxTrans5>

<ComboBoxTrans1>TEXT</ComboBoxTrans1>

<TextBoxTrnsComp1>TEXT</TextBoxTrnsComp1>

<TextBoxTrnsRef1>TEXT</TextBoxTrnsRef1>

<TextBoxTrnsContact1>NAME</TextBoxTrnsContact1>

<TextBoxTrnsEmail1>EMAIL</TextBoxTrnsEmail1>

<TextBoxTrnsAddress1>ADDRESS</TextBoxTrnsAddress1>

<ComboBoxTrans2>TEXT</ComboBoxTrans2>

<TextBoxTrnsComp2>TEXT</TextBoxTrnsComp2>

<ComboBoxTrans3>TEXT</ComboBoxTrans3>

<TextBoxTrnsComp3>TEXT</TextBoxTrnsComp3>

<ComboBoxTrans4>TEXT</ComboBoxTrans4>

<TextBoxTrnsComp4>TEXT</TextBoxTrnsComp4>

</Transport>


</data>




In the above xml file, there are 30 lines of information that should be returned. However there are also 6 numbers each of which is returned twice. This means that when the xml file is read back into excel using the above code, it is missing 6 pieces of information.

Hope you can help.
Thanks again.