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 (http://groups.google.com.au/groups?hl=en&lr=&threadm=3EA893CC.B22D7DAA%40msn.com&rnum=2&prev=/groups%3Fhl%3Den%26lr%3D%26q%3DxlDialogSaveAs%2Bdefault%2Bdirectory%26btnG% 3DSearch)
Here 2 (http://groups.google.com.au/groups?hl=en&lr=&threadm=ianripping.1894vm%40excelforum-nospam.com&rnum=5&prev=/groups%3Fq%3DxlDialogSaveAs%2520default%2520directory%2520%26hl%3Den%26lr%3 D%26sa%3DN%26tab%3Dwg)

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


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


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. :think: I checked the arguments for the dialog and there was no Path argument. :dunno

[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