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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
This will get the correct dialog showing:
Code:
    Application.Dialogs(xlDialogSaveAs).Show "mydoc.xls"
 

JRS

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Example:

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

Hamalot

New Member
Joined
Feb 5, 2010
Messages
12
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Hamalot

New Member
Joined
Feb 5, 2010
Messages
12
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

New Member
Joined
Feb 5, 2010
Messages
12
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,605
Messages
5,765,385
Members
425,281
Latest member
tmoreira001

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
Top