Sleeper: Looking for guidance on saving file

[GYMMIC]

Greeting my fellow techies. I am but a mere newbie to this world. i humbly approach you now, with this question after scowering the kb for about three hours today.

I have a file that generates an autonumber. Ie the excel sheet generates a new number everytime that this particular sheet is opened.

What i would like to do is have vba automatically append or contatenate (is this the right word? ) this number to the name of the file as it is being saved by the user.

Any help would be greatly appreciated. I know the answer is out there. Its just a matter of looking for it in the right place.

Thank you for your time.

[mdmackillop]

Hi
This code should append the number in a cell A1 to the active workbook name. Give it a try and let me know how you get on. The code should be pasted into the ThisWorkbook module.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyName As String, Ext As String, NewName As String
    Application.EnableEvents = False
    MyName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
    Ext = Range("A1")
    If Right(MyName, Len(Ext)) = Ext Then
        ThisWorkbook.Save
    Else
        NewName = ThisWorkbook.Path & "\" & MyName & Ext & ".xls"
        ThisWorkbook.SaveAs NewName
    End If
    Cancel = True
    Application.EnableEvents = True
End Sub

[Bob Phillips]

Not sure whether you are asking how to manage an incrementing number or how to save the file with this name, so I'll cover both.

I use a range name in VBA () to manage the number. I check in the workbook_open to see if it exists, if not then I seed it.

Const InvName As String = "__Invoice"
 
Private Sub Workbook_Open()
Dim nTemp
On Error Resume Next
nTemp = Evaluate(ThisWorkbook.Names(InvName).RefersTo)
On Error GoTo 0
If IsEmpty(nTemp) Then
ThisWorkbook.Names.Add Name:=InvName, RefersTo:=1
End If
End Sub

Then, when you want to save the file, you just pick up that name value.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim iPos As Long
Dim sfile As String
On Error GoTo wb_exit
Application.EnableEvents = False
sfile = ThisWorkbook.Name
iPos = InStr(sfile, "#")
If iPos > 0 Then
sfile = Left(sfile, iPos - 2)
Else
If Right(sfile, 3) = "xls" Then
sfile = Left(sfile, Len(sfile) - 4)
End If
End If
sfile = sfile & " #" & _
Format(Evaluate(ThisWorkbook.Names(InvName).RefersTo), "000")
sfile = IIf(ThisWorkbook.Path = "", sfile, ThisWorkbook.Path & "\" & sfile)
ThisWorkbook.SaveAs sfile
Cancel = True
wb_exit:
Application.EnableEvents = True
End Sub

This is workbook event code. To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code