incrimental save

[jonp]

I have written a workbook to automate the prosses of creating a work sheet for billing engineers time and costs. once the macros have done their job i am left with a pdf of the work sheet and a archive saveas xls the xls save name is the windows user name + report number, can i interigate the the active dir and get the next report number automaticaly? i.e if i have paynej01,paynej02 in my folder i want the next file save name to be paynej03. I already get the windows user name automaticaly i just need the next number !
thanks

[rory]

Try this:

Function GetMaxNumber(strPath As String, strFileStart As String, strFileExt As String) As Long
Dim strFile As String, strFullPath As String
Dim lngCounter As Long, lngTemp As Long
Dim lngFileReplace As Long, lngExt As Long
strFullPath = strPath & Application.PathSeparator & strFileStart
lngFileReplace = Len(strFileStart)
strFile = Dir(strFullPath & "*" & strFileExt)
Do Until strFile = ""
strFile = Replace$(strFile, strFileExt, "")
lngTemp = CLng(Mid$(strFile, lngFileReplace + 1))
If lngTemp > lngCounter Then lngCounter = lngTemp
strFile = Dir
Loop
GetMaxNumber = lngCounter
End Function


called like this:

NextNum = getmaxnumber("C:\Test", "Book", ".xls") + 1

where the first argument is the path to the files, the second is the start of the filename (the username in your case) and the third is the file extension.

[jonp]

thanks very much, that is great!