saving a file (with a difference)

[jaypatel]

Hi

have a file (spreadsheet) on our intranet, which is accessible to everyone within our organisation. The spreadsheet is a timesheet.

The problem i have is, i would like part of my script to say:
1, email the payroll dept (this is sorted)
2, but then do a file save as, within a particular directory. But, firstly to do a check if any files exist in the specified directory, if not, then do file save as timesheet1.xls, but essentially do a count of the number of files that exist in that directory, so the second file to be uploaded will be timesheet2.xls, and the 30th file would be timesheet30.xls. This is where i am struggling......(Shall we say that the new path is c:\payroll

I have worked out the Ch Dir macro part, but cannot work out if part 2 is possible.

many thanks in advance

Jay

 

[wigi]

You could use this code:

Sub saveas_Wigi()
    
    Const sDirectory As String = "C:\payroll\"
    
    ActiveWorkbook.SaveAs sDirectory & "timesheet" & Format(CountFiles(sDirectory, "timesheet*.xls") + 1, "00") & ".xls"

End Sub

Function CountFiles(Directory As String, sMask As String) As Long

    Dim lTemp As Long
    Dim MyName As String

    MyName = Dir(Directory & sMask, vbNormal)

    Do While MyName <> ""

        If MyName Like sMask Then lTemp = lTemp + 1
        MyName = Dir
        
    Loop

    CountFiles = lTemp

End Function