none
Excel SaveAs Macro Prompt for path, Cell as filename, Close Original file, Open New File

    Question

  • I am building a workbook that will be used by multiple people at work.  I need an Excel SaveAS Macro that allows the user to choose the path, but use a cell in the worksheet as the filename.  I seem to be able to find an example of letting the user define the path, and an example of using a worksheet cell as the filename, but I can't seem to figure out how to make the 2 work together. Here's what I've got so far:

    Sub SaveAsNewFile()

        Dim NewFileType As String
        Dim NewFileName As String

        Application.ScreenUpdating = False    ' Prevents screen refreshing.
       

        NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
                   "Excel Files 2007 (*.xlsx), *.xlsx,"
       

        NewFileName = Range("B18").Value
        ActiveWorkbook.SaveAs Filename:=NewFileName
       

        If NewFile <> "" And NewFile <> "False" Then
            ActiveWorkbook.SaveAs Filename:=NewFile, _
                FileFormat:=xlNormal, _
                Password:="", _
                WriteResPassword:="", _
                ReadOnlyRecommended:=False, _
                CreateBackup:=False
        End If
         Application.ScreenUpdating = True

    End Sub

    Monday, July 25, 2011 8:23 PM

Answers

  • Need some clarification.

    Will the code be in the workbook to be saved or in a separate workbook?

    You have 2 file types shown in your post (.xls and .xlsx) and then in the save code you use xlNormal. What type of file do you want to save as? (1997-2003 or 2007-2010) or maybe you want to save in whatever version of excel the user has. Therefore does your code need to test the version of excel being used and save in that version format?

    If the code will be in the workbook to be saved then if saving as xl2007/10 type file then will need to save as an Excel Enabled workbook (.xlsm).

    I am assuming that because the user needs to be able to nominate the path that you would like the Save As dialog box to pop up and the user selects the path from there.

    The following code opens a dialog box for the user to select the path and it identifies the version of xl and displays the dialog box relative to the version of excel being used saves as either 97-2003 format or 2007 format depending on the users version.

    Note that even though the user clicks Save in the dialog box, it really does not save until it reaches the code to save. If the user cancels out of the dialog box then a message is displayed.

    the code could be placed in another workbook and then set the required workbook where the comment refers to using another workbook.

    If you have problems then feel free to get back to me.

    Sub SaveAsNewFile()
        Dim wb As Workbook
        Dim NewFileName As String
        Dim NewFileFilter As String
        Dim myTitle As String
        Dim FileSaveName As Variant
        Dim NewFileFormat As Long
       
        Set wb = ThisWorkbook
       
        'Use following code to set to workbook other than this one
        'Set wb = Workbooks("My Test Save As File.xlsm")
       
        If Application.Version >= 12 Then   'Version 12 is xl2007
          'Note: If file extension not included in B18 then concatenate it
          NewFileName = wb.Sheets("Sheet1").Range("B18").Value & ".xlsm"
          NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
          'The value 52 is substituted in next line for the constant _
           xlOpenXMLWorkbookMacroEnabled because earlier versions of _
           excel will not recognize the constant and code will error.
          NewFileFormat = 52
        Else
          'Note: If file extension not included in B18 then concatenate it
          NewFileName = wb.Sheets("Sheet1").Range("B18").Value & ".xls"
          NewFileFilter = "Microsoft Excel Workbook (*.xls), *.xls"
          'Because xlNormal is an earlier version constant, later versions _
           of excel will recognize it.
          NewFileFormat = xlNormal
        End If
       
        myTitle = "Navigate to the required folder"
       
        FileSaveName = Application.GetSaveAsFilename _
                (InitialFileName:=NewFileName, _
                 FileFilter:=NewFileFilter, _
                 Title:=myTitle)
        If Not FileSaveName = False Then
          wb.SaveAs Filename:=FileSaveName, _
                        FileFormat:=NewFileFormat
        Else
          MsgBox "File NOT Saved. User cancelled the Save."
        End If

    End Sub 


    Regards, OssieMac
    • Marked as answer by TxDaisy1962 Tuesday, July 26, 2011 1:47 PM
    Tuesday, July 26, 2011 5:06 AM

