Dialogs(xlDialogSaveAs) default location problem

Can someone explain to me why the code below does not open the save as dialog box in the C:\Windows directory?

Kind regards,
Antonio King
strFileLocation = "C:\Windows\"
        strFileName = Format(wsSummary.Range("Date"), "mmm yyyy") & " - Report.xls"
        Application.Dialogs(xlDialogSaveAs).Show strFileLocation & strFileName

Open in new window

antoniokingAsked:
Who is Participating?
 
Arno KosterConnect With a Mentor Commented:
you can use

ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename(InitialFileName:=strFileLocation & strFileName)

Open in new window


instead of

Application.Dialogs(xlDialogSaveAs).Show strFileLocation & strFileName

Open in new window

0
 
jppintoCommented:
The problem seem to be here:

strFileName = Format(wsSummary.Range("Date"), "mmm yyyy") & " - Report.xls"

If you try with something like this:

strFileName = "MAR 2011 - Report.xls"

The Save as dialog appears.

jppinto
0
 
antoniokingAuthor Commented:
Hmm... that wasn't my question but thanks for your input!

there is nothing wrong with strFileName.
The problem is the save as dialog keeps defaulting to the desktop and not to the folder specified in strFileLocation.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Rory ArchibaldCommented:
Works fine for me - are you sure you have a real date in the range Date?
0
 
antoniokingAuthor Commented:
There is nothing wrong with the file name.
The file is correctly named as I want it
That part of the code works perfect.

The problem is the save as dialog defaults to the desktop, and not C:\Windows\
as specified in strFileLocation.

Even if I change the code to the following... the save as dialog STILL defaults to the desktop folder...

strFileLocation = "C:\Windows\"
strFileName = "Report.xls"
Application.Dialogs(xlDialogSaveAs).Show strFileLocation & strFileName

Open in new window

0
 
Rory ArchibaldCommented:
Which version of Excel are you running this in? (I assume your Windows folder is actually located at "C:\Windows")
0
 
antoniokingAuthor Commented:
Yep Windows folder is located in C:\Windows
I've even tried creating folders on the C Drive and changing the strFileLocation to that location.... still no joy. It keeps defaulting to the desktop!
Using Excel 2003 on Windows 7 Pro

Regards
0
 
antoniokingAuthor Commented:
@akoster
The activeworkbook.saveas method does not present the user with a dialog box.
The save as dialog window must pop up to give the user the option of where to save the file.
0
 
Rory ArchibaldConnect With a Mentor Commented:
The GetSaveAsFilename function does the dialog in akoster's code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.