Error When Opening XML File Created From Excel Spreadsheet Data

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

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

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

regmigrant,

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

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

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

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.

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

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

Select allOpen 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>

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)'?

posts cross, well spotted :)

Thanks everyone for your time!