Save Workbook Using Excel VBA to Specific Folder

Home/Excel VBA/Save Workbook Using Excel VBA to Specific Folder

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.



50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

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()
'Saving the Workbook
ActiveWorkbook.SaveAs "C:\WorkbookName.xls"
'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"
'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.

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
'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
End Sub
Example Files

You can download the example file and explore it. – 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.

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
'wkb.SaveAs Filename:="C:\WorkbookName1.xls"</span>
'Eanbling the Application Aletrts after saving the file
Application.DisplayAlerts = True
End Sub
  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
By |January 17th, 2013|Excel VBA|23 Comments

About the Author:

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.


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

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

    • PNRao February 3, 2014 at 9:54 AM - Reply

      Thanks Katharina-PNRao!

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

    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 - Reply

      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


  3. November 29, 2014 at 3:25 AM - Reply

    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 - Reply

      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!

  4. December 17, 2014 at 9:06 PM - Reply

    […] […]

  5. Chris January 7, 2015 at 3:02 AM - Reply

    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 - Reply

      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


  6. mo March 25, 2015 at 6:04 PM - Reply


    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 - Reply


      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.

  7. Saputro July 18, 2015 at 6:19 PM - Reply

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


  8. :) November 27, 2015 at 4:56 PM - Reply

    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”…

  9. DLoughry December 17, 2015 at 8:01 PM - Reply

    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:02 PM - Reply

    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

  11. DLoughry December 17, 2015 at 8:05 PM - Reply

    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 “”.


  12. DLoughry December 17, 2015 at 8:07 PM - Reply

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

    Sorry for all the extra comments.

  13. Guz January 2, 2016 at 2:39 AM - Reply

    Great, it really helped me!!

  14. T.Lajos April 12, 2016 at 11:09 PM - Reply

    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 - Reply

      I have the same problem. Maybe sone ideas?

  15. Patrick Mahoney August 26, 2016 at 2:41 AM - Reply

    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
    Application.DisplayAlerts = True

  16. Sub December 29, 2016 at 7:20 PM - Reply

    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

  17. Shane June 16, 2017 at 8:21 PM - Reply

    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

    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 = “”
    .CC = “”
    .BCC = “”
    .Subject = “XX ” & Format(Date, “dd-mm-yyyy”)
    .Body = “Hi All,” & vbCrLf & vbCrLf & “XX.” & vbCrLf & vbCrLf & “Many Thanks” & vbCrLf & “Shane”
    .Attachments.Add “\\\gb001\Redir GB USERS LAPTOP\43960692\Documents\Shane\hello.xlsm.”
    End With
    On Error GoTo 0
    .Close savechanges:=False
    End With

    End Sub

Leave A Comment

Related pages

how to use hlookup in excel 2013h lookup formula in excelvba code excel examplesvba do until loop examplematch formula in excel 2007excel project planner templateexcel unsharehow to password protect excel sheetxml convert to excelcheckmarks in excelshort key for insert row in excelduplicate function in excelcombine multiple worksheets into one workbookhow to turn on developer tab in excel 2010ms excel remove duplicate rowsexcel vba connect to access databasecopy range vbaexample macro excelvba excel addressexcel vba project examplesvba function syntaxvba excel offsethow to unhide a tab in excelexcel vba matchexcel vba sheetexcel vba range functioncool excel graphspassword lock excelexcel macro to delete rowsexcel how to remove blank rowsvba excel tutorial pdfexample of a macro in excelv look up tutorialvba cut and pasterunning macro in excelhow to unlock excel spreadsheetuse of vlookup in excel 2010search duplicates in excelmacro to combine multiple workbooks into oneobjfso.deletefileunprotect excel sheet 2010sql query interview questions and answers for experienced pdfvba loop breakexcel vba color rgbdim array vbams access insert into vbaunhide hidden columnssql query excel worksheettest automation interview questions and answersunhide worksheet excel 2013excel macro rename fileexcel 2010 unprotect workbookdelete range vbaexcel test for nullexcel vba array functionssql interview questions and answers pdf downloadhide column in excelexcel sql connectionexcel query sqlexcel vba array functionsexcel data validation if statementmessage box vba yes noexcel insert columnvlookup excel functionunprotect worksheet excelhow to delete blank cells in excel 2010insert text box excelexcel vlookup function exampleexcel remove blankexcel function hlookupactivesheet namemicrosoft excel autofithow to use advanced filter in excel 2010pivot table macroexcel filter duplicate rowsshortcut to switch between excel workbookssql questions and answers for experienced