none
loop through individual files, within multiple folders RRS feed

  • Question

  • Good evening all.

    I have a directory, which has 2 main directories, and each of those 2 directories have 8, and 16 sub-directories. And each of those 24 folders have their own folders, with an individual excel file, and 4 different file types in them.

    Eg,

    L:\DFT1\HerStores\Folders_A-P\Files_A-P.xls

    L:\DFT2\HisStores\Folders_Q-X\Files_Q-X.xls

    Where each folder and file would be ...\A\A.xls through ...\X\X.xls

    I'm seeing where I can loop through groups of files within individual folders, and can do it with multiple sub-directories, but can I do it looking through multiple directories as I've described above, with an individual file within the final directory?


    TYIA.
    • Edited by SteveDB1 Thursday, December 15, 2016 1:23 AM Append
    Thursday, December 15, 2016 1:17 AM

Answers

All replies

  • Instead of looping through folders, you can try to get all files using
    string[] files =  System.IO.Directory.GetFiles(@"D:\Temp", "*.xls*", SearchOption.AllDirectories);


    It all Happenz Sendil


    • Edited by sendilg Thursday, December 15, 2016 4:55 AM
    Thursday, December 15, 2016 4:54 AM
  • I really like the C# one liner. In vba, if I understand your question, a recursive function would do this also.

    In your case, as Dir(strSearchPath ,vbDirectory) returns a folder, you then append whatever folder name it came up with to strSearchPath  and call the same function to cycle through the contents of that folder.

    When either Dir(strSearchPath ,vbDirectory) or Dir() returns an empty string, then follow with Dir(strSearchPath & "*.xls*") to get the list of files and do whatever with them.


    -MainSleuth

    Thursday, December 15, 2016 5:18 AM
  • Instead of looping through folders, you can try to get all files using
    string[] files =  System.IO.Directory.GetFiles(@"D:\Temp", "*.xls*", SearchOption.AllDirectories);


    It all Happenz Sendil


    Hi Sendilg,

    Everything I'm finding on Google, wrt the syntax you're using is all VB, or C#.

    I am not able to locate anything which I would be able to use this in Excel VBA.

    Do you have a VBA version of this?

    Thursday, December 15, 2016 5:31 AM
  • I really like the C# one liner. In vba, if I understand your question, a recursive function would do this also.

    In your case, as Dir(strSearchPath ,vbDirectory) returns a folder, you then append whatever folder name it came up with to strSearchPath  and call the same function to cycle through the contents of that folder.

    When either Dir(strSearchPath ,vbDirectory) or Dir() returns an empty string, then follow with Dir(strSearchPath & "*.xls*") to get the list of files and do whatever with them.


    -MainSleuth

    Hi.

    I wish the C# code worked in Excel. I'd use it in a heartbeat. It looks about as simple and elegant as it gets. I did try--- not realizing the difference.

    Thursday, December 15, 2016 5:48 AM
    • Marked as answer by SteveDB1 Thursday, December 15, 2016 10:54 PM
    Thursday, December 15, 2016 8:09 PM
  • Just use File Ststem Object:

    http://www.EXCELGAARD.dk/Lib/FileSystemObject/Files/Iterate/Recursive/

    Had to make a couple of minor modifications, but it works great. Thank you.

    Please let the author know he's got a couple of typos in his description.

    I.e.,

    In order to run this macro, you have to call this macro with something like this: 

    Call FSO_GetFilesInfoRecursive ( FolderPath , True ) 

    FSO_Files_Iterate_Recursive

    Thursday, December 15, 2016 10:57 PM
  • A recursive VBA script will do this for you.  There are many ways to set this up.  One of my favorites is available here.

    http://www.learnexcelmacro.com/wp/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/

    Once you have listed your folder structure, you can do all kinds of things, like iterate through each value, open the file, make changes, save, and move to the next one.  That's just one single example.


    MY BOOK

    • Marked as answer by SteveDB1 Thursday, January 19, 2017 6:59 PM
    Monday, January 16, 2017 5:23 PM