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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
Okay.

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


»bp
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Nick CollinsAuthor Commented:
That's fine with me
0
Bill PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Batch

From novice to tech pro — start learning today.