Macro to import multile xml into xlsm with Map data properties or schema

[PippaThePointer]

Hi,
I have some basic Macro knowledge learnt from this site manly. But not sure how to do the following.
I have a bunch of xml files all with the same format. I want to be able to use a standing xlsm template that has a header row and formating and then get the user to import all the availble xml in a folder but map only the relvent tags i require to the correct columns.

• Ideally i would like it to search all subfolders within the selected folders but i can work around this if not possible.
• I would like it to display the number of xml files picked up
• Possibly i may want to more the selected xml files to another folders (complete, for example)

I dont know too much about 'map data' but i created a test and could manually import xml with map data but it only brought in the first xml. Hopefully someone can whip me up a little macro example were it grabs all availble xml and maps to the active workfbook from row 2 to end.

Then after the use makes some manualy checks and changes i can write another macro that would export the data back out line by line as xml or csv to run in another system.
Appreciate any help.

 

[Kerryx]

Just use power query no macro needed, i assume that the structure of the files are the same, i.e. column and column headers are the same.

VBA to transpose blocks (arrays) of data

I have to transform literally hundreds of blocks of data exported from financial software into a different format for my manager. The source data is on the left in the image and the required output is on the right. Doing it manually is not that difficult but i will drive me to drink! Any help...

www.mrexcel.com

 

[PippaThePointer]

Just use power query no macro needed, i assume that the structure of the files are the same, i.e. column and column headers are the same.

VBA to transpose blocks (arrays) of data

I have to transform literally hundreds of blocks of data exported from financial software into a different format for my manager. The source data is on the left in the image and the required output is on the right. Doing it manually is not that difficult but i will drive me to drink! Any help...

www.mrexcel.com

Hi, Thanks for this info. It was realy helpful as i have never used or looked at power query. Its seems awesome. My reply is overdue becuase i have had few days trialing it and testing various things.
What im now kind of stuck on is which way i want to go. The power query does a great job and i liked all the action i could input but i know the end user will then want to manually adjust things, but a PQ refresh will undo all her changes as it pull is the referenced xml again. I guess i could 'disconect' the data or perhas export it. Im not sure how to make it very simple for her so that the data she gets can be resorted and lines removed etc.

The other option is i stick to using a macro to just import all the xml once, let her tidy it up and then macro to send the data back out as csv or xml.

 

[Kerryx]

If data is going to be written to another system you will probably be best to have a macro button in place to run the export in the format you need
In regards to running a PQ refresh obviously if you are selecting newer files the data in the original table will be overwritten or appended depends on what you want to do.
Even if she selects the same files and does a refresh all the changes she makes will be overwritten based on the way you have the PQ setup to load the data but really you'd hope all the steps would be done in PQ so there would be no changes required.

 

[PippaThePointer]

If data is going to be written to another system you will probably be best to have a macro button in place to run the export in the format you need
In regards to running a PQ refresh obviously if you are selecting newer files the data in the original table will be overwritten or appended depends on what you want to do.
Even if she selects the same files and does a refresh all the changes she makes will be overwritten based on the way you have the PQ setup to load the data but really you'd hope all the steps would be done in PQ so there would be no changes required.

View attachment 110012

Thanks.
Yes, i have written a macro and button to then export the data to where i want it to go which has instructions for file selection, renaming and impositions for printing. The reason the data is not 100% is that along with the perfectly supplied web2print xml information there is always some late manualy addins at the cutoff time that the person wants to manually add in. For example something that has been supplied via email and the spreadsheet user would be required to manually input some data in the row before performing the export macro.
I'll keep working on it as it does look great. I have created 2 vesions to trial. One with PQ and the other using 'import xml' with mapped cells and traditinoal formulas and macro.

 

[Kerryx]

Welcome to the world of PQ great for those of us who do not write macros and for those who do, the best tool in Excel i think.