vba countif file name is like a string

Question

I have a folder that updated versions of a file are saved to every hour. the format is always the same "yyyymmdd_hhmmss.xlsx". What I would like to be able to do is have a macro that looks in the folder and returns the count of the files that are like "yyyymmdd_*"

I know I could loop through the folder and do count = count + 1 but I would like to avoid that if possible since this folder holds thousands of files.

Any help would be appreciated. Thanks!

I'm not exactly sure how that could be done without a loop. I suppose it's possible. What have you done so far? — Brian, Jun 02 '16 at 13:22
Read this post: http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba. I think this is what you want. — Brian, Jun 02 '16 at 13:24
@Brian I was hoping for a countif type formula but i will try with the loop using Dir in as that link suggests. It's just so slow now since there are many years of data in this folder with 20 files per day. — Meghan, Jun 02 '16 at 16:46
What about this instead: http://stackoverflow.com/questions/11004045/batch-file-counting-number-of-files-in-folder-and-storing-in-a-variable — Brian, Jun 02 '16 at 17:08
Also helpful - http://stackoverflow.com/questions/5548231/determine-files-of-a-specific-type-in-a-folder-without-using-loop — Brian, Jun 02 '16 at 17:12
You can use the command line: `DIR your_dir\20160101_*.xlsx` to count by date. Also you can pipe the result through `FINDSTR`: `DIR your_dir\20160101_*.xlsx | FINDSTR File` to give a single-line result. — xidgel, Jun 02 '16 at 19:41

Brian · Answer 1 · 2016-06-02T17:29:10.747

0

Try creating the .bat file in the folder you want counted. Put the following in the file (change accordingly)

@echo off
set cnt=0
for %%A in (*.xlsx) do set /a cnt+=1
echo File count = %cnt%
pause

I tried the above code with almost 10,000 files in a folder and it counts in less than 1 second. Hope it helps! :-)

edited Jun 02 '16 at 17:29

answered Jun 02 '16 at 17:10

Brian

2,078
1
15
28

score 0 · Answer 2 · answered Jun 02 '16 at 18:47

If you want to count by a specified date this VBA function should work:

Public Function CountFiles(datestr As String, pathstr As String) As Long
    ' datestr has format yyyymmdd (no trailing underscore)
    ' pathstr has format C:\folder1\folder2\...\folderN (no trailing backslash)
    Dim count As Long

    count = 0
    If Dir(pathstr + "\" + datestr + "_??????.xlsx", vbNormal) Like "########_######.xlsx" Then
        count = count + 1
        While Dir() Like "########_######.xlsx"
            count = count + 1
        Wend
    End If
    CountFiles = count
End Function

An example call is:

=CountFiles("20160101", "C:\temp")

To count ALL files (no date filtering), use ???????? for the datestr.

Note that this function does some testing for a correctly formatted date/time stamp; it will not count a file named "20160101_report.xlsx".

If you always count the same directory, you could hard code pathstr.

Hope this helps.

vba countif file name is like a string

2 Answers2