Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder again.

Can anyone help?

Thanks
Dave

Try something along these lines, if the variable l does not equal 24 a
message box is displayed with the file count:

Sub CountEm()

Dim l As Long
Dim strDir As String

strDir = Dir("C:\*.*")

Do Until strDir = ""
l = l + 1
strDir = Dir

Loop

If l = 24 Then
Exit Sub
Else
MsgBox "You have " & l & " files in that directory."
End If

--
Kevin Backmann


"Dave" wrote:

Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder again.

Can anyone help?

Thanks
Dave

Option Explicit
Sub testme()
Dim FSO As Object
Dim FolderName As String

FolderName = "c:\yourfoldernamehere"

Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(FolderName) = False Then
MsgBox FolderName & " not found"
Else
if FSO.GetFolder(FolderName).Files.Count < 24 then
msgbox "Please check that folder!"
end if
End If
End Sub


Dave wrote:

Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder again.

Can anyone help?

Thanks
Dave

--

Dave Peterson

On Sun, 11 Nov 2007 16:39:00 -0800, Dave
wrote:

Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder again.

Can anyone help?

Thanks
Dave

You can use the FileSystemObject.

The code below sets a reference to Microsoft Scripting Runtime. You could use
late binding but if you set a reference, it's easier to get Help while writing
the routines.

As written, the count returned includes all files, including any that might be
Hidden or various System Files.

But you could also use the FSO to test the files to see if they should be
counted based on their attributes and type.


==================================
Option Explicit
Sub filecount()
'Requires setting reference to Microsoft Scripting Runtime
Dim fso As FileSystemObject
Dim fo As Folder
Dim fi As File
Dim i As Long

'change this to reflect your actual path
Const fldname As String = "C:\"

Set fso = New FileSystemObject
Set fo = fso.getfolder(fldname)

i = fo.Files.Count

If i < 24 Then
MsgBox ("Folder has " & i & " files. Should have 24")
End If


'Below was just used for debugging
'might be useful if you want to filter the count based on file type

'On Error GoTo errMgr 'some files gave an error looking at ShortName
'For Each fi In fo.Files
' i = i + 1
' Debug.Print fi.ShortName, fi.Name, , fi.Attributes, fi.Type
'Next fi
'Exit Sub

'errMgr:
' Debug.Print "**Error**" & vbLf & fi.Name, , fi.Attributes, fi.Type & vbLf
'Resume Next
End Sub
==========================================
--ron

If i wanted to build this into a current macro, what could I use instead of
Exit Sub?

Thanks!
Dave

"Kevin B" wrote:

Try something along these lines, if the variable l does not equal 24 a
message box is displayed with the file count:

Sub CountEm()

Dim l As Long
Dim strDir As String

strDir = Dir("C:\*.*")

Do Until strDir = ""
l = l + 1
strDir = Dir

Loop

If l = 24 Then
Exit Sub
Else
MsgBox "You have " & l & " files in that directory."
End If

--
Kevin Backmann


"Dave" wrote:

Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder again.

Can anyone help?

Thanks
Dave

Try something like

Function FileCount(FolderName As String) As Long
On Error Resume Next
FileCount = CreateObject("Scripting.FileSystemObject"). _
GetFolder(FolderName).Files.Count
End Function

If you want to count files in nested subfolders, use
Function FileCount2(FolderName As String) As Long
On Error Resume Next
Dim FSO As Object
Dim N As Long
Dim FF As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FF = FSO.getfolder(FolderName)
N = FSO.getfolder(FF).Files.Count
If FF.subfolders.Count 0 Then
For Each FF In FF.subfolders
N = N + FileCount2(FF.Path)
Next FF
End If
FileCount2 = N
End Function


You can then call this function with code like the following.

Dim N As Long
N = FileCount("C:\Test")
If N = 24 Then
Debug.Print "OK: " & CStr(N) & " files"
Else
Debug.Print "Not OK: " & CStr(N) & " files"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Dave" wrote in message
...
Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder
again.

Can anyone help?

Thanks
Dave

Hi Chip,

Thanks for this. It does work if I use a direct link e.g. C:\ but if I want
to use a link referred to as legacy1path it doesn't work. I get an error of:

Compile error:
ByRef Argument type mismatch.

Any tips.

Thanks!
Dave

"Chip Pearson" wrote:

Try something like

Function FileCount(FolderName As String) As Long
On Error Resume Next
FileCount = CreateObject("Scripting.FileSystemObject"). _
GetFolder(FolderName).Files.Count
End Function

