Macro to open "save as" box & populate filename

JRS

New Member
Joined
Mar 10, 2011
Messages
44
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
This will get the correct dialog showing:
Code:
    Application.Dialogs(xlDialogSaveAs).Show "mydoc.xls"
 
Upvote 0
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
 
Upvote 0
Example:

Code:
Sub Test()
    Const Variable1 As String = "22032011"
    Application.Dialogs(xlDialogSaveAs).Show "Report" & Variable1 & ".xls"
End Sub
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top