Save As Dialog

I am trying to put some code in the BeforeSave event so that when the user
tries to save the workbook they will be prompted with the Save As dialog
with a different file name so they do not save over the original file.

I am using the SafeFileAs function and the Save As dialog does appear but
the initial directory is set to My Documents and I would like it to be set
to something else. Below is the code that I am using. Can anyone tell me how
I can accomplish this?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim DialogResult As String
Dim UserFileName As String
Dim sAppPath As String

Application.EnableEvents = False
sAppPath = ActiveWorkbook.Path & "\Bone Match 5.0 Template
Directory\Bone Match 5.0
History\BoneMatch.xls"

DialogResult = Application.GetSaveAsFilename(InitialFileName:=sAppPath,
FileFilter:="Microsoft
Office Excel Workbook (*.xls), *.xls")

If DialogResult = "False" Then
Application.EnableEvents = True
Cancel = True
Exit Sub
End If

UserFileName = CStr(DialogResult)
Workbook.SaveAs (UserFileName)

Application.EnableEvents = True

End Sub

Larry,

just use a combination of ChDrive and ChDir as follows:

Sub SaveInDir()
ChDrive "c:"
ChDir "c:\Temp"
strFile = Application.Dialogs(xlDialogSaveAs).Show
End Sub

Robin Hammond
www.enhanceddatasystems.com

"Larry Dodd" <[email protected]> wrote in message
news:[email protected]...
>I am trying to put some code in the BeforeSave event so that when the user
>tries to save the workbook they will be prompted with the Save As dialog
>with a different file name so they do not save over the original file.
>
> I am using the SafeFileAs function and the Save As dialog does appear but
> the initial directory is set to My Documents and I would like it to be set
> to something else. Below is the code that I am using. Can anyone tell me
> how I can accomplish this?
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> Dim DialogResult As String
> Dim UserFileName As String
> Dim sAppPath As String
>
> Application.EnableEvents = False
> sAppPath = ActiveWorkbook.Path & "\Bone Match 5.0 Template
> Directory\Bone Match 5.0
> History\BoneMatch.xls"
>
> DialogResult = Application.GetSaveAsFilename(InitialFileName:=sAppPath,
> FileFilter:="Microsoft
> Office Excel Workbook (*.xls), *.xls")
>
> If DialogResult = "False" Then
> Application.EnableEvents = True
> Cancel = True
> Exit Sub
> End If
>
> UserFileName = CStr(DialogResult)
> Workbook.SaveAs (UserFileName)
>
> Application.EnableEvents = True
>
> End Sub
>

Thank you for responding but that did not work for me. It does open the
Save As dialog but it is in the folder that the original file was opened
from. This is the code that I used. Please let me know if there is
something else that I need to do.

Also when I call the SaveAs function the file is saved to the location
that I selected but then Excel gives me an error message and closes. If
you know anything about that please let me know

Application.EnableEvents = False
ChDir (ActiveWorkbook.Path)
ChDir ("Bone Match Template Directory")
ChDir ("Bone Match History")
strFile = Application.Dialogs(xlDialogSaveAs).Show
Application.EnableEvents = True



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Larry

Can you not explicitly pass the path to the GetSaveAsFilename method. I used
the code below and it offered the save as dialog with my root 'C' as the
'preset' path

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFileName As String
If SaveAsUI Then Exit Sub
With Application
.EnableEvents = False
sFileName = .GetSaveAsFilename("C:\" & Me.Name)
Me.SaveAs Filename:=sFileName
.EnableEvents = True
End With
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS


"Larry Dodd" <[email protected]> wrote in message
news:[email protected]...
> Thank you for responding but that did not work for me. It does open the
> Save As dialog but it is in the folder that the original file was opened
> from. This is the code that I used. Please let me know if there is
> something else that I need to do.
>
> Also when I call the SaveAs function the file is saved to the location
> that I selected but then Excel gives me an error message and closes. If
> you know anything about that please let me know
>
> Application.EnableEvents = False
> ChDir (ActiveWorkbook.Path)
> ChDir ("Bone Match Template Directory")
> ChDir ("Bone Match History")
> strFile = Application.Dialogs(xlDialogSaveAs).Show
> Application.EnableEvents = True
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim DialogResult As String
Dim UserFileName As String
Dim sAppPath As String
Dim sFile as String

On Error goto ErrHandler
Application.EnableEvents = False
Cancel = True

sAppPath = ActiveWorkbook.Path & _
"\Bone Match 5.0 Template Directory\" & _
"Bone Match 5.0 History\"

sFile = "BoneMatch.xls"

chDrive sAppPath
chdir sAppPath

DialogResult = Application.GetSaveAsFilename( _
InitialFileName:=sAppPath & sFile, _
FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls")

If DialogResult = "False" Then
Application.EnableEvents = True
Exit Sub
End If
If lcase(ThisWorkbook.FullName) = lcase(DialogResult) Then
msgbox "Must change name. Save Cancelled"
Application.EnableEvents = True
Exit Sub
End if
Workbook.SaveAs DialogResult

ErrHandler:
Application.EnableEvents = True

End Sub

This assumes the directory you selected exists. In any event, you always
want to set Cancel = True

--
Regards,
Tom Ogilvy


"Larry Dodd" <[email protected]> wrote in message
news:[email protected]...
> I am trying to put some code in the BeforeSave event so that when the user
> tries to save the workbook they will be prompted with the Save As dialog
> with a different file name so they do not save over the original file.
>
> I am using the SafeFileAs function and the Save As dialog does appear but
> the initial directory is set to My Documents and I would like it to be set
> to something else. Below is the code that I am using. Can anyone tell me
how
> I can accomplish this?
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> Dim DialogResult As String
> Dim UserFileName As String
> Dim sAppPath As String
>
> Application.EnableEvents = False
> sAppPath = ActiveWorkbook.Path & "\Bone Match 5.0 Template
> Directory\Bone Match 5.0
> History\BoneMatch.xls"
>
> DialogResult =
Application.GetSaveAsFilename(InitialFileName:=sAppPath,
> FileFilter:="Microsoft
> Office Excel Workbook (*.xls), *.xls")
>
> If DialogResult = "False" Then
> Application.EnableEvents = True
> Cancel = True
> Exit Sub
> End If
>
> UserFileName = CStr(DialogResult)
> Workbook.SaveAs (UserFileName)
>
> Application.EnableEvents = True
>
> End Sub
>
>

Hi Nick,

That is interesting. It actually works better than that.

I tried C:\MyTest, and it went to C: as you said.

I then tried C:\Mytest\Mytest, and it went to c:\MyTest !

But best of all, I then tried C:\Mytest\Mytest\ and it went to
c:\MyTest\Mytest.

A good find.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nick Hodge" <[email protected]> wrote in message
news:[email protected]...
> Larry
>
> Can you not explicitly pass the path to the GetSaveAsFilename method. I
used
> the code below and it offered the save as dialog with my root 'C' as the
> 'preset' path
>
> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> Boolean)
> Dim sFileName As String
> If SaveAsUI Then Exit Sub
> With Application
> .EnableEvents = False
> sFileName = .GetSaveAsFilename("C:\" & Me.Name)
> Me.SaveAs Filename:=sFileName
> .EnableEvents = True
> End With
> End Sub
>
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> [email protected]HIS
>
>
> "Larry Dodd" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you for responding but that did not work for me. It does open the
> > Save As dialog but it is in the folder that the original file was opened
> > from. This is the code that I used. Please let me know if there is
> > something else that I need to do.
> >
> > Also when I call the SaveAs function the file is saved to the location
> > that I selected but then Excel gives me an error message and closes. If
> > you know anything about that please let me know
> >
> > Application.EnableEvents = False
> > ChDir (ActiveWorkbook.Path)
> > ChDir ("Bone Match Template Directory")
> > ChDir ("Bone Match History")
> > strFile = Application.Dialogs(xlDialogSaveAs).Show
> > Application.EnableEvents = True
> >
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
>
>

Just to add - at least in xl97 -
If the path is not valid, it does not raise an error. It goes somewhere
else.

--
Regards,
Tom Ogilvy


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Hi Nick,
>
> That is interesting. It actually works better than that.
>
> I tried C:\MyTest, and it went to C: as you said.
>
> I then tried C:\Mytest\Mytest, and it went to c:\MyTest !
>
> But best of all, I then tried C:\Mytest\Mytest\ and it went to
> c:\MyTest\Mytest.
>
> A good find.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Nick Hodge" <[email protected]> wrote in message
> news:[email protected]...
> > Larry
> >
> > Can you not explicitly pass the path to the GetSaveAsFilename method. I
> used
> > the code below and it offered the save as dialog with my root 'C' as the
> > 'preset' path
> >
> > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
> > Boolean)
> > Dim sFileName As String
> > If SaveAsUI Then Exit Sub
> > With Application
> > .EnableEvents = False
> > sFileName = .GetSaveAsFilename("C:\" & Me.Name)
> > Me.SaveAs Filename:=sFileName
> > .EnableEvents = True
> > End With
> > End Sub
> >
> >
> > --
> > HTH
> > Nick Hodge
> > Microsoft MVP - Excel
> > Southampton, England
> > [email protected]HIS
> >
> >
> > "Larry Dodd" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thank you for responding but that did not work for me. It does open
the
> > > Save As dialog but it is in the folder that the original file was
opened
> > > from. This is the code that I used. Please let me know if there is
> > > something else that I need to do.
> > >
> > > Also when I call the SaveAs function the file is saved to the location
> > > that I selected but then Excel gives me an error message and closes.
If
> > > you know anything about that please let me know
> > >
> > > Application.EnableEvents = False
> > > ChDir (ActiveWorkbook.Path)
> > > ChDir ("Bone Match Template Directory")
> > > ChDir ("Bone Match History")
> > > strFile = Application.Dialogs(xlDialogSaveAs).Show
> > > Application.EnableEvents = True
> > >
> > >
> > >
> > > *** Sent via Developersdex http://www.developersdex.com ***
> > > Don't just participate in USENET...get rewarded for it!
> >
> >
>
>