Using GetSaveAsFileName in Excel 2007 VBA

[phudgens]

I am using the GetSaveAsFileName method in Excel 2007 VBA to allow the user to navigate to and specify a folder & file that will be used later in the program for output of text data. Following is my code:

With Application
    FileName = .GetSaveAsFilename(Filter, FilterIndex, Title)
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With

VBA invariably returns "Error 2015" as my FileName, which is dimmed as Variant. Filter and Title are dimmed as String, and FilterIndex as Integer. I need to allow the user to specify an existing file OR specify a brand new file for output. Are there specific items that need to be active in my Reference list? I see lots of stuff on the net about this issue, but no solutions. Thanks for any help.

 

[SkipVought]

hi,

The FIRST argument is the optional InitialFileName, which you have chosen to ignore. Therefore, you must explicitly define the arguments as follows...

With Application
    FileName = GetSaveAsFilename(FileFilter:=Filter, FilterIndex:=FilterIndex, Title:=Title)
'....
End with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[phudgens]

That worked - thanks very much.

 

[SkipVought]

Why are you changing the Drive and Directory in your code? I cannot remember the last time I used these archaic MS DOS commands.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[phudgens]

I have them so that the dialog opens in the D drive, rather than some other obscure place, to make navigation easier for the user.

Is there a way to make the GetSaveAsFileName dialog prompt for overwrite if the user selects an existing file? As it is, VBA just overwrites the file without asking. Thanks.

 

[SkipVought]

Well the GetSaveAsFileName opens in the DefaultFilePath folder, which is what you are assigning the dos functions above.

???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]