VBA save as Workbook Excel Macro code helps Save file to a specific Folder, its is a common task in automation process. Once you are done with actual calculations or task, at end of the procedure we generally call a procedure to export or Save the Output File to a Specific Folder or common drive. Or in other case you may not have the permissions to Save the File in a location, so that you can use SaveAs Option to store the revised or updated file.

VBA save as Workbook – Solution(s):

Save Workbook Using Excel VBA to Specific Folder You can use SaveAs method to Save the File to a specific location. You can Save with the same File Name and Location. Or you can use different File Name and Location to Save the File. You can also set to an object and Save the File.

In other method, you use Save Dialog Box. So that user can choose a specific folder to save the Excel File.

Save Workbook to Specific Folder – Example Cases:

Save a Workbook to a Specific Folder

The following example show you how to save an Excel Workbook in Specific folder using SaveAs method:

Sub ExampleToSaveWorkbook()

Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs "C:\WorkbookName.xls"
'OR
'ActiveWorkbook.SaveAs Filename:="C:\WorkbookName1.xls"

End Sub
Set to an Object and Save it

Set to an Object and Save it, so that it is easy to refer to your workbook to do further tasks. If you are dealing with more than one workbook, you will need this method to access a specific Excel Workbook.

Sub ExampleToSaveWorkbookSet()

Dim wkb As Workbook
'Adding New Workbook
Set wkb = Workbooks.Add
'Saving the Workbook
wkb.SaveAs "C:\WorkbookName.xls"
'OR
'wkb.SaveAs Filename:="C:\WorkbookName1.xls"

End Sub
Save Workbook to Specific Folder using Save Dialog Box

You can Save the Workbook to Specific Folder by showing the Save Dialog Box to user. So that user can choose desired location to save the file.

SaveAs dialogbox

Sub sbSaveExcelDialog()

Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Sample Output"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsm), *.xlsm")

If sFileSaveName <> False Then
    ActiveWorkbook.SaveAs sFileSaveName
End If

End Sub

Save Workbook in the same location of the Macro (this) Workbook

You can save the workbook in the same directory of the macro workbook using ThisWorkbook.Path property.

Sub ExampleToSaveWithSamePathDifferentName()
Dim sFilename As String
sFilename = "WorkbookName.xls" 'You can give a nem to save

Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & sFilename

End Sub

Save the Workbook

You can simply save the file without changing its file name or path name using Save method.

Sub ExampleToSaveWithSameNameandPath()

'Saving the Workbook
ActiveWorkbook.Save

End Sub
Example Files

You can download the example file and explore it.
ANALYSISTABS – Save Workbook

Overwrite an Existing Workbook using VBA

While Saving the existing workbook or a new excel file with existing name, Excel will prompt a warning message. It will interrupt the procedure and ask user to press yes or no for overwriting a file.

Overwrite an Existing Workbook using VBA – Solution:

Overwrite an Existing Workbook in Excel VBA
You can avoid this by disabling the alerts temporarily and save the workbook with the same name by setting the Application.DisplayAlerts=False property. Once you are done with the task, you should enable the application alerts by setting the property TRUE.

Overwrite an Existing Workbook using VBA – An Example

The following example will show you, how to overwrite a file by disabling the application alerts.

Code:
sub procedure to over write an excel file
Sub ExampleToOverWriteExistingWorkbook()
'Declaration: Declaring the variable
Dim wkb As Workbook

'Adding New Workbook using Workbook.Add method and setting to wkb Object
Set wkb = Workbooks.Add

'Saving the Workbook

'Desable the application alerts before svaing the file
Application.DisplayAlerts = False

'Now save the file
wkb.SaveAs "C:\WorkbookName.xls" ' change to existng file name
'OR
'wkb.SaveAs Filename:="C:\WorkbookName1.xls"</span>

'Eanbling the Application Aletrts after saving the file
Application.DisplayAlerts = True

End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute itit
Premium Project Management Templates
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE85% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: January 17th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

