Count the number of excel files in a folder with vba....

[mohit999]

Hi,

I receive +450 excel files everyday. All excel files have unique and fixed names. The problem is....I have to check manually if I got all files and no file is missing and it takes me a hell lot of time to check all of them one by one.

An Example :- Suppose there are 100 files of the the names f1, f2, f3, f4, f5, f6.....................f97, f98, f99, f100. If four files are missing f6, f12, f55, f87.....then is it possible to write a program in vba that can check if these four files are missing and output the name of missing files in a separate worksheet (say sheet5) or display a message box with the name of missing files.

Note :- All files are in the same folder.

[kalbasiatka]

Hi!
Show a few real file names

[shg]

Maybe (untested):

Please Login or Register  to view this content.

[AlphaFrog]

This prompts the user for the folder to check, the base file name, and the max sequential file number. The missing excel file list is placed on Sheet5 column A

Please Login or Register  to view this content.

[mohit999]

Hi alphafrog. Your code is working exactly what I want. This code solves my problem when I know the files name in advance.

I have one more question. Sometimes I also receive excel files whose names I don't know in advance but file names are written in separate worksheet from where I compare them to the name of files given in folder.

For example in sheet1 :-

Cell(A1) = z
Cell(A2) = z1
Cell(A3) = z2
cell(A4) = s2
cell(A5) = s122
cell(A6) = p6
......and so on

Now I simply want that the vba checks every cell and see if the names(given in the cell) match to the names of the excel files given in a particular folder, if files are missing in the folder than it lists missing files in sheet7. If this is possible than it will be a great help.

[AlphaFrog]

Now I simply want that the vba checks every cell and see if the names(given in the cell) match to the names of the excel files given in a particular folder, if files are missing in the folder than it lists missing files in sheet7. If this is possible than it will be a great help.

You're welcome.

Please Login or Register  to view this content.

[mohit999]

you are a genius frog.I can't thank you enough. Problem and thread is solved.