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.
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.