28 Comments

  1. Katharina February 3, 2014 at 9:22 AM

    Your means of describing all in this paragraph is really good,
    every one be able to easily understand it, Thanks a
    lot.

  2. Rob October 15, 2014 at 5:30 AM

    Having trouble using the below code. Any ideas?
    SaveFile = Application.GetSaveAsFilename( _
    FileFilter:=”Excel Files (*.xlsx), *.xlsx”)
    ActiveWorkbook.SaveAs SaveFile

    After the file is saved it appears as an Excel file in the indicated location, but when you try to open it a dialogue box says: “Excel cannot open the file because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.”

    • PNRao October 18, 2014 at 1:57 PM

      Hi Rob,
      I found no issues in your code, you may be using Excel 2003. If your Excel is not 2007 or higher, you can change the “Excel Files (*.xlsx), *.xlsx” as “Excel Files (*.xls), *.xls”

      i.e; xls, instead of xlsx

      Thanks-PNRao!

  3. Jason Funnell November 29, 2014 at 3:25 AM

    Hi. This is great help thanks! But I am trying to combine two codes but I cannot do it! I would like the macro to save the workbook as a file name taken from a cell and also save in in a certain folder location.! Please can you help?!!

    • PNRao November 29, 2014 at 7:44 PM

      Hi Jason,
      Assuming you have the Folder path at Range A1 of sheet1, and File name at Range A2:

      wkb.SaveAs Filename:=Sheets(“Sheet1”).Range(“A1″) &”\” &Sheets(“Sheet1”).Range(“A2″) &”.xlsx”

      Hope this helps!
      Thanks-PNRao!

  4. Chris January 7, 2015 at 3:02 AM

    Hi,
    Can I use a macro to save the excel file in a specific location based off of a specific cell within the document? Also, Could I have the macro change the name of the document based off of a different cell within the same document?

    • PNRao January 12, 2015 at 9:38 PM

      Hi Chris,

      Yes, you can use SaveAs method of workbook to save the file with different name. Assuming you have the file name in the Cell A1.

      The below code will save the active workbook in the same path with name specified at A1.

      
      Dim strFilename As String
      strFilename = ActiveWorkbook.Path & "\" & Range("A1") & ".xlsm"
      ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=52
      

      The below code will save the active workbook in the given path with file name mentioned with the full path at A1.

      
      Dim strFilename As String
      strFilename =Range("A1") & ".xlsm"
      ActiveWorkbook.SaveAs Filename:=strFilename, FileFormat:=52
      

      Thanks-PNRao!

  5. mo March 25, 2015 at 6:04 PM

    hi,

    i am trying to use this code but having some issues,

    i am using excel 2010 but the extension .xlsm nor .xlsx is working

    this code:

    If sFileSaveName False Then
    ActiveWorkbook.SaveAs sFileSaveName
    End If

    is saving a copy of the file but closes the original document to reopen the saved copy.

    please help

    • PNRao April 1, 2015 at 7:06 PM

      Hi,

      SaveAs will save the file with the changes in the specified location. It will not neither close the old file nor open new file.
      when you open the file, it will always stores in the temporary memory, when you Save the file, this will just save the changes to the existing file – SaveAs will save the file in the specified location.

      Hope this clarifies your query.
      Thanks-PNRao!

  6. Saputro July 18, 2015 at 6:19 PM

    Hi
    How to keep text format when save as to *.csv or at least to show “keep using that format” question dialog with VBA

    Thanks

  7. :) November 27, 2015 at 4:56 PM

    save file as .xls…. for that first go to the “save as type”.. then choose “.xls” extension file… ur file automatically will b saved as “filename.xls”…

  8. DLoughry December 17, 2015 at 8:01 PM

    What code do I use so that the file saves to a folder in a users My Documents. Now, the folder might not exist the first time they are saving the file, so I would need to incorporate that as well into the code.

    Example path: C:\Users\\Documents\\Workbook1.xlsx

    Thanks in advance!

  9. DLoughry December 17, 2015 at 8:02 PM

    What code do I use so that the file saves to a folder in a users My Documents. Now, the folder might not exist the first time they are saving the file, so I would need to incorporate that as well into the code.

    Example path: C:\Users\\Documents\\Workbook1.xlsx

    Thanks in advance

  10. DLoughry December 17, 2015 at 8:05 PM

    The example path did not show correctly in my initial comment. Between Users and Documents it should show “” and between Documents and the file name it should show “”.

    C:\Users\””\Documents\””\Workbook1.xlsx

  11. DLoughry December 17, 2015 at 8:07 PM

    Argh…Ok. Where the quotes are should be username and New Folder respectively.

    Sorry for all the extra comments.

  12. Guz January 2, 2016 at 2:39 AM

    Great, it really helped me!!

  13. T.Lajos April 12, 2016 at 11:09 PM

    Hi,
    I tried several code to save my excxel file. All attemts including your “Save Workbook in the same location of the Macro (this) Workbook” results the same: I get a new but empty file (with your code workbookname.xls) in the same directory where workbook using ThisWorkbook.There is data in cells of original workbook but the saved one is empty…(excel 2007)..
    Could you Have please some idea what couses this curious phenomena?

    • MK July 27, 2016 at 4:26 PM

      I have the same problem. Maybe sone ideas?

  14. Patrick Mahoney August 26, 2016 at 2:41 AM

    Hi to all,
    I am using Excel 2011 for Mac. In a macro file (.xlsm) triggered from my database, with an AutoOpen macro, I import data from my database, format the spreadsheet and then save it as a .xlsx file. My problem is that when the macro does a SaveAs, there is a dialog box telling me that the macros will be removed in the .xlsx file and then required that I click on the save button. How can I bypass that dialog and finish the save process without user intervention?

    My code:

    ThisFile = Range(“B2”).Value
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=True
    Range(“A1”).Select
    Application.DisplayAlerts = True
    Application.Quit

  15. Sub December 29, 2016 at 7:20 PM

    I am passing a password through the variable “f” and trying to protect the workbook but it is not working.
    Can you tell me how?
    ActiveWorkbook.Protect Password:=f, Structure:=True, Windows:=False

  16. Shane June 16, 2017 at 8:21 PM

    Hi All,

    I am trying to add in code to save the below to a shared drive with a date in the name of the file ie “abc 16.06.2017” Would also love to send a print screen in the body of the mail too – can anyone help with that?

    Thanks a mill

    Sub Mail_ControlSheet()

    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

    With Destwb
    Select Case Sourcewb.FileFormat
    Case 51: FileExtStr = “.xlsx”: FileFormatNum = 51

    End Select

    End With

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    With Destwb
    With OutMail
    .to = “xx@abc.com”
    .CC = “”
    .BCC = “”
    .Subject = “XX ” & Format(Date, “dd-mm-yyyy”)
    .Body = “Hi All,” & vbCrLf & vbCrLf & “XX.” & vbCrLf & vbCrLf & “Many Thanks” & vbCrLf & “Shane”
    .Attachments.Add “\\hbeu.adroot.hsbc\gb001\Redir GB USERS LAPTOP\43960692\Documents\Shane\hello.xlsm.”
    .Display
    End With
    On Error GoTo 0
    .Close savechanges:=False
    End With

    End Sub

  17. George September 9, 2017 at 1:52 AM

    I am trying to save file RENT.xls in different folders:
    My Documents/Jan 17/ RENT.xls
    My Documents/Feb/RENT.XLS
    My Documents/Mar/RENT.xls
    etc
    etc

    Can someone help please?

    • PNRao September 12, 2017 at 11:27 PM

      Here is the Macro to save the file into required folders using VBA.

      Sub sbSaveFileInToDifferentFolders()
      
      Set wb = Workbooks.Open("C:\Temp\RENT.xls")
      
      MyFoldersArray = Array( _
      "C:\Temp\FolderA", _
      "C:\Temp\FolderB", _
      "C:\Temp\FolderC" _
      )
      
      For iCntr = 0 To UBound(MyFoldersArray, 1)
          wb.SaveAs MyFoldersArray(iCntr) & "\" & wb.Name
      Next
      
      End Sub
      

      Thanks!

  18. chinkleet shukla October 24, 2018 at 2:12 PM

    hi,
    After saving the workbook in a specific folder as shown below
    Sub ExampleToSaveWorkbook()

    Workbooks. Add
    ‘Saving the Workbook
    ActiveWorkbook.SaveAs “G:\LookupExercise.xlsx”
    ‘OR
    ‘ActiveWorkbook.SaveAs Filename:=”G:\LookupExercise.xlsx”

    End Sub
    I am not able to see the content in the workbook why?. I am a little bit confused in the context can you explain please.
    Thanks

  19. Aaron February 14, 2019 at 11:48 PM

    I need help with the following. I need to save a copy of a workbook with the File Name and the Date (A Save at that moment), and then open that saved Copy. Currently the below code will Save the file and rename the document correctly (except it will do .xlsm.xslm and I cant fix this…) but when you open the document, there is no information. Its completely blank…. so its not a save as, its just opening a new file and naming based on my file.

    Can someone help me correct this code so that it saves all of my data, renames the file and opens it up once saved?

    Sub snwb()
    Dim thisWb As Workbook, d As Integer

    Set thisWb = ActiveWorkbook
    Workbooks.Add
    d = InStrRev(thisWb.FullName, “.”)
    ActiveWorkbook.SaveAs filename:=Left(thisWb.FullName, d – 1) & Format(Now, ” yyyy.mm.dd”) & Mid(thisWb.FullName, d) & “.xlsm”, FileFormat:=52

    ActiveWorkbook.Close savechanges:=False
    End Sub

    • Aaron February 15, 2019 at 4:36 AM

      I have an updated code that seems to be working correct, however, I need this to work with Office 365. I need the file to save back to the same location on 365. Any ideas?

      Sub SaveToRelativePath()
      Dim relativePath As String
      relativePath = ThisWorkbook.path & “\” & ActiveWorkbook.Name
      ActiveWorkbook.SaveAs filename:=ThisWorkbook.Name & ” FINAL BID” & “.xlsm”, FileFormat:=xlOpenXMLWorkbookMacroEnabled
      End Sub

  20. Sivaprakasam October 28, 2019 at 12:57 PM

    Hi can u provide the below u r VBA including view list (file size, created date and modified date), due to i have confusing the new file or old file.

    Save a Workbook to a Specific Folder

    Sub ExampleToSaveWorkbook()

    Workbooks.Add
    ‘Saving the Workbook
    ActiveWorkbook.SaveAs “C:\WorkbookName.xls”
    ‘OR
    ‘ActiveWorkbook.SaveAs Filename:=”C:\WorkbookName1.xls”

    End Sub

Leave A Comment