Excel macro to write xml files

I need to get an Excel spreadsheet with Bill of Material's fields into xml format repeatedly.  One BOM will create 2 xml files.  The BOM has 3 fields; Quantity, Reference Designator or Location, and Component Part, in that order.  A Parent Part becomes a fourth field for every record.  The Parent Part appears separately as either text in an email or the name of the BOM file like AA0697-2014.xls(x).  The xml files are ProductStructure.xml and BOMNotes.xml.  The xml files contain a subset of the BOM fields along with several fields that are either NULL or filled with default data, all are to be copied down for each record except for the Component Part, Quantity, and Reference Designator or Location.  The ProductStructure.xml file uses the Parent Part, Component Part, and Quantity.  The BOMNotes.xml file uses the Parent Part, Component Part, and Reference Designator or Location.

I have started to automate the process with and Excel spreadsheet but do not have the VBA chops to automate the whole process.  It is attached along with a sample BOM.  The BOM could be the 20 lines as is the sample or hundreds.  Currently in the spreadsheet's Welcome sheet I ask for the Parent Part ("AA" number) and the number of records.  There are instructions there to enable repetition.  I also have directory locations, dates and filenames so they can be used in the creation of fields and filenames.  Along with the 2 xml files I need to also write 2 archive versions of teh xml files with the Parent Part and Date appended to the filename.  The 2 files for the ProductStructure will be ProductStructure.xml and ProductStructureAA0697-2014-201504171640.xml.  The 2 files for the BOMNotes will be BOMNotes.xml and BOMNotesAA0697-2014-201504171640.xml.  Both using today's date string.

The process will take the Parent Part and number of records in the Welcome sheet and the copy/pasted BOM in the RAW BOM sheet and automatically fill the ProductStructure and BOMNotes sheets with all the dynamic data and copy down all the NULL and default data for each record.  The ProductStructure and BOMNotes sheets get reviewed and if all looks well the "Write..." button(s) on the Welcome sheet get pressed and the 4 xml files get written.  If there are leftover ProductStructure.xml and BOMNotes.xml files from previous iterations the user will be warned and told to delete the old files then press the "Write.." button(s) again.  The archive files with the Parent Part and date will remain as archives.

The date format for the archive file save is yyyymmddhhmm
The date format in the ProductStructure and BOMNotes sheets is m/d/yyyy
The date format in the ProductStructure and BOMNotes xml files is mm-dd-yyyy

RAW BOM!D2 = Welcome!F$6
ProductStructure!A2 = 'RAW BOM'!D2
ProductStructure!B2 = TRIM('RAW BOM'!C2)
ProductStructure!C2 =NOW()
ProductStructure!D2 ='RAW BOM'!A2
ProductStructure!J2 =NOW()
BOMNotes!E2 = 'RAW BOM'!B2
BOMNotes!J2 = 'RAW BOM'!D2
BOMNotes!K2 = TRIM('RAW BOM'!C2)
BOMNotes!L2 = NOW()

Currently I have to click the copy down on every field with default data.  And when I click the buttons to write the files both xml files have ProductStructure Data so the BOMNotes sheet is not associated with the correct xml schema for BOMNotes.

I've attached the blank macro, and in-process macro, the xml schema, the current exported xml files (not archives) and a recent archive of each and a new BOM spreadsheet.  The macro would sit without any part or record info, ready for the next process.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Pravin AsarPrincipal Systems EngineerCommented:
Thanks for detailed explanation. Are you just looking for way to fomat  Date to String ? or complete solution development.

Excel has plenty of buit-in functions to convert or extract date part


Excel/VBA has VBA functions to copy and paste.


Also look at


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
gibneytAuthor Commented:
Pravin Asar,

At this point I am looking to have the data automatically, after a button press i guess, taken from the RAW BOM sheet and populated into the ProductStructure and BOMNOtes sheets.  Currently I have to copy down every column manually.

The other issue is the xml write.  After creating two macros to write the xml to two separate files, the two separate files are created but the same xml data is written to each.  I am not sure how to get the BOMNOtes xml to be written to the BOMNotes.xml file.

I'll look over the links you sent me thanks.

Pravin AsarPrincipal Systems EngineerCommented:
The links cover the example of code for the use cases you are talking about.

Good luck.
gibneytAuthor Commented:
Pravin AsarPrincipal Systems EngineerCommented:
Thanks for the points.
Hope the links helped.
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.