Loop Through Subfolders of a Folder using Excel VBA

Рет қаралды 14,029

8 жыл бұрын

This video tutorial will walk you through a macro to loop through the subfolders of a folder searching for a specific file.
Each part of the Excel VBA code is explained in the video, and you can get it from this link - goo.gl/Mf8cPq
This macro uses File System Object, also known as FSO, to access and run routines on the files and folders of a directory. FSO makes working with files and folders in Excel VBA easy.
This macro searches for a specific file. When found the file is opened and a range is copied from it into the current file. This part of the macro can easily be changed to perform a different task.
If you seriously want to master Excel VBA, check out our online course - goo.gl/oTPQZj
Find more great free tutorials at;
www.computergaga.com
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

Пікірлер: 14

@ShampooWow 8 жыл бұрын

*_Awesome video! I like it_*

@MsWatchdog 8 жыл бұрын

I was wondering if you could help me? I wanted to learn BigHand Digital Dicatation.. and also how to polish up my CV wonder if you could show something like this. Many Thanks.. your awesome

@Computergaga 8 жыл бұрын

Thanks for your comments Naz. Unfortunately I don't know that software.

@viper1424 6 жыл бұрын

How could I use this code if a file with the same name was in each folder and I needed to copy data from each file into the workbook

@katherinep4184 6 жыл бұрын

Hi There, really great code and very helpful. I have a question tho, is there a way to get this working when the file name has a wild card within it? thanks

@Computergaga 6 жыл бұрын

Thanks Katherine. I didn't think this would be an issue as the wildcard would not be treated as one. The filenames should be viewed as a string. If you were after a partial file name match. You could use a wildcard in the code when referring to the filename variable.

@katherinep4184 6 жыл бұрын

Hi There, many thanks for your speedy response, :) . yes have tried dim as variant for the file name (assuming I have understood you) and used the wild card in the file name. With the fso option (for looking within subfolders) it does not appear to match the file name when the wild card is used and thus will not open the file. within the code I am also using the dir to search the main folder for the file and this recognizes and opens the file when the wild card is used. I need to use the fso as the subfolder names will vary and change in number. Not sure if that explains the issue or if you are happy to help any further?

@Computergaga 6 жыл бұрын

Is there a way of knowing the number to refer to the file specifically, such as an incremental number pattern - File1, File2 etc, or are they written somewhere on a spreadsheet?

@katherinep4184 6 жыл бұрын

Hi there, the file name is a string of concatenated info, a Job number, action type, complexity, it also contains a date and this is the bit that's replaced with the wild card. The file name elements are within the spreadsheet where the code is being run from and the code brings the file name together. Essentially the code is creating a job instruction that gets saved to a specific location. The code firstly runs a check to see if an instruction for that job number has already been run and saved down, this then returns a message box to ask the user if they wish to over write the found file, else it proceeds to create and save down the new job instruction. Not sure if that answers your question?

@Computergaga 6 жыл бұрын

Ok, if the information is in the file including the date. You should be able to loop through the list looking for that workbook and if found do what you need it to, and if not save this one.