Prompting user to provide file name and path 2

[honeypot3]

Hi there

I know this is really simple but I seem to be struggling with the syntax to get this to work. As part of a macro in Excel I want the macro to open the save as box and allow the user to change path and filename before the macro continues. I have used this before for file open set as a variable and it works fine but cannot get it to work for save using the same idea.

This one works great: myfileopen = Application.GetOpenFilename(“Text Files,*.txt”) - set as a variable at the beginning of the macro.

This variable however does not - myfilesave = ActiveWorkbook.SaveAsFilename("Excel files,*.xls)

What am I doing wrong?

Thanks Sandra

 

[PHV]

Have a look at the Application.GetSaveAsFilename method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[honeypot3]

It sort of does - I can see how I can use it as below:

Sheets("Sheet4").Copy
myfile = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=myfile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
MsgBox ("Now choose File, Send To, Email as attachment to open Outlook and email")
Workbooks("pivotscores.xls").Close SaveChanges:=False

The only trouble is when I run the macro and provide a name for the file it puts a full stop after the name and the file is not identifiable as an XLS file automatically. Any ideas on how I can get this method to save it as an XLS file automatically?

 

[shetlandbob]

you can do this a number of ways, the best is to preset the extension when you call the save dialog

Dim filter As String
    Sheets("Sheet4").Copy
    filter = "Excel Files (*.xls),*.xls,"
    myfile = Application.GetSaveAsFilename("mMyInitialName", Filter)
        ActiveWorkbook.SaveAs Filename:=myfile, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

Note: You can also give the dialog an assumed file name as a first guess

Robert Cumming

http://www.consultantengineer.co.uk

 

[honeypot3]

Thanks Robert, works like a dream!