All replies

  • Need some clarification.

    Will the code be in the workbook to be saved or in a separate workbook?

    You have 2 file types shown in your post (.xls and .xlsx) and then in the save code you use xlNormal. What type of file do you want to save as? (1997-2003 or 2007-2010) or maybe you want to save in whatever version of excel the user has. Therefore does your code need to test the version of excel being used and save in that version format?

    If the code will be in the workbook to be saved then if saving as xl2007/10 type file then will need to save as an Excel Enabled workbook (.xlsm).

    I am assuming that because the user needs to be able to nominate the path that you would like the Save As dialog box to pop up and the user selects the path from there.

    The following code opens a dialog box for the user to select the path and it identifies the version of xl and displays the dialog box relative to the version of excel being used saves as either 97-2003 format or 2007 format depending on the users version.

    Note that even though the user clicks Save in the dialog box, it really does not save until it reaches the code to save. If the user cancels out of the dialog box then a message is displayed.

    the code could be placed in another workbook and then set the required workbook where the comment refers to using another workbook.

    If you have problems then feel free to get back to me.

    Sub SaveAsNewFile()
        Dim wb As Workbook
        Dim NewFileName As String
        Dim NewFileFilter As String
        Dim myTitle As String
        Dim FileSaveName As Variant
        Dim NewFileFormat As Long
       
        Set wb = ThisWorkbook
       
        'Use following code to set to workbook other than this one
        'Set wb = Workbooks("My Test Save As File.xlsm")
       
        If Application.Version >= 12 Then   'Version 12 is xl2007
          'Note: If file extension not included in B18 then concatenate it
          NewFileName = wb.Sheets("Sheet1").Range("B18").Value & ".xlsm"
          NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
          'The value 52 is substituted in next line for the constant _
           xlOpenXMLWorkbookMacroEnabled because earlier versions of _
           excel will not recognize the constant and code will error.
          NewFileFormat = 52
        Else
          'Note: If file extension not included in B18 then concatenate it
          NewFileName = wb.Sheets("Sheet1").Range("B18").Value & ".xls"
          NewFileFilter = "Microsoft Excel Workbook (*.xls), *.xls"
          'Because xlNormal is an earlier version constant, later versions _
           of excel will recognize it.
          NewFileFormat = xlNormal
        End If
       
        myTitle = "Navigate to the required folder"
       
        FileSaveName = Application.GetSaveAsFilename _
                (InitialFileName:=NewFileName, _
                 FileFilter:=NewFileFilter, _
                 Title:=myTitle)
        If Not FileSaveName = False Then
          wb.SaveAs Filename:=FileSaveName, _
                        FileFormat:=NewFileFormat
        Else
          MsgBox "File NOT Saved. User cancelled the Save."
        End If

    End Sub 


    Regards, OssieMac
    • Marked as answer by TxDaisy1962 Tuesday, July 26, 2011 1:47 PM
    Tuesday, July 26, 2011 5:06 AM
  • OssieMac,

    This worked perfectly.  Thank you so much. 

    Tuesday, July 26, 2011 1:47 PM
  • What if I wanted to put a word in front of the cell value.  For example.  The cell contains a work order number such as 122234.  I want to save the cell as "SRO Closeout 122234" since my mailbox that receives this file will rout it to a "SRO Closeout" folder.
    Wednesday, September 21, 2011 2:21 AM
  • Simpley concatenate the required words with the file name.

    Example for xl2007 or xl2010 with file extension XLSM

    NewFileName = "SRO Closeout " & wb.Sheets("Sheet1").Range("B18").Value & ".xlsm"

     

    Example for earlier versions of xl with file extension XLS

    NewFileName = "SRO Closeout " & wb.Sheets("Sheet1").Range("B18").Value & ".xls"

    NOTE: If the cell contains the file name including the file extension then omit the last part of the concatenation otherwise will result in the file extension doubled up.


    Regards, OssieMac
    Wednesday, September 21, 2011 3:00 AM
  • Hi 

    Can you please tell me how to add a specific directory so that I do not have to navigate to it?

    I wish to add a default directory to this script!

    Thanks

    Rob

    Thursday, February 09, 2012 10:00 AM
  • Hello Rob,

    Record the code to save the file and then use the recorded code in lieu of the following.

      myTitle = "Navigate to the required folder"
       
        FileSaveName = Application.GetSaveAsFilename _
                (InitialFileName:=NewFileName, _
                 FileFilter:=NewFileFilter, _
                 Title:=myTitle)
        If Not FileSaveName = False Then
          wb.SaveAs Filename:=FileSaveName, _
                        FileFormat:=NewFileFormat

    If you wish to save in different version formats then use the If/Else/End If to determine the version being used and record code using the different versions. If you just want to save from one version then determining the version is irrelevant.


    Regards, OssieMac

    Thursday, February 09, 2012 11:12 AM
  • I'm new to vba and I'm stumped. This script is picking up cell c29 as my file name, how do I change that to b1. I thought it was changing "b18" but thats not it, please advise.

    Tuesday, February 14, 2012 9:32 PM
  • Make sure you are changing the "b18" to whatever cell you want to use in both sections of the code, I did this and it works just fine for me.

    Now on to a new challenge... Here's an overview of my workbook; containing 3 worksheets, names of the worksheets are in paranthesis.

    1. "Start". This contains 2 macros. 1 that prompts to browse to a folder containing CSV log files, these files are merged and outputted as one continuouse stream of data on worksheet #3 titled "MasterCSV". The second macro is used to manipulate this raw data further and isolate a single system or station that contains 11 columns of data per station (56 total stations, so ya it's A LOT of data). This macro pulls out an individual satations data and outputs it to worksheet 2 "Results".

    2. "Results" Contains the data for a single test station to analyze. On this page is where I placed a buttton for this 'savefile' macro to output the results to a new file that can be edited, analyzed, and shared via e-mail. But I do not need the "Start" worksheet or the "MasterCSV" worksheet to remain and right now it does. It also leaves all the vba code in the file forcing it to be saved as a 'xlsm' file when I would rather it be a normal xls file with no macros in it at all. This new file will be e-mailed to engineers requesting test data from units for which the massive log files were created. Right now it's very cumbersome to sort through 616 columns of data, thus the reason for this macro project. :)

    3. "MasterCSV" contains all the raw data for multiple CSV log files that have been merged into one stream of data. This is not needed in the new file.

    Once someone runs the first 2 macros, I want the new one to allow them to export to a new file, leaving the original workbook containing the macros open, so that if they need to or want to, they can use the macro on sheet "Start" again to analyze and capture data from another unit. Then export that file if need be, etc, etc. Right now my project works perfect, with the exception of exporting the "Results" sheet to a new macro-less 'xls' file.  Can someone tackle this one? I'm stumped. Thanks a million!!!

    Thursday, February 23, 2012 2:38 PM
  • I'm new to vba and I'm stumped. This script is picking up cell c29 as my file name, how do I change that to b1. I thought it was changing "b18" but thats not it, please advise.

    Hello texastwostep,

    I am the author of the original answer code.

    Use Find and Replace in the VBA editor to replace the B18 with B1. (Find and Replace is found in menu Item Edit -> Replace). Do this with a new copy of my code from the post above; not on code you have already edited in case that should cuse it to miss one of the changes.

    Also does your cell B1 include the file extension? The code determines the version of Excel being used and therefore the cell should not contain the file extension.

    Of course if you are always saving as a specific version and are not using the part of the code to determine the Excel version then above does not apply.

    If above does not work then post the code as you have edited it and answer the following questions and I will edit the code for your purpose.

    1. Do you want the code in the same workbook as the one being saved or a separate workbook?

    2. What version (or versions) of Excel are you using?

    3. Do you need to determine the Version of Excel for saving?

    4. What is the name of the workbook to be saved as entered in your cell B1?

    5. What is the name of your worksheet in which your cell B1 is located?


    Regards, OssieMac

    Thursday, February 23, 2012 8:12 PM
  • Hello, I Have a similar situation to the original post but I'm trying to get a little different outcome. Not sure if you can help but the above code is close so i thought i'd ask.

    The problem: I'm trying to save 10 different files from a master file, with names from a cell while letting the user pick the destination.  However, after each save i dont want the master file to be renamed. Is there a way to create new saved files while leaving the master file open? 

    The reason I want to keep the master file open is that the output files need to be macro free workbooks. I want the master file macro to continue running to make the neede changes and prompting save path's.

    Any suggestions would be appricated.  

    Brian

    Friday, February 24, 2012 3:29 PM
  • Could, ActiveWorkbook.SaveCopyAs ,work?

    Friday, February 24, 2012 4:18 PM
  • Hello, I Have a similar situation to the original post but I'm trying to get a little different outcome. Not sure if you can help but the above code is close so i thought i'd ask.

    The problem: I'm trying to save 10 different files from a master file, with names from a cell while letting the user pick the destination.  However, after each save i dont want the master file to be renamed. Is there a way to create new saved files while leaving the master file open? 

    The reason I want to keep the master file open is that the output files need to be macro free workbooks. I want the master file macro to continue running to make the neede changes and prompting save path's.

    Hello Brian,

    I know 2 ways of achieving this. My preferred method is to have the VBA code in a separate workbook. (I don't use the Personal workbook for macro code because it would just get too big.) I create a macro workbook for the project and that workbook only contains the code, one worksheet and a main menu on the worksheet. Workbook/s containing the data are opened and assigned to a workbook object variable.

    The other way is to export (copy) the worksheets to another workbook but if you have code in the worksheet modules then that gets exported also but if you want to distribute the workbooks without code then there seems to be no reason to have code in the worksheet modules.


    Regards, OssieMac

    Friday, February 24, 2012 8:46 PM
  • Awesome, that's a good solution. Already have most parts working!

    Thanks for the help.

    Brian

    Monday, February 27, 2012 2:27 PM
  • Run-time error '1004' app-defined or object-defined error?

    Any suggestions?

    If Not FileSaveName = False Then
          wb.SaveAs Filename:=FileSaveName, _
                        FileFormat:=NewFileFormat

    Tuesday, February 26, 2013 5:46 PM
  • I don't know what version of xl that you are saving as and I don't know what NewFileFormat is.

    Create your own example of Save as code by recording the code and you will probably pick up what the error is.


    Regards, OssieMac

    Tuesday, February 26, 2013 8:56 PM