rama4672
02-12-2005, 09:32 AM
I have a function that looks at the file name of the workbook and increments it by 1.
This was working but now i get an error message, it says compile error sub or function not defined.
Here is the code it refers to
Function Increment_Filename() As String
' if the filename of the current activeworkbook contains a numeric
' suffix then the suffix is incremented by 1 and the full path and filename
' are returned.
' if not then the filename is returned with a numeric suffix of 1.
' if the file has not yet been saved then 'Not Saved' is returned.
' ASSUMPTION: that the workbook has a 3 character extension
Dim newPath As String: Dim baseFileName As String: Dim Extension As String: Dim i As Integer
newPath = ActiveWorkbook.Path & "\"
'return Not Saved if path is empty
If newPath = "\" Then Increment_Filename = "Not Saved": Exit Function
Extension = Right(ActiveWorkbook.Name, 4)
baseFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
For i = Len(baseFileName) To 1 Step -1
If Mid(baseFileName, i, 1) < "0" Or Mid(baseFileName, i, 1) > "9" Then
Exit For
End If
x = Mid(baseFileName, i, 1) & x 'build numeric suffix
Next i
Increment_Filename = newPath & Left(baseFileName, Len(baseFileName) - Len(x)) & (Val(x) + 1) & Extension
End Function
Private Sub SaveAs()
newfile = Increment_Filename
If newfile = "Not Saved" Then MsgBox "Save File First.": Exit Sub
save As newfile
End Sub
then i put a command button on with the following code:
Private Sub CommandButton18_Click()
SaveAs
End Sub
Thank you if anyone can shed some light on this
Regards
Ian
This was working but now i get an error message, it says compile error sub or function not defined.
Here is the code it refers to
Function Increment_Filename() As String
' if the filename of the current activeworkbook contains a numeric
' suffix then the suffix is incremented by 1 and the full path and filename
' are returned.
' if not then the filename is returned with a numeric suffix of 1.
' if the file has not yet been saved then 'Not Saved' is returned.
' ASSUMPTION: that the workbook has a 3 character extension
Dim newPath As String: Dim baseFileName As String: Dim Extension As String: Dim i As Integer
newPath = ActiveWorkbook.Path & "\"
'return Not Saved if path is empty
If newPath = "\" Then Increment_Filename = "Not Saved": Exit Function
Extension = Right(ActiveWorkbook.Name, 4)
baseFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
For i = Len(baseFileName) To 1 Step -1
If Mid(baseFileName, i, 1) < "0" Or Mid(baseFileName, i, 1) > "9" Then
Exit For
End If
x = Mid(baseFileName, i, 1) & x 'build numeric suffix
Next i
Increment_Filename = newPath & Left(baseFileName, Len(baseFileName) - Len(x)) & (Val(x) + 1) & Extension
End Function
Private Sub SaveAs()
newfile = Increment_Filename
If newfile = "Not Saved" Then MsgBox "Save File First.": Exit Sub
save As newfile
End Sub
then i put a command button on with the following code:
Private Sub CommandButton18_Click()
SaveAs
End Sub
Thank you if anyone can shed some light on this
Regards
Ian