Save as

  • Thread starter Thread starter dvya
  • Start date Start date
D

dvya

Ive created a template for my office staff to use to create invoices. Im
looking for a way that when they choose save -
1) the box that opens up will automatically be directed to a certain folder
2) and the file name will be the contents of "A1".

Thank You
 
Apply this event macro:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
fileSaveName =
Application.GetSaveAsFilename(InitialFileName:="Certain_path\" & Range("A1"))
End Sub

Replace Certain_path by a real path name!
Post if you need help to install it!

Regards,
Stefi

„dvya†ezt írta:
 
How would i install this code?

Thank you,

Simon Lloyd said:
Rather than direct them to a folder why not just have the workbook save
ther only?
Like this:

Code:
--------------------
ActiveWorkbook.SaveCopyAs "C:\Users\Simon\Desktop" & (Sheets("Sheet1").Range("A1").Value & ".xls")

--------------------





--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
Click to expand...
 
Open VBE (Alt+F11)
In the Project Explorer window right click on Thisworkbook under your
workbook name
Select View code from the local menu
Copy and Paste event macro code in the Thisworkbook (Code) window

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
fileSaveName = _
Application.GetSaveAsFilename(InitialFileName:="D:\work\" &
Range("A1") & ".xls")
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=fileSaveName
Application.EnableEvents = True
End Sub


I don't know your aspects, but maybe you could consider Simon's suggestion,
it doesn't allow the user change the save path at all.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
ActiveWorkbook.SaveCopyAs "D:\work\" & Range("A1") & ".xls"
Workbooks.Open Filename:="D:\work\" & Range("A1") & ".xls"
ThisWorkbook.Close savechanges:=False
End Sub

Regards,
Stefi

„dvya†ezt írta:
 
Back
Top