Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 _ _ ^ąŻ^ -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|