Save As Dialog

[Robin Hammond]

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]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Tom Ogilvy]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[Larry Dodd]

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