If you want to count files in nested subfolders, use
Function FileCount2(FolderName As String) As Long
On Error Resume Next
Dim FSO As Object
Dim N As Long
Dim FF As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FF = FSO.getfolder(FolderName)
N = FSO.getfolder(FF).Files.Count
If FF.subfolders.Count 0 Then
For Each FF In FF.subfolders
N = N + FileCount2(FF.Path)
Next FF
End If
FileCount2 = N
End Function


You can then call this function with code like the following.

Dim N As Long
N = FileCount("C:\Test")
If N = 24 Then
Debug.Print "OK: " & CStr(N) & " files"
Else
Debug.Print "Not OK: " & CStr(N) & " files"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Dave" wrote in message
...
Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder
again.

Can anyone help?

Thanks
Dave

How did you declare legacy1path? Did you use "As String" or "As Variant" or did
you not declare that variable at all (and excel sees those undeclared variables
as Variants).

I'd declare it as a string or pass cstr(legacy1path) to the function.



Dave wrote:

Hi Chip,

Thanks for this. It does work if I use a direct link e.g. C:\ but if I want
to use a link referred to as legacy1path it doesn't work. I get an error of:

Compile error:
ByRef Argument type mismatch.

Any tips.

Thanks!
Dave

"Chip Pearson" wrote:

Try something like

Function FileCount(FolderName As String) As Long
On Error Resume Next
FileCount = CreateObject("Scripting.FileSystemObject"). _
GetFolder(FolderName).Files.Count
End Function

If you want to count files in nested subfolders, use
Function FileCount2(FolderName As String) As Long
On Error Resume Next
Dim FSO As Object
Dim N As Long
Dim FF As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FF = FSO.getfolder(FolderName)
N = FSO.getfolder(FF).Files.Count
If FF.subfolders.Count 0 Then
For Each FF In FF.subfolders
N = N + FileCount2(FF.Path)
Next FF
End If
FileCount2 = N
End Function


You can then call this function with code like the following.

Dim N As Long
N = FileCount("C:\Test")
If N = 24 Then
Debug.Print "OK: " & CStr(N) & " files"
Else
Debug.Print "Not OK: " & CStr(N) & " files"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Dave" wrote in message
...
Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder
again.

Can anyone help?

Thanks
Dave


--

Dave Peterson

legacy1path has been declared above like this:

Set Fobj = CreateObject("Scripting.FileSystemObject")
Set wbCodeBook = ThisWorkbook
Set legacy1 = Application.FileDialog(msoFileDialogFolderPicker)
legacy1.Show
legacy1path = CurDir()

"Dave Peterson" wrote:

How did you declare legacy1path? Did you use "As String" or "As Variant" or did
you not declare that variable at all (and excel sees those undeclared variables
as Variants).

I'd declare it as a string or pass cstr(legacy1path) to the function.



Dave wrote:

Hi Chip,

Thanks for this. It does work if I use a direct link e.g. C:\ but if I want
to use a link referred to as legacy1path it doesn't work. I get an error of:

Compile error:
ByRef Argument type mismatch.

Any tips.

Thanks!
Dave

"Chip Pearson" wrote:

Try something like

Function FileCount(FolderName As String) As Long
On Error Resume Next
FileCount = CreateObject("Scripting.FileSystemObject"). _
GetFolder(FolderName).Files.Count
End Function

If you want to count files in nested subfolders, use
Function FileCount2(FolderName As String) As Long
On Error Resume Next
Dim FSO As Object
Dim N As Long
Dim FF As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FF = FSO.getfolder(FolderName)
N = FSO.getfolder(FF).Files.Count
If FF.subfolders.Count 0 Then
For Each FF In FF.subfolders
N = N + FileCount2(FF.Path)
Next FF
End If
FileCount2 = N
End Function


You can then call this function with code like the following.

Dim N As Long
N = FileCount("C:\Test")
If N = 24 Then
Debug.Print "OK: " & CStr(N) & " files"
Else
Debug.Print "Not OK: " & CStr(N) & " files"
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Dave" wrote in message
...
Hi Everyone,

I am trying to do the following.

I have a folder e.g. C:\Folder1

I have this path defined as folderPath1.

I want to run a macro that will check that Folder1 has 24 files in it. If
not, a message box will display, prompting the user to check the folder
again.

Can anyone help?

Thanks
Dave


--

Dave Peterson