Sleeper: Dialogs(xlDialogSaveAs)- strange behaviour when setting default directory

[brettdj]

On another forum I was asked how to set the default directory when using Application.Dialogs(xlDialogSaveAs).

Simple one, I thought, I opened a new file and tried

Dim Mystr As String
Mystr = "C:\PTL Files"
ChDrive "c:\"
ChDir Mystr
Application.Dialogs(xlDialogSaveAs).Show Mystr

and it worked fine. The OP wrote back and said it didn't work - I tried my code again and he was correct. After going through google I discovered that this code works fine for Excel 97 but not later versions. On my own accord I also confirmed that it worked fine on unsaved files

Several recent links suggest that it should work in later Excel versions, ie
Here
Here 2

Has anyone else come accross this?

Cheers

Dave

[Jacob Hilderbrand]

Works fine for me in Excel 2002.

[Zack Barresse]

I'd be curious as to where the OP actually placed the code, and ensured there was the opening and closing 'Sub/End Sub' lines. Sounds like it might be hand holding time. (we all need it sometimes, eh)

[Jacob Hilderbrand]

Works fine in 2003 as well. Also you shouldn't need to change the drive or directory if you are specifying an argument for the dialog.

Application.Dialogs(xlDialogSaveAs).Show "C:\PTL Files"

[Steiner]

And it wasn't simply a missing "\" at the end of Mystr?

[brettdj]

It doesn't work for the OP and it doesn't work for me on saved files in 2003, either at work or home ... any other takers ?

Works fine in 2003 as well. Also you shouldn't need to change the drive or directory if you are specifying an argument for the dialog.

Application.Dialogs(xlDialogSaveAs).Show "C:\PTL Files"

My initial code was just

Dim Mystr As String
Mystr = "C:\PTL Files"
Application.Dialogs(xlDialogSaveAs).Show Mystr

I added the directory codes after browsing through suggestions by other coders that if would be necessary to do this if the default path was set to another drive. So I did this in case it was the OP's problem.

Cheers

Dave

[Jacob Hilderbrand]

That is strange. Before I tried it, but the file was not saved. It works fine on a new workbook but once it is saved it will not work. I think it just automatically goes to the folder where the file is saved.

Why don't we try a different approach.

Dim FName As Variant
FName = Application.GetSaveAsFilename(InitialFileName:="C:\PTL Files")
If FName = False Then
'Cancel
Else
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlNormal
End If

[brettdj]

Thats the way to go to workaround the issue but I'm still curious about the dialog behaviour, the threads I posted gave the impression that people got it working.

[Jacob Hilderbrand]

I would be interested if someone got it to work. I checked the arguments for the dialog and there was no Path argument.

[mvidas]

I was not able to either in xl2000.. In another language while browsing google I came across someone who said to try:

Mystr = "C:\foldername"
With Application.Dialogs(xlDialogSaveAs)
 ChDir Mystr
 .Show Mystr
End With

I did notice that ChDrive Mystr works to change it to the C drive.
I also tried setting the DefaultFilePath as someone else had suggested and it didn't work either.
Consider me subscribed, this is odd

[Olivier]

Hello,

I have exactly the same problem!

Application.dialogs(xldialogsaveas).show ("C:\dir\file.xls") doesn't work!
The name of file suggested by the dialog box is fine but not the directory.

I also tried with ChDir and ChDrive but with no success.

If you have any clue...

Olivier./.

[Ken Puls]

That does seem pretty weird. Changing the drive via the immediate window affects the GetOpenFileName, but not the SaveAsFileName dialogs...

I'd probably work around the issue with something like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Mystr As String
    Dim var As Variant
Application.EnableEvents = False
    If SaveAsUI = True Then
        Mystr = "C:\test"
        var = Application.GetSaveAsFilename(Mystr)
        If Not var = False Then ActiveWorkbook.SaveAs var
        Cancel = True
    End If
    Application.EnableEvents = True
End Sub