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

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

Open in new window



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/GROPER/eBillStatusChanges....


What is the issue?

Thanks,

gdunn59
LVL 1
gdunn59Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
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
Jan Karel PieterseExcel and VBA ExpertCommented:
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.
gdunn59Author Commented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Download eBook now!
regmigrantCommented:
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
gdunn59Author Commented:
regmigrant,

I didn't get the error about 'invalid character in name'.

So the attachment you attached, is it working or not?

Thanks,

gdunn59
gdunn59Author Commented:
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
Jan Karel PieterseExcel and VBA ExpertCommented:
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:

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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdunn59Author Commented:
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
regmigrantCommented:
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
gdunn59Author Commented:
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.
regmigrantCommented:
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/GROPER/eBillStatusChanges....

.

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.
gdunn59Author Commented:
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
gdunn59Author Commented:
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:

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

Open in new window



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-C5D0592C9A43
  ]]>
  </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-C8BD8C7301B9
  ]]>
  </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-B2B3D5A51BB7
  ]]>
  </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>
regmigrantCommented:
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)'?
regmigrantCommented:
posts cross, well spotted :)
gdunn59Author Commented:
Thanks everyone for your time!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.