Directory count occurrences

J

jujubeans69

New Member
Joined
Aug 17, 2012
Messages
43
I am currenlty using a "simple" macro to count the number of files in a particular folder. I was wondering if anyone can help me modify/adjust it so it only counts the number of files with today's date. Many thanks in advance!

My script:

Rich (BB code): 
Sub DTSx()

Dim srch1 As String, found1 As Integer, i1 As Integer
For i1 = 4 To 4
     srch1 = "\\root\inbound\" & "*.835"
     found1 = 0
     If Dir(srch1) <> "" Then 'is there such a file?
          Do
               found1 = found1 + 1
          Loop While Dir() <> "" 'are there more such files
     End If
     Sheets("DTS Xmissions").Range("C" & i1) = found1
Next i1

I was able to find this using google, but am not sure how to incorporate it, or even if it would work.

Rich (BB code): 
Dim csvFiles = From csv In Directory.EnumerateFiles("DirectoryPath", "*Test.csv", IO.SearchOption.AllDirectories)
               Where File.GetCreationTime(csv).Date = Date.Today
For Each csvPath In csvFiles
    Console.WriteLine(csvPath)
Next

If you are on .NET 2 as commented, you don't have Linq and <CODE>Directory.EnumerateFiles</CODE>, then use <CODE>GetFiles</CODE> and loops:

<CODE>
Rich (BB code): 
Dim csvFilesToday = New List(Of String)
For Each csv In Directory.GetFiles("DirectoryPath", "*Test.csv", IO.SearchOption.AllDirectories)
    If File.GetCreationTime(csv).Date = Date.Today Then
        csvFilesToday.Add(csv)
    End If
Next
</CODE>
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
I always use the scripting filesystem for this kind of thing (not that there is anything wrong with dir). So this would show the files modified today (unless you strictly mean brand new files created today - then you should use the DateCreated attribute.

Code: 
[COLOR="Navy"]Sub[/COLOR] foo()

[COLOR="Navy"]Dim[/COLOR] FSO [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]  [COLOR="SeaGreen"]'Scripting.FileSystemObject[/COLOR]
[COLOR="Navy"]Dim[/COLOR] f [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]    [COLOR="SeaGreen"]'Scripting.File[/COLOR]

[COLOR="Navy"]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] f [COLOR="Navy"]In[/COLOR] FSO.GetFolder("C:\myTemp").Files
    [COLOR="Navy"]If[/COLOR] f.DateLastModified >= [COLOR="Navy"]Date[/COLOR] [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] f.Name [COLOR="Navy"]Like[/COLOR] "*.835" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "              Name: " & f.Name
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "              Path: " & f.Path
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] "Last Modified Date: " & f.DateLastModified
            [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Next[/COLOR] f

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Your code you posted confuses me. Why is the loop variable "For i1 = 4 to 4"? That's not really any loop at all, and since the value of i1 doesn't change the write statement to the range will always be to the same cell, so the values will be overwritten and you only keep the last file found (Sheets("DTS Xmissions").Range("C" & i1) = found1)

The other code you found on the internet is .NET code will not run in VBA.
 
Upvote 0
xenou, thank you for this code.

The reason for my coding is because I track multiple files that process downstream to different folders, and sometimes change names. There is usually the \inbound folder, and then the \inbound\history folder. This ensures the file actually processed, by confirming the count (with the today's date) is the same in both folders. So the srch (in code below) is supposed to be counting how many files have today's date (and specified file mask), and pops that number into the necessary column. That is why I am trying to merge the two, if possible. Thank you. :)

Rich (BB code): 
Dim srch73 As String, found73 As Integer, i73 As Integer
For i73 = 40 To 40
    srch73 = "\\mb05a\ftproot\dts\medicaid_az\inbound\" & "*.TXT"
    found73 = 0
    If Dir(srch73) <> "" Then   'is there such a file?
        Do
             found73 = found73 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i73) = found73
Next i73

Dim srch74 As String, found74 As Integer, i74 As Integer
For i74 = 40 To 40
    srch74 = "\\mb05a\ftproot\dts\medicaid_az\inbound\history\" & "*.TXT"
    found74 = 0
    If Dir(srch74) <> "" Then   'is there such a file?
        Do
             found74 = found74 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i74) = found74
Next i74

Dim srch75 As String, found75 As Integer, i75 As Integer
For i75 = 40 To 40
    srch75 = "\\mb03a\mbxc01c\q\payor\azmd1\in\wf\" & "13???????.ARA"
    found75 = 0
    If Dir(srch75) <> "" Then   'is there such a file?
        Do
             found75 = found75 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i75) = found75
