In order to achieve this yesterday we created a .XSD file that we manually attached to the Excel file to allow us to do the mapping and the extract. I've attached an example of the Box 1A.XLSX file and the Box 1A-converted.XML file for comparison. The issue we have with the XLSX file is it comes to us with some fields merged together (so we have to remove the merge from these fields) then remove the blank columns. Doing this manually is easy enough - open the file; highlight the worksheet; right-click and choose to remove the merge; using Find & Select to remove the blank columns is quite simple but I'm looking to automate the create of the XML file from the XLSX if I can.
So I'm looking for advice on how we automate portions of this. We have the ability to do the work on a file by file basis but I'm now looking to perform this work on all files in a directory (we can output the files to a different directory if it helps matters). The idea would be that the job would be run once a day and everything contained in the folder would be used - Box 1A files joined together and output to the Box 1A directory; Box 2A files joined together and output to the Box 2A directory and so on...every night the folder area would be cleared out and all files/sub-directories would be removed and a fresh batch of data would be added the next day.
Hope this makes sense...I know it looks a little complicated but hopefully my directory snapshots and the files (plus the original question) make things a little easier to follow. If this has to be broken down into multiple questions (Excel conversion and then the automation process) then no problems and I'm happy to do this as well.
Here is a copy of the FileCreate batch file we have that pulls the 2 files and outputs them to a 3rd file. We need to automate this to pick up each file in the directory, with the corresponding ""-converted.XML file (so Box 1A.XML would be matched to Box 1A-converted.XML and so on) and we also need to create a directory based upon the name of the original XML file (the one without -converted at the end):