Macro to open "save as" box & populate filename

[JRS]

I was wondering if there is a way of getting a macro to open the "save as" dialogue box and then have it fill in the "file name" field.

I dont want it to actually make the save though as I want the user to have the chance to check that the save location is correct and the option to change the filename if required.

Cheers.

 

[Norie]

Why not use GetSaveAsFilename?

Dim varResponse
Dim strFileName As String
    strFileName = "Default.xls"
    varResponse = Application.GetSaveAsFilename(strFileName, fileFilter:="Excel Files (*.xls), *.xls)", Title:="Please check file name")
    
    strFileName = varResponse

 

[Hamalot]

Ok, i got it, its trying to save as an xlsx filetype but call it .xls. (i need them as xls so visio 200 can read it)

My next question was going to be how do you set the filetype but you've just answered that for me Norie. Thanks!

 

[Hamalot]

Got there in the end...
Application.Dialogs(xlDialogSaveAs).Show TopLevel, xlExcel8

Noob question: I see you can have lots of arguments after this but how do you find out what the argument options are? I found xlExcel8 by recording a save macro.

 

[EZRider]

I am trying to use this information to bring up the save as box with the filename defaulted from the value of a cell. When I run the macro the dialogue box comes up but the filename is blank. Any thoughts (using 2007)?

Dim ThisFile As String
ThisFile = Range("I1").Value
Application.Dialogs(xlDialogSaveAs).Show ThisFile & ".xls"

 

[Andrew Poulsom]

What's in I1 on the active sheet (or the sheet containing your code if it's in a worksheet module)?

 

[EZRider]

What's in I1 on the active sheet (or the sheet containing your code if it's in a worksheet module)?

This is the content of cell I1 on the active sheet.

AGREEMENT 5 - VLT PRODUCTION CHECKLIST

I should also mention that when I tried to run the code as originally provided on this thread it also would only bring up the save as dialogue box with a blank file name.

 

[Andrew Poulsom]

The code you posted worked for me when you tried it? Mybe you need to qualify the Range with its Worksheet, eg:

    Dim ThisFile As String
    ThisFile = Worksheets("Sheet1").Range("I1").Value
    Application.Dialogs(xlDialogSaveAs).Show ThisFile & ".xls"

 

[EZRider]

Thanks Andrew. While that didn't work (for me) I figured out my issue. I had to change ".xls" to ".xlsx".

Your quick responses are greatly appreciated! Keep up the great work!