Save to specific folder (Excel 2002)

Topic

New Reply

Is there a way to force a document created from a template to save to a specific folder. This is a shared template, which automatically tries to save to a person’s own My Documents folder. How can I force the document to automatically navigate to a different folder so that everyone who creates this document will save it to the same folder. I’ve looked at some of the SaveAs methods, but can’t find anything that seems appropriate.

Reply | Quote

Replies

Use this line in your code change file and path as appropriate

activeworkBook.SaveAs Filename:=”d:pathfilename.xls”

Steve

Reply | Quote

You most likely can use the Workbook SaveAs event routine, depending on exactly what you want to do. Before being specific, you need to answer a couple of questions:

1- Do you want to suggest a folder but allow the user to change to another, or only allow the save to go to the particular folder?

2- Do you want to use a specific file name, or let the user choose?

Reply | Quote

Thanks for your reply.

I want the file to save to a specific folder, but I want the user to be able to determine the specific file name.

Reply | Quote

The following should work in Excel 2002

ChDir Path
Application.Dialogs(xlDialogSaveAs).Show

where Path his the folder you want to save to. The Save As Dialog opens and allows the user to enter a file Name.

Andrew C

Reply | Quote

Unfortunately, that is not going to prevent the user from changing to another directory in the SaveAs dialog.

Reply | Quote

The code below, placed in the Workbook Before Save event routine should do what you want. It saves into the directory C:Work which can be changed to whatever directory you want.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim vFileName As Variant
    If Not SaveAsUI Then Exit Sub
    Cancel = True
    ChDir ("C:Work")
    vFileName = Application.GetSaveAsFilename(Title:="SaveAs Filename")
    If vFileName = False Then Exit Sub
    vFileName = Right(vFileName, Len(vFileName) - InStrRev(vFileName, ""))
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs Filename:="C:Work" & vFileName
    Application.EnableEvents = True
End Sub

Reply | Quote