I have a workbook template which when team members use it and the
they go to save it defaults to “my documents” and I would like to set
up a macro to save it to a particular drive / file location.

Below is one that works for a word template that I use, but I don’t
think it will work with an Excel file.

Excel 2003

Any Ideas ?

John


Credit to Graham Mayor
For these macros.

Sub FileSaveAs()
Dim sPath As String
sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path
for the document
On Error Resume Next
ChDir sPath 'Change to the directory you wish to save in
If Err.Number = 76 Then 'that folder doesn't exist

MsgBox sPath & " is not available on this PC?" & vbCr & "Select
the correct folder to save the document"
End If
With Dialogs(wdDialogFileSaveAs)

.Name = sPath & "\"
.Show 'show the save dialog
End With
End Sub


Sub FileSave()
Dim sPath As String
sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path
for the document
On Error Resume Next
If Len(ActiveDocument.Path) 0 Then 'the document has been previously
saved
ActiveDocument.Save 'so save the changes
Else 'The document has not been saved
ChDir sPath 'Change to the directory you wish to save in
If Err.Number = 76 Then 'that folder doesn't exist

MsgBox sPath & " is not available on this PC?" & vbCr & "Select
the correct folder to save the document"
End If
With Dialogs(wdDialogFileSaveAs)

.Name = sPath & "\"
.Show 'show the save dialog
End With
End If
End Sub

This could be a quick & dirty (and very clumsy) solution but have you
looked at something like this:

'Should go into ThisWorkbook object coed
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Me.SaveAs "the full name goes here" '<-- amend as necessary
Cancel = True
End Sub

As i said - it's an ugly one (+ its recoursive) but it should get you
started.
Getting the destination name file name would be no different from what
you do in Word.


On Sep 29, 7:26*pm, Johnnyboy5 wrote:
I have a workbook template which when team members *use it *and the
they go to save it defaults to “my documents” and I would like to set
up a macro to save it to a particular drive / file location.

Below is one that works for a word template that I use, *but I don’t
think it will work with an Excel file.

Excel *2003

Any Ideas ?

John

Credit to Graham Mayor *

For these macros.

Sub FileSaveAs()
Dim sPath As String
sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path
for the document
On Error Resume Next
ChDir sPath 'Change to the directory you wish to save in
If Err.Number = 76 Then 'that folder doesn't exist

* * MsgBox sPath & " is not available on this PC?" & vbCr & "Select
the correct folder to save the document"
End If
With Dialogs(wdDialogFileSaveAs)

* * .Name = sPath & "\"
* * .Show 'show the save dialog
End With
End Sub

Sub FileSave()
Dim sPath As String
sPath = "S:\Duty & Assessment\DAT ONLY" 'set the default save as path
for the document
On Error Resume Next
If Len(ActiveDocument.Path) 0 Then 'the document has been previously
saved
* *ActiveDocument.Save 'so save the changes
Else 'The document has not been saved
* *ChDir sPath 'Change to the directory you wish to save in
* *If Err.Number = 76 Then 'that folder doesn't exist

* * * *MsgBox sPath & " is not available on this PC?" & vbCr & "Select
the correct folder to save the document"
* *End If
* *With Dialogs(wdDialogFileSaveAs)

* * * *.Name = sPath & "\"
* * * *.Show 'show the save dialog
* *End With
End If
End Sub