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-201
1640.xml. The 2 files for the BOMNotes will be BOMNotes.xml and BOMNotesAA0697-2014-201504
. 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!D2 ='RAW BOM'!A2
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.