Is this possible?
 

Hi all,

Is it possible to programatically determine the number of workbooks in a
folder? Also, I need to be able to delete a workbook from the said folder
if it is more than 7 days old.

Any help is greatly appreciated.

Thanks and regards,

Del.

Is this possible?
 

Del,

Here is some clues

Dim FSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFolder As String
Dim i As Long

sFolder = "C:\myTest"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.getfolder(sFolder)
For Each oFile In oFolder.Files
If oFile.Type = "Microsoft Excel Worksheet" Then
cWBs = cWBs + 1
End If
Next oFile
MsgBox "Number of workbooks is " & cWBs

For Each oFile In oFolder.Files
If oFile.Type = "Microsoft Excel Worksheet" Then
If oFile.Type = "Microsoft Excel Worksheet" And _
oFile.datelastmodified < Date - 7 Then
Kill oFile.Path
End If
End If
Next oFile

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"del" wrote in message
...
Hi all,

Is it possible to programatically determine the number of workbooks in a
folder? Also, I need to be able to delete a workbook from the said folder
if it is more than 7 days old.

Any help is greatly appreciated.

Thanks and regards,

Del.

Is this possible?
 

Thanks Bob. I can adapt that to my needs I think..I appreciate your help.


"Bob Phillips" wrote in message
...
Del,

Here is some clues

Dim FSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFolder As String
Dim i As Long

sFolder = "C:\myTest"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.getfolder(sFolder)
For Each oFile In oFolder.Files
If oFile.Type = "Microsoft Excel Worksheet" Then
cWBs = cWBs + 1
End If
Next oFile
MsgBox "Number of workbooks is " & cWBs

For Each oFile In oFolder.Files
If oFile.Type = "Microsoft Excel Worksheet" Then
If oFile.Type = "Microsoft Excel Worksheet" And _
oFile.datelastmodified < Date - 7 Then
Kill oFile.Path
End If
End If
Next oFile

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"del" wrote in message
...
Hi all,

Is it possible to programatically determine the number of workbooks in a
folder? Also, I need to be able to delete a workbook from the said
folder
if it is more than 7 days old.

Any help is greatly appreciated.

Thanks and regards,

Del.

Is this possible?
 

Del,

Try
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Option Explicit
Sub TEST()

Dim SFS As Object
Dim FLD As Object
Dim FLE As Object
Dim i As Long
Dim N_1 As Integer
Dim N_2 As Integer
Dim N_3 As Integer
Dim str_D As String
Dim str_M As String

str_D = "C:\Temp"
Set SFS = CreateObject("Scripting.FileSystemObject")
Set FLD = SFS.GetFolder(str_D)

For Each FLE In FLD.Files
N_1 = N_1 + 1
If FLE.Type = "Microsoft Excel Worksheet" Then
N_2 = N_2 + 1
If FLE.DateLastModified < Date - 7 Then
FLE.Delete
N_3 = N_3 + 1
End If
End If
Next

str_M = "Total Files: " & N_1 - N_3 & vbNewLine
str_M = str_M & "Excel Files: " & N_2 - N_3 & vbNewLine
MsgBox str_M

End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--