Is this possible?

[del]

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.

 

[Bob Phillips]

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]

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

 

[Soo Cheon Jheong]

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
_ _
^¢¯^
--