Excel File Save As

[cptitle]

many users in our office have a different default directory and we are finding files all over the place, I would like to prevent this.

I have a macro in Word that will open the the SaveAs dialog box for the user in the correct directory for the specific document they are working on, is there a way to do this for excel? I would like to tie it to a read-only or template so when users enter info they must do a FileSaveAs,
The Macro I have written for word is below:

Sub SaveInPolicyLetters()
'
'
With Dialogs(wdDialogFileSaveAs)
.Name = "h:\worddata\policyletters\"
.Show

End With


End Sub

 

[dan1967]

Here is one that I wrote for myself so I don't have to browse back to the original directory for a file I am working on. I think it is, or close to, what you are needing:

Sub FileSave_As()
Dim fDrive, fDir, fName
Dim Dest
On Error Resume Next
Dest = ActiveWorkbook.CustomDocumentProperties("Destination")
fDir = ActiveWorkbook.Path
fName = ActiveWorkbook.Name
If IsEmpty(Dest) = True Then
    If fDir = "" Then
    Application.Dialogs(xlDialogSaveAs).Show
    Else
    Application.Dialogs(xlDialogSaveAs).Show (fDir & "\" & fName)
    End If
Else
    Application.Dialogs(xlDialogSaveAs).Show (Dest & fName)
End If
End Sub

The variable "Dest" refers to the option in Properties for a workbook. For certain templates I created, I enter the path they belong in in the Destination Property of the file. Also, if the file being saved is a new file (i.e. Book1), then the Save As dialog opens to the last used directory, other wise, it uses the parent directory of the file being saved.

Dan.