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

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

Code:

---

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

Works fine for me in Excel 2002.

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)

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.

Code:

---

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

---

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

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 ? :banghead:

Quote:

---

Originally Posted by DRJ

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.

Code:

---

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

---

---

My initial code was just

Code:

---

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

That is strange. Before I tried it, but the file was not saved. :doh: 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. :think:

Code:

---

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

---

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.

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

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

Code:

---

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

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./.

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:

Code:

---

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

---