Prompting user for filename in Excel ...

[TheBionicOrange]

Hi.

I have the following code to prompt a user to save an Excel spreadsheet (when I have finished building it !) :

Do
        fname = Application.GetSaveAsFilename
    Loop Until fname <> ""

What I need is someone to tell me how I change the default path of the SaveAs box, as it is defaulting to the 'My Documents' folder, and I need 'S:MARGIN'.

Looking under the various properties for 'APPLICATION', but haven't found what I need yet !

Thanks.

[alex_read]

You could insert your own commondialog control & use the initial path property of that as a fallback plan...

[TheBionicOrange]

Yeah, thats not a bad idea. Cheers Alex.

[alex_read]

I just tried this myself using win2000 & Excel2000 :

Private Sub CommandButton1_Click()
    Application.GetSaveAsFilename ("S:\Margin")
End Sub

and this works. The first argument is the initial filename - so I tried it with S:\Margin\NameOfFile.xls, but playing around & not putting in a filename as above works for me !

[TheBionicOrange]

Thats great.

I'm using Win 98 but it works with that too.

The only downside is that it doesn't seem to like you specifying subdirectories ! It ignores them and defines the filename as the subdirectory name instead !?!?!

Thanks for your help.

[alex_read]

um, nope again
I can specify them okay -
1) do you finish it with a "\" symbol ?
2)( What happens if ou put in a filename after the subfolder
- i.e. "S:\margin\subfolder\buggerit.xls"

[TheBionicOrange]

No I wasn't putting a '\' on the end of the path name.

Now I am and its working fine.

Thanks again for helping me out.