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

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


"Bhupinder Rayat" wrote:

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

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

--
Regards,
Tom Ogilvy

"Bhupinder Rayat" wrote in
message ...
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

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Bhupinder Rayat" wrote in
message ...
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

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

-----Original Message-----
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Bhupinder Rayat"
wrote in
message news:A46A085C-7922-4C17-8490-
...
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




.

Hi,

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


Thanks.

" wrote:

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

-----Original Message-----
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Bhupinder Rayat"
wrote in
message news:A46A085C-7922-4C17-8490-
...
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




.