Generate an XML file from a table via VBA

Stez84

New Member
Joined
Jan 16, 2024
Messages
8
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top