Save as

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
 
S

Stefi

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:
 
S

Simon Lloyd

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")

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


dvya;248761 said:
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
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
D

dvya

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...
 
S

Simon Lloyd

You could use a command button or menu item, you could even us it lik
thi
Code
-------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean
ActiveWorkbook.SaveCopyAs "C:\Users\Simon\Desktop" & (Sheets("Sheet1").Range("A1").Value & ".xls"
End Su
-------------------
o
Code
-------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean
ActiveWorkbook.SaveCopyAs "C:\Users\Simon\Desktop" & (Sheets("Sheet1").Range("A1").Value & ".xls"
End Su
-------------------
you would probably want to use the latter

dvya;249803 said:
How would i install this code

Thank you




(Sheets("Sheet1").Range("A1").Value & ".xls"


Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1
(http://www.thecodecage.com/forumz/showthread.php?t=69432
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
S

Stefi

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:
 
Top