file save as macro

  • Thread starter Thread starter Joann Adams
  • Start date Start date
J

Joann Adams

I am trying to write macros to attached to a specific
files that will enable the automation of selecting the
correct folder the finished document will be saved in.

source document is in X folder, when user is ready to save
play macro to save in folder Y and prompt for file name
(to be entered by user)

any help would be appreciated
 
Perhaps use the beforeSave event

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
On Error goto ErrHandler
If Ucase(ThisWorkbook.FullName <> Ucase("C:\Myfolder\" & Thisworkbook.Name)
or _
SaveAsUI then
Cancel = True
Chdrive "C"
ChDir "C:\Myfolder"
fName = Application.GetOpenFileName(FileFilter:="Excel Files (*.xls),*.xls")
Application.enableEvents = False
Thisworkbook.SaveAs fName
End If
Errhandler:
Application.EnableEvents = True
End Sub

the above is untested, but should represent a general approach. May require
a bit more thought on all possibilities.
 
Back
Top