+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    11-19-2021
    Location
    Uranus
    MS-Off Ver
    2016
    Posts
    3

    Question 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/
    Attached Files Attached Files
    Last edited by Imposter; 11-20-2021 at 02:30 PM. Reason: Solved myself

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    22,570

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

    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.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-19-2021
    Location
    Uranus
    MS-Off Ver
    2016
    Posts
    3

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

    Thanks for warm welcoming! Sadly, I don't feel like anyone's going to help me. It looks like my question is of extreme difficulty. Likely unsolvable at all.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    22,570

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

    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.

  5. #5
    Registered User
    Join Date
    11-19-2021
    Location
    Uranus
    MS-Off Ver
    2016
    Posts
    3

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

    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.
    Last edited by Imposter; 11-20-2021 at 05:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 14
    Last Post: 09-18-2016, 08:13 AM
  2. Replies: 0
    Last Post: 09-18-2015, 03:20 AM
  3. [SOLVED] Correct save path and syntax to save workbookdirectly into ONEDRIVE subfolder
    By osbertdebroilg in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-29-2015, 06:27 PM
  4. VBA code needed to move from Outlook 2010 subfolder to Symantec Vault subfolder
    By Marijke in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2014, 12:14 PM
  5. [SOLVED] Mkdir command button to add subfolder/subfolder
    By frefry4 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-17-2013, 06:28 PM
  6. Moving an e-mail from a subfolder in Mailbox to the equivalent subfolder in an archive PST
    By johncassell in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2012, 11:31 AM
  7. Files move to a subfolder
    By Jokacave in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-22-2010, 02:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1