Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Error When Opening XML File Created From Excel Spreadsheet Data
I have the following VBA Code to create an XML file from data in an Excel Spreadsheet. It creates the file, but if I try to open it, I get an error (see error posted after the VBA Code).
Error:
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------
XML document must have a top level element. Error processing resource 'file:///H:/NRTEcho/ACTIVE
What is the issue?
Thanks,
gdunn59
Public Function ExportToXML(FullPath As String, RowName As String) As Boolean
On Error GoTo ErrorHandler
Dim colIndex As Integer
Dim rwIndex As Integer
Dim asCols() As String
Dim oWorkSheet As Worksheet
Dim sName As String
Dim lCols As Long, lRows As Long
Dim iFileNum As Integer
Set oWorkSheet = ThisWorkbook.Worksheets(2)
sName = oWorkSheet.Name
lCols = oWorkSheet.Columns.Count
lRows = oWorkSheet.Rows.Count
ReDim asCols(lCols) As String
iFileNum = FreeFile
Open FullPath For Output As #iFileNum
For i = 0 To lCols - 1
'Assumes no blank column names
If Trim(Cells(1, i + 1).Value) = "" Then Exit For
asCols(i) = Cells(1, i + 1).Value
Next i
If i = 0 Then GoTo ErrorHandler
lCols = i
Print #iFileNum, "<?xml version=""1.0""?>"
Print #iFileNum, "<" & sName & ">"
For i = 2 To lRows
If Trim(Cells(i, 1).Value) = "" Then Exit For
Print #iFileNum, "<" & RowName & ">"
For j = 1 To lCols
If Trim(Cells(i, j).Value) <> "" Then
Print #iFileNum, " <" & asCols(j - 1) & "><![CDATA[";
Print #iFileNum, Trim(Cells(i, j).Value);
Print #iFileNum, "]]></" & asCols(j - 1) & ">"
DoEvents 'OPTIONAL
End If
Next j
Print #iFileNum, " </" & RowName & ">"
Next i
Print #iFileNum, "</" & sName & ">"
ExportToXML = True
ErrorHandler:
If iFileNum > 0 Then Close #iFileNum
Exit Function
End Function
Error:
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------
XML document must have a top level element. Error processing resource 'file:///H:/NRTEcho/ACTIVE
What is the issue?
Thanks,
gdunn59
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The error is excel trying to interpret your filename as an XML map, you need to create an XML map so that excel knows how to load the data when it opens the xml file.
To create the map you can use the MS XML tool add-in and in excel help search for 'create xml map' and it will guide you and also tell you how to reference it in your data.
Here's the tool in case you haven't got it
xml addin
To create the map you can use the MS XML tool add-in and in excel help search for 'create xml map' and it will guide you and also tell you how to reference it in your data.
Here's the tool in case you haven't got it
xml addin
I just tested this with a few rows of data and it seems to be fine. Perhaps there is a problem with certain items in your data? I tried with just a, b, c, d, e, f (each cell a single character) in the cells.
jkpieterse,
I've attached a spreadsheet that contains dummy data and has the VBA code and macro that I'm using.
Maybe you can download it and see what is wrong, since you said the code is working for you.
Much appreciated!
gdunn59
Test.xlsm
I've attached a spreadsheet that contains dummy data and has the VBA code and macro that I'm using.
Maybe you can download it and see what is wrong, since you said the code is working for you.
Much appreciated!
gdunn59
Test.xlsm
after some work on the code to get it to run the error message that came up this time was 'invalid character in name' which was the space in the 'Date submitted' field (sheet 2) and 'Submitted Date' (sheet1).
But this is not the same as the error you reported in your original post
Copy-of-Test.xlsm
But this is not the same as the error you reported in your original post
Copy-of-Test.xlsm
regmigrant,
I didn't get the error about 'invalid character in name'.
So the attachment you attached, is it working or not?
Thanks,
gdunn59
I didn't get the error about 'invalid character in name'.
So the attachment you attached, is it working or not?
Thanks,
gdunn59
jkpieterse,
Do you have time to look at the Test file I uploaded since you said you were able to get yours to work?
Thanks,
gdunn59
Do you have time to look at the Test file I uploaded since you said you were able to get yours to work?
Thanks,
gdunn59
The test file works if you get these things right:
1. The code you posted above starts on row 1, but your data in the testfile starts on row 7. Adapt the code to cater for that
2. The headings may not contain any spaces (tag names in xml cannot contani space characters).
3. The number of rows and columns is wrong, you're using all rows and columns of the entire worksheet. It is better to use the listobject (the table). Like so:
1. The code you posted above starts on row 1, but your data in the testfile starts on row 7. Adapt the code to cater for that
2. The headings may not contain any spaces (tag names in xml cannot contani space characters).
3. The number of rows and columns is wrong, you're using all rows and columns of the entire worksheet. It is better to use the listobject (the table). Like so:
Public Function ExportToXML(FullPath As String, RowName As String) As Boolean
On Error GoTo ErrorHandler
Dim colIndex As Integer
Dim rwIndex As Integer
Dim asCols() As String
Dim oWorkSheet As Worksheet
Dim sName As String
Dim lCols As Long, lRows As Long
Dim iFileNum As Integer
Set oWorkSheet = ThisWorkbook.Worksheets(1)
sName = oWorkSheet.Name
lCols = oWorkSheet.ListObjects(1).ListColumns.Count
lRows = oWorkSheet.ListObjects(1).ListRows.Count
ReDim asCols(lCols) As String
iFileNum = FreeFile
Open FullPath For Output As #iFileNum
For i = 0 To lCols - 1
'Assumes no blank column names
If Trim(oWorkSheet.ListObjects(1).HeaderRowRange.Cells(1, i + 1).Value) = "" Then Exit For
asCols(i) = oWorkSheet.ListObjects(1).HeaderRowRange.Cells(1, i + 1).Value
Next i
If i = 0 Then GoTo ErrorHandler
lCols = i
Print #iFileNum, "<?xml version=""1.0""?>"
Print #iFileNum, "<" & sName & ">"
For i = 2 To lRows
If Trim(oWorkSheet.ListObjects(1).DataBodyRange.Cells(i, 1).Value) = "" Then Exit For
Print #iFileNum, "<" & RowName & ">"
For j = 1 To lCols
If Trim(Cells(i, j).Value) <> "" Then
Print #iFileNum, " <" & asCols(j - 1) & "><![CDATA[";
Print #iFileNum, Trim(oWorkSheet.ListObjects(1).DataBodyRange.Cells(i, j).Value);
Print #iFileNum, "]]></" & asCols(j - 1) & ">"
DoEvents 'OPTIONAL
End If
Next j
Print #iFileNum, " </" & RowName & ">"
Next i
Print #iFileNum, "</" & sName & ">"
ExportToXML = True
ErrorHandler:
If iFileNum > 0 Then Close #iFileNum
Exit Function
End Function
Jkpieterse,
Ok. I will try what you suggested, but I won't be able to try it until Monday. I'm leaving in the morning driving to Utah for my Aunts funeral.
Also, I don't really have time right now to look at exactly what you did, but just to clarify things, the XML code should be looping through sheet2 data, and creating the XML file from that data. That data was created from other code behind the 2nd button on Sheet1. I can't remember the name of that button right now, I just know its button 2.
Button 3 is the one that runs the XML code.
I'll get back to you on Monday.
Thanks,
gdunn59
Ok. I will try what you suggested, but I won't be able to try it until Monday. I'm leaving in the morning driving to Utah for my Aunts funeral.
Also, I don't really have time right now to look at exactly what you did, but just to clarify things, the XML code should be looping through sheet2 data, and creating the XML file from that data. That data was created from other code behind the 2nd button on Sheet1. I can't remember the name of that button right now, I just know its button 2.
Button 3 is the one that runs the XML code.
I'll get back to you on Monday.
Thanks,
gdunn59
The attachment contains the code I used which ran against sheet 2 - without the extra fettling JKP did but it does run - to produce a file which Excel can read if the space is removed from 'Date Submitted'.
But, as I said, that's not the error you are quoting in the original post so its not clear we are troubleshooting the original issue
But, as I said, that's not the error you are quoting in the original post so its not clear we are troubleshooting the original issue
My original post states that it creates the file but if I try to open it, I get the error.
I didn't specify that although it creates the file, the file size is 0KB.
I didn't specify that although it creates the file, the file size is 0KB.
The error we found after running your code was linked to invalid tag names - ie: tag names with a space in them
The error in the post at the top of this thread is:
XML document must have a top level element. Error processing resource 'file:///H:/NRTEcho/ACTIVE
.
It would seem that the code you supplied in the attachment works as expected (assuming the tags don't have spaces), which implies that its not exactly the same as the code you originally had a problem with or something in the data has changed.
Your original error would be explained if the file is blank and this would seem to mean that your code is getting past the file creation point and then not finding any rows to output, try commenting out the error handler steps and stepping though the loop that outputs the individual rows - I would guess that one of your checks, for example: invalid tag or empty row, is causing the output stage to be skipped.
The error in the post at the top of this thread is:
XML document must have a top level element. Error processing resource 'file:///H:/NRTEcho/ACTIVE
.
It would seem that the code you supplied in the attachment works as expected (assuming the tags don't have spaces), which implies that its not exactly the same as the code you originally had a problem with or something in the data has changed.
Your original error would be explained if the file is blank and this would seem to mean that your code is getting past the file creation point and then not finding any rows to output, try commenting out the error handler steps and stepping though the loop that outputs the individual rows - I would guess that one of your checks, for example: invalid tag or empty row, is causing the output stage to be skipped.
It is getting to this line of code, and then exiting.
If Trim(Cells(1, i + 1).Value) = "" Then Exit For
Not sure why?
Thanks,
gdunn59
If Trim(Cells(1, i + 1).Value) = "" Then Exit For
Not sure why?
Thanks,
gdunn59
All,
I finally got it to work. The problem was I needed to add Sheet2 to the code. Every place in the code where it shows .Cells, I added Sheet2 in front of it.
Here is the code I used that finally worked:
Here are the results of the XML file that was created:
<?xml version="1.0" encoding="utf-8" ?>
- <Sheet2>
- <Row>
- <InvIndex>
- <![CDATA[ 707785
]]>
</InvIndex>
- <InvElectHistID>
- <![CDATA[ 9B2932BE-B778-464C-9F48-C5
]]>
</InvElectHistID>
- <DateSubmitted>
- <![CDATA[ 1/20/2016
]]>
</DateSubmitted>
- <Comments>
- <![CDATA[ This is test1
]]>
</Comments>
</Row>
- <Row>
- <InvIndex>
- <![CDATA[ 707949
]]>
</InvIndex>
- <InvElectHistID>
- <![CDATA[ 4EDD4E14-122D-4456-BC4C-C8
]]>
</InvElectHistID>
- <DateSubmitted>
- <![CDATA[ 1/22/2016
]]>
</DateSubmitted>
- <Comments>
- <![CDATA[ This is test2
]]>
</Comments>
</Row>
- <Row>
- <InvIndex>
- <![CDATA[ 707975
]]>
</InvIndex>
- <InvElectHistID>
- <![CDATA[ 56396562-F974-40A8-9FDA-B2
]]>
</InvElectHistID>
- <DateSubmitted>
- <![CDATA[ 1/31/2016
]]>
</DateSubmitted>
- <Comments>
- <![CDATA[ This is test3
]]>
</Comments>
</Row>
- <Row>
- <InvIndex>
- <![CDATA[ 727820
]]>
</InvIndex>
- <InvElectHistID>
- <![CDATA[ 797978979878
]]>
</InvElectHistID>
- <DateSubmitted>
- <![CDATA[ 2/3/2016
]]>
</DateSubmitted>
- <Comments>
- <![CDATA[ This is test4
]]>
</Comments>
</Row>
</Sheet2>
I finally got it to work. The problem was I needed to add Sheet2 to the code. Every place in the code where it shows .Cells, I added Sheet2 in front of it.
Here is the code I used that finally worked:
Public Function ExportToXML(FullPath As String, RowName As String) As Boolean
On Error GoTo ErrorHandler
Dim colIndex As Integer
Dim rwIndex As Integer
Dim asCols() As String
Dim oWorkSheet As Worksheet
Dim sName As String
Dim lCols As Long, lRows As Long
Dim iFileNum As Integer
Set oWorkSheet = ThisWorkbook.Worksheets(2)
sName = oWorkSheet.Name
lCols = oWorkSheet.Columns.Count
lRows = oWorkSheet.Rows.Count
ReDim asCols(lCols) As String
iFileNum = FreeFile
Open FullPath For Output As #iFileNum
For i = 0 To lCols - 1
'Assumes no blank column names
If Trim(Sheet2.Cells(1, i + 1).Value) = "" Then Exit For
asCols(i) = Sheet2.Cells(1, i + 1).Value
Next i
If i = 0 Then GoTo ErrorHandler
lCols = i
Print #iFileNum, "<?xml version=""1.0"" encoding=""utf-8"" ?>"
Print #iFileNum, "<" & sName & ">"
For i = 2 To lRows
If Trim(Sheet2.Cells(i, 1).Value) = "" Then Exit For
Print #iFileNum, "<" & RowName & ">"
For j = 1 To lCols
If Trim(Sheet2.Cells(i, j).Value) <> "" Then
Print #iFileNum, " <" & asCols(j - 1) & "><![CDATA[";
Print #iFileNum, Trim(Sheet2.Cells(i, j).Value);
Print #iFileNum, "]]></" & asCols(j - 1) & ">"
DoEvents 'OPTIONAL
End If
Next j
Print #iFileNum, " </" & RowName & ">"
Next i
Print #iFileNum, "</" & sName & ">"
ExportToXML = True
ErrorHandler:
If iFileNum > 0 Then Close #iFileNum
Exit Function
End Function
Here are the results of the XML file that was created:
<?xml version="1.0" encoding="utf-8" ?>
- <Sheet2>
- <Row>
- <InvIndex>
- <![CDATA[ 707785
]]>
</InvIndex>
- <InvElectHistID>
- <![CDATA[ 9B2932BE-B778-464C-9F48-C5
]]>
</InvElectHistID>
- <DateSubmitted>
- <![CDATA[ 1/20/2016
]]>
</DateSubmitted>
- <Comments>
- <![CDATA[ This is test1
]]>
</Comments>
</Row>
- <Row>
- <InvIndex>
- <![CDATA[ 707949
]]>
</InvIndex>
- <InvElectHistID>
- <![CDATA[ 4EDD4E14-122D-4456-BC4C-C8
]]>
</InvElectHistID>
- <DateSubmitted>
- <![CDATA[ 1/22/2016
]]>
</DateSubmitted>
- <Comments>
- <![CDATA[ This is test2
]]>
</Comments>
</Row>
- <Row>
- <InvIndex>
- <![CDATA[ 707975
]]>
</InvIndex>
- <InvElectHistID>
- <![CDATA[ 56396562-F974-40A8-9FDA-B2
]]>
</InvElectHistID>
- <DateSubmitted>
- <![CDATA[ 1/31/2016
]]>
</DateSubmitted>
- <Comments>
- <![CDATA[ This is test3
]]>
</Comments>
</Row>
- <Row>
- <InvIndex>
- <![CDATA[ 727820
]]>
</InvIndex>
- <InvElectHistID>
- <![CDATA[ 797978979878
]]>
</InvElectHistID>
- <DateSubmitted>
- <![CDATA[ 2/3/2016
]]>
</DateSubmitted>
- <Comments>
- <![CDATA[ This is test4
]]>
</Comments>
</Row>
</Sheet2>
It jumping out of the loop because its found an empty cell - so when it was written the assumption was that there would be no empty cells in the data area.
if you check the value of i (by stepping through with F8) then you will be able to confirm which cell its pointing to at the time; to give you a hint - which 'sheet' do you think is referred to by the 'cells(1,i+1)'?
if you check the value of i (by stepping through with F8) then you will be able to confirm which cell its pointing to at the time; to give you a hint - which 'sheet' do you think is referred to by the 'cells(1,i+1)'?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial