Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Unanswered: Enter new name in save as

    Sub Save_And_Print()
    '
    ' Save_And_Print Macro
    ' Prints Proposal then saves it to Completed proposals Folder
    '
    I have some code written in a macro for a button that saves and prints.
    I would like to change the name of the file instead of it, entering it automatically.
    Below you see in blue I have the file name highlighted.
    How can I change the code to let me enter a new file name?
    Actually I would be happy to just stop the code at that window, let me enter my filename.

    '
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveWorkbook.SaveAs Filename:= _
    "C:\WINDOWS\Personal\Completed Proposals\Proposal for XL.xls", FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    Application.CommandBars("Forms").Visible = False
    End Sub


    Thank you,
    Michael
    Gotta to do some code

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    'I didn't test this, but it should be close:

    dim fname as string, path as string
    path = "C:\WINDOWS\Personal\Completed Proposals\"
    fname = InputBox("Enter file name")
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveWorkbook.SaveAs Filename:= path & fname, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    Application.CommandBars("Forms").Visible = False
    End Sub

  3. #3
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Even better is there a way to enter the file name according to a cell?

    let me explain!

    Cell "N8" on the spreadsheet has the actual proposal number that we use to name the file.

    The file now automatically saves it to Proposal for XL

    Can I have it save it to "Proposal" & Cell N8 somehow?
    Gotta to do some code

  4. #4
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    path = "C:\WINDOWS\Personal\Completed Proposals\"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveWorkbook.SaveAs Filename:= path & "Proposal " & _
    Str(Application.Range("N8").value), FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    Application.CommandBars("Forms").Visible = False
    End Sub

    I'd recommend saving before printing though. This allows the filename to automatically print in the footer, if you desire.

  5. #5
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Thank You so much.
    This sure will make it much easier.

    Michael
    Gotta to do some code

Posting Permissions

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