Checking the number of files in a folder using VBA

[Guest]

Hi all,

The following code saves the active spreadsheet into subfolders within
c:/inbox.

sPath = ActiveWorkbook.FullName
ActiveWorkbook.SaveAs "c:\Inbox\" & _
Range("C12") & "\" & ActiveWorkbook.Name

Kill sPath
ActiveWorkbook.Close

I want to write a piece of code that brings up a message when the specified
subfolder holds 15 files already, stating that this subfolder is 'fill up'
and to save the file in the next subfolder.

Is there any way that VBA can test this? I spoke to my lecturer and he said
there are sub-routines available to do this, but I have drawn a blank with my
research.


Any help would be much appreciated,

Thanks,

Bhupinder Rayat

 

[Guest]

You can get the count of files in a folder as follows:

Function xx()
Set fso = CreateObject("Scripting.FileSystemObject")
xx = fso.getfolder("c:\ajay").Files.Count
Set fso = Nothing
End Function

 

[Tom Ogilvy]

On easy way is to use the scripting runtime library.

Sub Tester1()
Dim fso As Object
Dim fldr As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("C:\BlueBook")
Debug.Print fldr.Files.Count
End Sub

The above used late binding. If you want to use early binding, you can
create a reference to the scripting runtime in Tools=>References in the VBE.

Then you can do:

Sub Tester2()
Dim fso As New FileSystemObject
Dim fldr As Folder
Set fldr = fso.GetFolder("C:\BlueBook")
Debug.Print fldr.Files.Count
End Sub

 

[Guest]

Thanks Guys, that works great.

How can I get the result of the count to be displayed in a cell on a
spreadsheet?

I can then use that cell ref (or name range) to generate a message to say
the specified sub folder is full up.

Thanks again,


Bhupinder Rayat

 

[Guest]

hi,
range("A1").value = fldr
or
Cells(1,1).vaue = fldr

 

[Guest]

Hi,

i was looking for Cells(1, 1).Value = fldr.Files.Count, but ur code got me
on the right track.


Thanks.