Save As Prompt in Excel 3

[cghoga]

I have searched the forum, but I cannot find an example of prompting without a filename hardcoded in the VBA.

I tried recording the macro and copying the code, but it made me save to a location before it would allow me to stop the recorder.

How would I prompt to Save As and allow the user to save to the location of their liking?

I feel silly for asking, but this has got me stumped.

Thanks!

 

[SkipVought]

Hi,

Take a look at the GetOpenFileName method

Skip,

_[red][/red]

 

[cghoga]

Thanks Skip! Will do.

 

[cghoga]

Sorry Skip,

I tried it and it worked great, but I need it to open the 'Save As' window.

I did some searching and found the GetSaveFileName, but it looks intimidating.

If you can help I would greatly appreciate it.

Thanks!

 

[SkipVought]

And the REASON you need to open the SaveAs file window is...

Skip,

_[red][/red]

 

[IknowMe]

Set NewBook = Workbooks.Add
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
NewBook.SaveAs Filename:=fName

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[cghoga]

Thanks MrWilson,

This works great, but if I decide to Cancel the save, the Window keeps popping up because fname never gets set to True?

Also, how can I get xls in the Save as Type list and to default to xls. Right now if I click Save it saves without an xls extension.

May I please have your help?

 

[PHV]

What about this ?
Set NewBook = Workbooks.Add
fName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If fName <> False Then
NewBook.SaveAs Filename:=fName
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cghoga]

BRILLIANT!!!

Thank you both

 

[IknowMe]

Guess the VBA help example really wants to make sure the user saves ..... lol

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.