Next i75

Dim srch76 As String, found76 As Integer, i76 As Integer
For i76 = 40 To 40
    srch76 = "\\mb03a\mbxc01c\q\payor\azmd1\in\wf\history\" & "13???????.ARA"
    found76 = 0
    If Dir(srch76) <> "" Then   'is there such a file?
        Do
             found76 = found76 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i76) = found76
Next i76

Dim srch74 As String, found74 As Integer, i74 As Integer
For i74 = 42 To 42

This would be for the next file type, and place into row 42, and etc.
 
Last edited:
Upvote 0
You don't need any of these for-next loops. They aren't doing diddly-squat (technical term). You can also re-use variables so all these dims aren't really necessary. I think you are in over your head here, so it's hard to give advice -- if I were on site the first thing I do is delete all of this.
ξ
 
Upvote 0
I don't understand where your code places the count output. I thought the for-next loops were counting each instance of the Dir(srch)? :(
 
Upvote 0
Does anyone know which column or row the second posting puts the code in? Or how do I manipulate it so it puts it in any specific row and column? Thanks!
 
Upvote 0
Post your complete code. And say what you want to do/accomplish. As I said, your code is confusing and doesn't make sense. Did you write it yourself?
 
Upvote 0
The code is pretty much the same as the last one posted. It simply repeats for different folders and file types. No, I did not write the code myself. I believe I found something similiar on here, and tweaked it, as I understand it enough to know where it searches, and where it places the output counts.

So, the next set of codes would be the same as the last, only searching a different subfolder, and possible file type. What I want to do is track files that processed for TODAY. Files come into \inbound, and then are moved to \inbound\history when processed. This count is supposed to help me confirm that if 5 files were received today, then 5 files processed to \history, without me manually looking into each subfolder to verify, as the \history folder contains hundreds of previously processed files, and I only wish to COUNT the ones received/processed with TODAY's date. Thanks!

4 columns
C: mb05a\inbound
D: mb05a\inbound\history
E: mb03a\inbound
F: mb03a\inbound\history

Rich (BB code): 
Dim srch77 As String, found77 As Integer, i77 As Integer
For i77 = 42 To 42
    srch77 = "\\mb05a\ftproot\dts\medicaid_ga\inbound\" & "*.out.dat"
    found77 = 0
    If Dir(srch77) <> "" Then   'is there such a file?
        Do
             found77 = found77 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("C" & i77) = found77
Next i77
Dim srch78 As String, found78 As Integer, i78 As Integer
For i78 = 42 To 42
    srch78 = "\\mb05a\ftproot\dts\medicaid_ga\inbound\history\" & "*.out.dat"
    found78 = 0
    If Dir(srch78) <> "" Then   'is there such a file?
        Do
             found78 = found78 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("D" & i78) = found78
Next i78
Dim srch79 As String, found79 As Integer, i79 As Integer
For i79 = 42 To 42
    srch79 = "\\mb03a\mbxc01c\q\payor\gamd1\in\wf\" & "13*.ARA"
    found79 = 0
    If Dir(srch79) <> "" Then   'is there such a file?
        Do
             found79 = found79 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("E" & i79) = found79
Next i79
Dim srch80 As String, found80 As Integer, i80 As Integer
For i80 = 42 To 42
    srch80 = "\\mb03a\mbxc01c\q\payor\gamd1\in\wf\history\" & "13*.ARA"
    found80 = 0
    If Dir(srch80) <> "" Then   'is there such a file?
        Do
             found80 = found80 + 1
        Loop While Dir() <> ""  'are there more such files
    End If
    Sheets("DTS Xmissions").Range("F" & i80) = found80
Next i80
 
Upvote 0
The code you posted cannot be the whole code. It doesn't have beginning or an end. What scares me is your variables. When you Dim i78 does that mean there are 78 other files you are checking for?
 
Upvote 0
Dim i77 is the inbound file count (placed into col C). Dim i78 is the inbound\history count (placed into col D). Dim i79 is the same file, but the process changed the name, and it's on another server (col E). Dim i80 is the count when the file processed to \history (colum F). Tracking 1 set of files received today, processing through 4 different folders.

C D E F
mb05a\inbound mbo5a\inbound\history mb03a\inbound mb03a\inbound\history
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Expert help needed to restrict directory search to date
Replies
3
Views
713
FDibbins
FDibbins

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top