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):
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 Workbook to Specific Folder
- Set to an Object and Save it
- Save Workbook to Specific Folder using Save Dialog Box
- Save Workbook in the same location of the Macro (this) Workbook
- Save the Workbook
- Download: Example Macro Workbook
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.
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:
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to execute itit
Your means of describing all in this paragraph is really good,
every one be able to easily understand it, Thanks a
lot.
Thanks Katharina-PNRao!
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.”
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!
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?!!
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!
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?
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.
The below code will save the active workbook in the given path with file name mentioned with the full path at A1.
Thanks-PNRao!
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
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!
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
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”…
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!
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
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
Argh…Ok. Where the quotes are should be username and New Folder respectively.
Sorry for all the extra comments.
Great, it really helped me!!
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?
I have the same problem. Maybe sone ideas?
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
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
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.hsbcgb001Redir GB USERS LAPTOP43960692DocumentsShanehello.xlsm.”
.Display
End With
On Error GoTo 0
.Close savechanges:=False
End With
End Sub
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?
Here is the Macro to save the file into required folders using VBA.
Thanks!
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
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
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
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