Checking Folders

Evening Experts,

I am trying to find a solution either using VBA in excel or another kind of process.

I want to be able to check folders in a directory to see if any files exist in them. If files are found in a folder, I would like to create a txt file with the name of the folder and the name of the files inside that folder. In a separate text a list of empty files.

If I was going down the excel approach, I would like the same as above but I would like to include an hyperlink of the files in the folder so that I can can open them direct.

Thanks for your help
Nick CollinsAsked:
Who is Participating?
 
Bill PrewCommented:
Ah, looks like I forgot to reference the sheets everywhere needed, please adjust the sorting line as below.

    ' Sort sheets by folder, file names
    shtFiles.Range("A3:B" & lngRowFiles).Sort Key1:=shtFiles.Range("A3"), Order1:=xlAscending, Key2:=shtFiles.Range("B3"), Order2:=xlAscending, Header:=xlYes
    shtEmpty.Range("A3:A" & lngRowEmpty).Sort Key1:=shtEmpty.Range("A3"), Order1:=xlAscending, Header:=xlYes

Open in new window


»bp
0
 
Bill PrewCommented:
Just one level down below the base folder, or chase subfolders as well?

If a subfolder has no files in it, but does have subfolders, is that considered empty?

Not too hard to do in Excel VBA producing an Excel file, or standalone BAT or VBS both producing text files (likely CSV if you wanted).  What's your preference?


»bp
0
 
Nick CollinsAuthor Commented:
Just one level under the base folder.

I would like it in excel if possible, is the hyperlink possible?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Bill PrewCommented:
Okay.

If you just want to open the folder in Windows File Explorer then yes, that is doable.


»bp
0
 
Nick CollinsAuthor Commented:
That's fine with me
0
 
Bill PrewCommented:
Can you mock up a sample of the Excel file you would like created?  I suspect the format I might think is useful could be different than what you are thinking.  Maybe you want a tab for each category, empty versus have files?  Etc...


»bp
0
 
Bill PrewCommented:
Okay, here's a basic approach, with a sheet for each type (FILES versus EMPTY).  Change the folder on the first sheet and click the button see what you think.

EE29097464.xlsm


»bp
0
 
Nick CollinsAuthor Commented:
Thanks you very much..

Can you add a sort to the folder names - A to Z

Is it possible to change the script, so it can be multi times so that it updates the new changes to the spreadsheet..

Otherwise it's perfect...
0
 
Bill PrewCommented:
Is it possible to change the script, so it can be multi times so that it updates the new changes to the spreadsheet..

I don't think I fully understand this, can you expand?


»bp
0
 
Nick CollinsAuthor Commented:
I want to be able to press 'Run' more than once so that it updates the spreadsheet with any new changes.
0
 
Bill PrewCommented:
No easy way to just "update for changes", the approach would be to delete or clear the old sheets (FILES and EMPTY) and then repopulate them from current data.  Is that something you can handle, or need help with that small change?


»bp
0
 
Nick CollinsAuthor Commented:
I would some help please
0
 
Bill PrewCommented:
Okay, this adds reuse of existing FILES and EMPTY sheets if they exist, as well as sorting by folder and file names.

EE29097464.xlsm


»bp
0
 
Nick CollinsAuthor Commented:
I have done some testing on the changes you made..

I am getting Run-Time Error 1004

The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By inbox isn't the same or blank.

Have any ideas?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.