Add filename and some values from the files in 1 overview

hello i have many .xlsx files with many information now i want a list with some information of all these files

----------------------------------
for example:

Colum A - all the .xlsx File names
Colum B - the amount of records from Tab(1) (calculate from Colum D)
Colum C - the amount of records from Tab(2) (calculate from Colum D)

---------------------------------

i got this from a friend it works but its not scanning subfolders and only file names

Please Login or Register  to view this content.

is there a solotion to add subfolders to the search and adding the other options a added up here

can someone help me maybe ?

Screenshot_4.png

Put the code below in the sheet object that you want the results written to, then run it. It will ask you to select a file in the root directory - you can replace this with setting the root directory manually as you have in your code; it will then run through the root directory and all sub-directories, extract the path name, file name, file size, and count the number of non-blank cells in column D of the first and second worksheets.

Hope that helps.

Please Login or Register  to view this content.

I suspect you have put the code in a module or the workbook object. It needs to go in the sheet object that you're writing the results to. Or you can explicitly reference the results sheet if you want to.

I'm not getting that issue ... perhaps you had a file open in a separate instance of Excel? Does it happen when the workbook with the code is the only Excel file open?

Nope. I'm not getting that behaviour and all windows are closing correctly.

You could do something to check for open instances of Excel in other windows but to be honest it's a bit of a pain. Activewindow isn't advisable because if the issue goes away (or isn't there as in my case) then you risk closing the instance with your code (without saving) ... I'm sorry I can't replicate the issue ...