Variable Save Filename

  • Thread starter Thread starter Rick Ong
  • Start date Start date
R

Rick Ong

I had created a Invoice Template also with some form of
autonumbering in a cell for Invoice Number e.g J12 will
contain autogenerated value UBS1001.

Upon opening a new invoice using File > New, and working
on the invoice, how can I default the suggested filename
in the SaveAs window to be "UBS1001" when I save the file
instead of the default "Invoice1"
 
Is a macro ok?

Option Explicit
Sub testme()
Application.Dialogs(xlDialogSaveAs).Show _
Worksheets("sheet1").Range("j12").Value & ".xls"
End Sub

You could do tools|macro|macros... and run this (give it a nice memorable
name). Or you could even plop a button from the Forms toolbar on that worksheet
that has this macro assigned to it.
 
I am actually hoping that the actions be performed when I
select Save or SaveAs from the File menu.

Any clues?
 
Hi,

I had used these codes under the workbook code and it
seems to be working fine.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
Application.EnableEvents = False
Cancel = True
Application.Dialogs(xlDialogSaveAs).Show [J11] & [K11]
Application.EnableEvents = True
End Sub


Above codes works as expected when a new workbook was
opened from a template and saved for the first time with a
filename from Cell J11 and K11. But I hope to modify it
better so that subsequent click of Save button from the
toolbar or from file menu does not bring up the SaveAs
window again and just save the file with it's present
filename, just like in normal excel operation when saving
an existing file.

Thx
 
You can check to see if the workbook has been saved before by looking at the
name of the folder/path that would be holding the file. If it's "", then it's
never been saved.

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If Me.Path = "" Then
'never saved before
Application.EnableEvents = False
Cancel = True
With Worksheets("sheet1")
Application.Dialogs(xlDialogSaveAs).Show _
.Range("j11").Value & .Range("K11").Value
End With
Application.EnableEvents = True
End If
End Sub

Rick said:
Hi,

I had used these codes under the workbook code and it
seems to be working fine.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
Application.EnableEvents = False
Cancel = True
Application.Dialogs(xlDialogSaveAs).Show [J11] & [K11]
Application.EnableEvents = True
End Sub

Above codes works as expected when a new workbook was
opened from a template and saved for the first time with a
filename from Cell J11 and K11. But I hope to modify it
better so that subsequent click of Save button from the
toolbar or from file menu does not bring up the SaveAs
window again and just save the file with it's present
filename, just like in normal excel operation when saving
an existing file.

Thx
-----Original Message-----
Is a macro ok?

Option Explicit
Sub testme()
Application.Dialogs(xlDialogSaveAs).Show _
Worksheets("sheet1").Range("j12").Value & ".xls"
End Sub

You could do tools|macro|macros... and run this (give it a nice memorable
name). Or you could even plop a button from the Forms toolbar on that worksheet
that has this macro assigned to it.



--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...
 
Back
Top