Excel export to XML

[krspa9]

Hi

Im having problems with dates when exporting an excel spreadsheet as an XML file. I have applied an XML map to the spreadsheet in which the users enter data. They then save the spreadsheet as an xml document. Everything exports fine apart from the dates which are appearing as the XLS serial date format (a number). If I format all date fields as text then the data exports fine. The prob Im having is that the users are copying these dates from another spreadsheet where the fields are formatted as dates. When they copy into my spreadsheet it is defaulting my text field back to a date field and thus the dates are saving again as the serial date format. Is there a way tp get the XML file to save whats in the field as when I save as a CSV that date appears fine even though it is formatted as a date field.

[Charles]

Welcome to the wonder that is Excel. I think that the only way to solve this is to use a different method of exporting the data. I would run a little JScript or VB script using the Windows Scripting Host. Or if you or your users are freaked out by the command line then I would write a little HTA that reads the Excel file and generates the XML.

[Ultimater]

Excel probably has some settings somewhere for this type of thing which can control the data formats of the export. I don't play around with Excel exports much. Here are some workarounds: (Note that I haven't tested any of these)


Perhaps:
Export it as a CSV initially so the export contains the right data (since you said exporting as a CSV doesn't corrupt the format of the cells).
Then import it and export it again but this time change the file type to XML.

Another method:
Export the orginal as an XML and a CSV.
Reopen both in seperate sheets, and pick and choose to merge the two as you see fit to a 3rd sheet and finally export as XML.

Or:
On the original, manually change the fields from a date format to a text format before exporting.

Or:
Edit the exported XML file manually and change the fields to your liking from notepad and resave.

Or:
Don't trust the user's input, and have them copy-paste to a different sheet where they can paste in date format.
Then have another row reference it but this time convert it to the text format.

Or:
Write a note on the excel page adjacent to the cells telling the user to make sure the cells are kept in the text format "@".

Or:
Write a new VBA module using the OnEntry event handler:
http://support.microsoft.com/kb/125810
http://www.mrexcel.com/articles/macr...ll-changes.php

Sub Auto_Open()
          Worksheets("Sheet1").OnEntry = "Test"
End Sub

Sub Test()
          MsgBox "A key was pressed."
End Sub

That way the subroutine "Test" would be called any time a field in the entire sheet changes.
Then instead of showing a dialog, for the subroutine, apply a function to make sure a cell range is the proper type:
Untested:

Sub Auto_Open()
          Worksheets("Sheet1").OnEntry = "EntryCallBack"
End Sub

Sub EntryCallBack()
	Worksheets("Sheet1").Range("B5:B12").Select
	Selection.NumberFormat = "@"
	'Sets the format to Text for the given range.
End Sub

A few links that might come in handy:
http://www.accessmonster.com/Uwe/For...xport-to-Excel
http://www.excelforum.com/excel-prog...rt-to-csv.html
http://jira.atlassian.com/browse/JRA-11360


You could also go with Charles' idea and setup a .WSH, .VBS, .JS or .HTA file to open the excel file
by creating an "Excel.Application" object.
http://msdn.microsoft.com/en-US/libr...=VS.80%29.aspx
Make some edits to the page programmatically then SaveAsXMLData().

[Charles]

Make some edits to the page programmatically then SaveAsXMLData().

Actually, my idea is to have the script pull the information from Excel, build the XML tree with the MSXML parser and then save. The idea is to get Excel out of the equation as much as possible.

Just one caveat. You can read an Excel file with ActiveX and with the Jet engine. The Active X method is way powerful, you can make SQL calls to your sheet, but with Excel it drops data in certain situations. For this use the ActiveX method.

excel = new ActiveXObject ('Excel.Application');
book = excel.workbooks.open (fileName);
sheet = book.worksheets (1);
headings = [];
for (i = 1; i <= sheet.usedRange.columns.count; i++) headings.push (sheet.cells (1, i));
data = array[];
for (j = 2; j <= sheet.usedRange.rows.count; j++) {
    a = [];
    for (i = 1; i <= sheet.usedRange.columns.count; i++) headings.push (sheet.cells (j, i));
    data.push (a);
}