I'm trying to introduce some automation in my company. My first goal is to make accessible the data contained in our management system directly from excel. Unfortunately the only way I know to extrapolate the data from our database is via export. So I'm preparing this sort of automation:
1) Trigger a data export from our database during night. DONE
2) Export to excel output (the only possible format with our software) in a readable table set of data (the output generated by the software is awful) via a VBA script. DONE
3) Automatically trigger the subroutine in 2) every day. WORKING ON
4) Map the data with a schema file automatically with a VBA script. NOT DONE
5) Export the data as XML file automatically via a VBA script. NOT DONE
6) Use the XML file thus obtained as source data file in all the other company's spreadsheet where data is needed.
TO BE NOTED: I do want to create an XML file since 80% of the PC in the company works with Excel 2007, thus they haven't access to PowerQuery features. The only way I see to link external data source is through XML files.
I'm struggling in point 4. How can I bind the columns of my dataset to an xml schema thus to be able to export the dataset into an XML file? I don't think this is a difficult operation, but It's been a few days I'm trying to find a solution and still haven't found one. Any ideas?
You'll find attached an example of export output with related XML file.
xml file
excel sheet
1) Trigger a data export from our database during night. DONE
2) Export to excel output (the only possible format with our software) in a readable table set of data (the output generated by the software is awful) via a VBA script. DONE
3) Automatically trigger the subroutine in 2) every day. WORKING ON
4) Map the data with a schema file automatically with a VBA script. NOT DONE
5) Export the data as XML file automatically via a VBA script. NOT DONE
6) Use the XML file thus obtained as source data file in all the other company's spreadsheet where data is needed.
TO BE NOTED: I do want to create an XML file since 80% of the PC in the company works with Excel 2007, thus they haven't access to PowerQuery features. The only way I see to link external data source is through XML files.
I'm struggling in point 4. How can I bind the columns of my dataset to an xml schema thus to be able to export the dataset into an XML file? I don't think this is a difficult operation, but It's been a few days I'm trying to find a solution and still haven't found one. Any ideas?
You'll find attached an example of export output with related XML file.
xml file
excel sheet
