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.

 

[Andrew Poulsom]

Like this?

Application.Dialogs(xlDialogSaveAs).Show "MyFileName.xls"

 

[GlennUK]

This will get the correct dialog showing:

    Application.Dialogs(xlDialogSaveAs).Show "mydoc.xls"

 

[JRS]

Your method did work so thanks, but I'm not able to use it how I had hoped, maybe you can help further with this query?...

I am trying to enter a variable as the filename for the "save as" dialogue box. With each run of the macro taking in a different variable value, the names that appear in the filename field of the "save as" dialogue box will be different each time.


So instead of using your code of:

Application.Dialogs(xlDialogSaveAs).Show "mydoc.xls"



I was hoping to go more along the lines of:

Application.Dialogs(xlDialogSaveAs).Show ("Report" & Variable1 & ".xls")


Any ideas how I can get the filename field to self populate using the format above? What I have written above doesnt work,

Cheers
JRS

 

[Andrew Poulsom]

Example:

Sub Test()
    Const Variable1 As String = "22032011"
    Application.Dialogs(xlDialogSaveAs).Show "Report" & Variable1 & ".xls"
End Sub

 

[Hamalot]

and the search function comes up trumps again

Thanks for the code guys, it may be picky, but is there a way to stop your chosen filename displaying in quotes in the dialogue box? I'm just worried about possible confusion when people wonder why it wont save when thay change the default "filename1.xls" to (missing quote)filename2.xls"

Thanks

 

[Andrew Poulsom]

I didn't get quotes around the Filename with my code.

 

[GlennUK]

I didn't get quotes around the Filename with my code.

... me neither.

 

[Hamalot]

my code is
Application.Dialogs(xlDialogSaveAs).Show TopLevel & ".xls"
where TopLevel is the content of a textbox. I'm using 2007.

i've also tried
Application.Dialogs(xlDialogSaveAs).Show "test.xls"
and the save as caption still comes out as "test.xls" inc quotes

 

[Hamalot]

i've just tried again and it seems that if the workbook is already saved it comes up without quotes, but if you run it on an unsaved workbook it comes out with quotes.

my code opens a new book and then does a save as.