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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

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()
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.


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.
mongopono.ru – 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
LIMITED TIME OFFER
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.

23 Comments

  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
    lot.

    • 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

      Thanks-PNRao!

  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!
      Thanks-PNRao!

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

    […] mongopono.ru […]

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

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

      Thanks-PNRao!

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

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

      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!

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

    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

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

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

  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

    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 - 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
    Range(“A1”).Select
    Application.DisplayAlerts = True
    Application.Quit

  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

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

Leave A Comment


Related pages


vba variable typesvba command buttonrecord macro powerpoint 2007hyperlink vba excelhiding a cell in excelfree manpower planning template excelvba range cellvba excel syntaxjoining cells in excelformula vlookup in excelms excel data validation listcreate folder vbarun macro excel 2007wait function in vbaexcel vba sheethow to make macros in excel 2007vba range propertieswith selection vbaactivex definitionvba coding basicslearn vba excelfrequently asked questions in technical interviewms excel 2007 formulas with examplesiferror functionexcel vba input boxsas code for beginnersvlookup between sheetsvba insert blank rowworkbooks closefso copyfilesql interview questions and answers for experienced pdfvba stringschart templates in excelcheck duplicates excelseriescollection vbaexcel formula countifdelete duplicates excelvba function leftvlookup example in hindivbcritical vbaexcel isemptyvba examples for excelaverageifs excelexcel vba specify rangehow to remove blanks in excelexcel macro free workbookexcel shortcut to delete rowcarriage return excellocation of personal xlsbexcel vba range functionhow do i create a dashboard in excelautomation vbaexcel macro paste valueshow to insert title in excelvba excel macro tutorialhow to use the countif function in excel 2013how to make a drop down list in excel 2007how to unlock the excel passwordsql interview questions for experienced pdfwhat is a userform in excelfinding duplicates excelvba module excelwhat is vlookup function in excelautofit excel columnsawesome excel chartsexcel functions vlookupmsgbox yes no vbaexample excel macrosql operandsvba adodb recordsetselect sheet excel vbaadd developer tab to ribbon in microsoft wordremove blank cells in excel 2010