I need to convert excel file to XML file but keep getting an error "Cannot save or export XML data. The XML maps in this workbook are not exportable".
I have an 2010 excel spreadsheet with 250 columns off data that i need to convert to XML file but keep getting an error "Cannot save or export XML data. The XML maps in this workbook are not exportable". Cannot find out why I keep getting this error. Can someone help me. I will upload the excel file and the schema XML file.
Please can someone help me with this problem. Thank you
Schema.xml
Test.xlsx
Please can someone help me with this problem. Thank you
Schema.xml
Test.xlsx
Last Comment
Based on the previous questions you have asked the schema you trying to export to is not supported by excel.
Based on this the only way to get what you are after will be to a create a VBA script. To do this we would need a full copy of the excel spreadsheet (dummy data is fine) so we can see all the headers and how it will fit the schema supplied
An XML Map can't be exported if the mapped element’s relationship with other elements can't be preserved.https://support.office.com/en-us/article/Export-XML-data-0b21f51b-56d6-48f0-83d9-a89637cd4360
Based on this the only way to get what you are after will be to a create a VBA script. To do this we would need a full copy of the excel spreadsheet (dummy data is fine) so we can see all the headers and how it will fit the schema supplied
ASKER
Ok, I uploaded to Schema.XML file and the excel worksheet file. Hope you can help with this.
Thank you.
3584-1st.xlsx
nhpbj_2_00_0.xml
Thank you.
3584-1st.xlsx
nhpbj_2_00_0.xml
OK I have made a start on this. So far it writes out the XML with the header. Will work on this further when I have some more time
I do have a couple of questions
I do have a couple of questions
Is it one XML per worksheet and so header information can be simply read from the first data row
No termination date column? Are we missing any columns in the example?
3584-1st.xlsm
ASKER
No we will be adding 250 people's data to the 1 XML file and we do not need a termination date column as we can logon to the program and enter that when needed. We have to upload 1 XML file every month with all employee's times for that month.
Thank you.
Thank you.
I am doing some more work on this but need an example excel sheet so that I know what each row will look like. I do not need actual data but I do need to be able to see what exactly the format will be so could you please post a sheet with 20-30 rows with a couple of different employee numbers
@jodyreid I am still happy to finish off this but still need a more complete example to work from as the original spreadsheet does not contain enough data for me to see how I need to code this. Please post a sheet with 20-30 rows of data.
ASKER
Thank you. Sorry i did not get back to you sooner but was in Ireland for a few days. Here is a sample
3584.xlsx
3584.xlsx
ASKER
Just checking on this problem again. I still need to up load this data by way of .XML file. Can anyone help with this.
Thank you.
Thank you.
ASKER
I've requested that this question be deleted for the following reason:
Did not get an answer
Did not get an answer
@Jodyreid
Sorry got sent to KL for work. Will have some time thus weekend
Sorry got sent to KL for work. Will have some time thus weekend
ASKER
Thank you.
Hi @Jodyreid
Please find attached the completed workbook
If you look into the script (ALT+F11) you will see a module named constants which allows you to easily edit options such as the columns and attribute values.
To start with you will need to update the FILE_PATH to point to an appropriate location to save the completed XML as well as choosing the XML file name. At the moment it is set to "C:\TestXML.xml" which may cause an "Access is denied" error on your machine
3584-1st.xlsm
TestXML.xml
Please find attached the completed workbook
If you look into the script (ALT+F11) you will see a module named constants which allows you to easily edit options such as the columns and attribute values.
To start with you will need to update the FILE_PATH to point to an appropriate location to save the completed XML as well as choosing the XML file name. At the moment it is set to "C:\TestXML.xml" which may cause an "Access is denied" error on your machine
3584-1st.xlsm
TestXML.xml
ASKER
Thank you but it is Access is denied and I can not change any thing as it is read only.
Thank you.
Thank you.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you. I will see if that works. If it does not can i have you add some comments to this question as i want to make sure you get the points I have asked this before but can someone help with converting excel to XML
ASKER
Hi Michael74. It looks like it is almost there. It is working but the format for the dates keeps change it to mm/dd/yyyy and I need to keep them at yyyy-mm-dd. Is this some thing I can change to get this working. I also need to give you all the points for you help. Thank you.
ASKER
Thank you.
This was an excellent solution to a problem.
Looked all over the internet for the answer.
Thanks for posting!
fordraiders
Looked all over the internet for the answer.
Thanks for posting!
fordraiders
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
https://support.office.com/en-us/article/Export-XML-data-0b21f51b-56d6-48f0-83d9-a89637cd4360?CorrelationId=b45b7815-c3b9-4a19-b53e-1601e462d362&ui=en-US&rs=en-US&ad=US