Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Macro to open "save as" box & populate filename

  1. #1
    New Member
    Join Date
    Mar 2011
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to open "save as" box & populate filename

    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.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    Like this?

    Code:
    Application.Dialogs(xlDialogSaveAs).Show "MyFileName.xls"
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    This will get the correct dialog showing:
    Code:
        Application.Dialogs(xlDialogSaveAs).Show "mydoc.xls"
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  4. #4
    New Member
    Join Date
    Mar 2011
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    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

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    Example:

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

  6. #6
    New Member
    Join Date
    Feb 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    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

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    I didn't get quotes around the Filename with my code.
    Microsoft MVP - Excel

  8. #8
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    11,044
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    Quote Originally Posted by Andrew Poulsom View Post
    I didn't get quotes around the Filename with my code.
    ... me neither.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  9. #9
    New Member
    Join Date
    Feb 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    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

  10. #10
    New Member
    Join Date
    Feb 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to open "save as" box & populate filename

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •