Import XML files from each subfolder to a separate XLS for each subfolder

Hello,

I need to:
1) import thousands of XML files with unique filenames from multiple level one subfolders inside parent folder (i.e. Work\xmlDownload\0001\*.xml) to template XLSM file (i.e. Work\institutionInfo.xlsm with customized XML Map),
2) apply data transformation (using query refresh) using Power Query inside this XLSM file,
3) save file as a separate XLS file for each subfolder (i.e. Work\Folder-1.xls),
4) do this for every subfolder inside parent folder (i.e. Work\xmlDownload\0001, Work\xmlDownload\0002, Work\xmlDownload\0003, etc.)

I have managed to create VBA that does everything but since it's based of someone else's code I cannot simplify my result for my modest needs. Currently VBA asks to select next subfolder via folder select dialog and I don't need this:

Please Login or Register  to view this content.

I need to drop folder selection prompt from this VBA so it just loops through every subfolder of a parent folder (which is in same folder as my template file containing VBA) and does it's thing. However I struggle to do this correctly: after stripping folder selection the VBA cannot find any XML files. Here's my failed take on it:

Please Login or Register  to view this content.

I can do import of all XML of specified folder but cannot figure out how to inject this to subfolders correctly, i.e. this works as exceted:

Please Login or Register  to view this content.

I feel I'm missing something really obvious. Please help me out, I'm out of ideas. In case you need my project files for testing (template, folder structure with several XML files), they are in the attachment (institutionInfo.xlsm contains 2 simplified Macro modules, A and B, B is the one that works, A is the one that doesn't work and requires help).

Kind regards,

Imposter

P.S. Testing subfolders for contents works, it looks like relative path of XML files inside of subfolders is the problem.

Please Login or Register  to view this content.

Cross-posted at www .mrexcel. com/board/threads/1188127/

As a new member please take the time to review our rules. There aren't many, and they are all important.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have done it for you this time since it's your first post.

The reason I didn't help you myself is that you presented a very large amount of code, and I could not quickly grasp by reading your description specifically what your problem is. Your code is supposed to do a whole lot of stuff and I can't figure out what works the way you want it to, and what doesn't, other than you want to drop a prompt for folder. Also your code refers to data in a file that you did not provide. It would be very helpful to attach that file.

I think what you want to do is possible but finding someone who has the time to do this for free might be a little harder. Once we are talking about downloading an entire test bed from a third-party repository it starts to sound like a big investment.

I will take a second look.

Thank you, I may have also overcomplicated the description to provide as much code for other people as possible. I feel like this is a generic task sequence that many people can benefit from in the future.

Anyway, I figured it out using code from this Stack Overflow post to which I'm very grateful — stackoverflow. com/a/64279322. Code below does the following for parent folder xmlDownload that is located in the same directory as institutuionInfo.xlsm template file and which contains any number of level one subfolders with any number of *.xml files:
1) import .xml files from subfolder institionInfo.xlsm_template_location\xmlDownload\0001
2) refresh query sheet "Data"
3) save a copy whole file as institionInfo.xlsm_template_location\Folder_1.xls
4) loop for other subfolders, i.e. 0002, 0003.

Please Login or Register  to view this content.

I've also managed to get copies to save with filename of subfolder, i.e. Subfolder1Name.xlsm, Subfolder2Name.xlsm, etc., but that's a not related to this thread story (just in case, reference code to solve this can be found at www .mrexcel .com/board/threads/1115158/).

P.S. On a side note, I'm not doing this for money as well.