User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Excel VBA Workbooks - Reference, Open, Add, Name, Save, Activate, Copy & Close Workbooks; SendMail Method

 


Related Links:

Working with Objects in Excel VBA

Excel VBA Application Object, the Default Object in Excel

Microsoft Excel VBA - Worksheets

Excel VBA Range Object, Referencing Cells and Ranges

Excel VBA Custom Classes and Objects


 

--------------------------------------------------------------------------------------

Contents:

The Workbook Object

Reference a Workbook object ie. a single workbook

Open, Add, Copy & Close Workbooks

Saving Workbooks

Some often used Methods & Properties of the Workbook Object

--------------------------------------------------------------------------------------

 

The Workbook Object appears next below the Application Object in Excel VBA object hierarchy, and represents a single workbook within the Excel application. The Workbooks Object refers to a collection of all currently open Workbooks (ie. all currently open Workbook Objects) in Excel.

 

Note that while working with Workbooks, you will use Properties & Methods of the Workbook Object as well as Properties & Methods of the Workbooks Object.

 

 

 

Reference a Workbook object ie. a single workbook.

 

To reference or return a Workbook object (single workbook), the following properties can be used.

 

Workbooks.Item Property

 

The Item Property of the Workbooks object (ie. Workbooks.Item property) refers to a single workbook in a collection. Syntax: WorkbooksObject.Item(Index), where Index is the workbook name or index number. You can also omit using the 'item' word - use syntax as WorkbooksObject(WorkbookName) or WorkbooksObject(IndexNumber). The index number starts at 1 for the first workbook opened or created and increments accordingly for each subsequent workbook (hidden workbooks are also included), the last workbook being returned by Workbooks(Workbooks.Count). The Count property of the Workbooks object - WorkbooksObject.Count Property - returns the number of workbooks in the collection. Refer below examples of using this property.

 

Close the workbook named "ExcelVBA1.xlsm":

Workbooks.Item("ExcelVBA1.xlsm").Close

Workbooks("ExcelVBA1.xlsm").Close

 

Close the workbook opened last, after saving it:

Workbooks(Workbooks.Count).Close SaveChanges:=True

 

Return the name of workbook with index number 2 ie. the second workbook which is opened / created:

MsgBox Workbooks(2).Name

 

Print preview of workbook with index number 2:

Workbooks(2).PrintPreview

 

Return the name of the last workbook opened or created:

MsgBox Workbooks(Workbooks.Count).Name

 

 

Example 1 - return names of all open workbooks:

 

Sub workbookNames()
'return name of each open workbook

Dim i As Integer
For i = 1 To Workbooks.Count

msgbox Workbooks(i).Name

Next i


End Sub

 

 

Example 2 - set variable to a workbook:

 

Sub workbookVariable()
'set variable to a workbook

 

Dim wb As Workbook
Dim i As Integer
'set the wb variable to the specified workbook:
Set wb = Workbooks("Excel2007Workbook - Copy.xlsx")

'refer total number of worksheets in the specified workbook using the Worksheets.Count property:

For i = 1 To wb.Worksheets.Count

'return the name of each worksheet using the Name property of the Worksheet object:

MsgBox wb.Worksheets(i).Name

Next i


End Sub

 

 

 

ActiveWorkbook Property of the Application object. This property returns the active workbook (ie. the workbook in the active window). Syntax: ApplicationObject.ActiveWorkbook. It is optional to specify the ApplicationObject.

 

MsgBox "Active Workbook's name is " & ActiveWorkbook.Name

 

 

ThisWorkbook Property of the Application object. This property is used only from within the Excel Application and returns the workbook in which the code is running currently. Syntax: ApplicationObject.ThisWorkbook. It is optional to specify the ApplicationObject.

 

MsgBox "This Workbook's name is " & ThisWorkbook.Name

 

 

Note that though most times the ActiveWorkbook is the same as ThisWorkbook, but it might not always be so. The active workbook can be different than the workbook in which the code is running, as shown by the following code example. The Active Object has been illustrated in detail in the section "Excel VBA Objects, Properties & Methods.".

 

Example 3 - ActiveWorkbook and ThisWorkbook:

 

Sub ActiveWorkbook_ThisWorkbook()
'Open two Excel workbook files ("Book1.xlsm" and "Book2.xlsm") in a single instance (this will enable all workbooks to access macros).
'Enter this code in the workbook "Book1.xlsm", which is also the active/selected workbook.

 

'returns "Book1.xlsm":
MsgBox "Active Workbook's name is " & ActiveWorkbook.Name

'returns "Book1.xlsm":

MsgBox "This Workbook's name is " & ThisWorkbook.Name

 

'activate "Book2.xlsm":

Workbooks("Book2.xlsm").Activate
'returns "Book2.xlsm", while ThisWorkbook remains "Book1.xlsm":
MsgBox "Active Workbook's name is " & ActiveWorkbook.Name

'returns "Book1.xlsm":

MsgBox "This Workbook's name is " & ThisWorkbook.Name

 

'activate "Book1.xlsm":
Workbooks("Book1.xlsm").Activate
'returns "Book1.xlsm":
MsgBox "Active Workbook's name is " & ActiveWorkbook.Name
'returns "Book1.xlsm":

MsgBox "This Workbook's name is " & ThisWorkbook.Name


End Sub

 

 

 

 

Open, Add, Copy & Close Workbooks

 

Workbooks.Open Method

 

Use the Workbooks.Open Method (Open Method of the Workbooks object) to open a workbook. Syntax: WorkbooksObject.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad). The FileName argument is required while all other arguments are optional to specify. We detail only some arguments here. The FileName argument is a String value specifying the workbook name which is to be opened. The file name (including extension) with its path should be specified, else omitting the path will look-up the file name in the current directory. Setting the UpdateLinks argument to 0 will not update external links or references when the workbook is opened, while specifying the value 3 will update the external links or references. Set the ReadOnly argument to True to open the workbook in read-only mode. The Password argument is a string value specifying the password required to open workbook which is password-protected, and omitting this argument will prompt the user for a password. The WriteResPassword argument is a string value specifying the password required to open a write-reserved workbook (ie. opening this file without the password will make it read-only), and omitting this argument will prompt the user for a password. Note that when a workbook is opened programmatically, macros are enabled by default.

 

 

Workbooks.Add Method

 

Use the Workbooks.Add Method (Add Method of the Workbooks object) to create a new workbook, which also becomes the active workbook. Syntax: WorkbooksObject.Add(Template). Using this method returns a workbook object. It is optional to specify the Template argument. This argument can be a string value specifying the name (and path) of an existing Excel file, and in this case the specified file acts as the template for the new workbook which is created (meaning that the new workbook will have the same content, formatting, macros & customization as the existing file which is specified). You can also define a constant (XlWBATemplate Enumeration) for this argument, and in this case the newly created workbook will contain a single sheet of the type which is specified viz. specifying the constant xlWBATChart (value -4109) will create one Chart sheet; xlWBATExcel4MacroSheet (value 3) - creates one Excel version 4 macro sheet; xlWBATExcel4IntlMacroSheet (4) - creates one Excel version 4 international macro sheet; xlWBATWorksheet (-4167) - creates one Worksheet. Omitting the Template argument will create a new workbook with the Excel default of three blank sheets, wherein the default number of sheets can be changed / set by using the Application.SheetsInNewWorkbook Property.

 

Default name of the new workbook(s): Creating new workbooks using the Add method - when the Template argument is omitted - the workbooks are named BookN, the first workbook created being Book1, followed by Book2, and so on; when the Template argument is the xlWBATWorksheet constant, the workbooks are named SheetN, the first workbook created being Sheet1, followed by Sheet2, and so on; using the constants xlWBATExcel4MacroSheet or xlWBATExcel4IntlMacroSheet, the workbooks are named MacroN, the first workbook created being Macro1, followed by Macro2, and so on; using the constant xlWBATChart, the workbooks are named ChartN;

 

 

Example 4: Use the Add method to create new workbook(s).

 

Sub WorkbooksAdd()
'use the Add method to create new workbook(s).

 

Dim i As Integer
Dim n As Integer
'entering 4 in the input box, will create four new workbooks with default names Book1, Book2, Book3 & Book4.
i = InputBox("Enter number of workbooks to create")

For n = 1 To i

Workbooks.Add

Next n


End Sub

 

 

 

Close Method

 

Use the Close Method of the Workbooks object to close all open workbooks. Syntax: WorkbooksObject.Close. This method accepts no arguments, and using this will prompt you for saving changes, if any changes have been made in the open workbook. You can set the DisplayAlerts property to False to not display any prompts or alerts. To close all open workbooks, use the code line Workbooks.Close. While dealing with multiple excel workbooks, remember that all files should have been opened in a single instance of Excel.

 

Use the Close Method of the Workbook object to close a single workbook. Syntax: WorkbookObject.Close(SaveChanges, Filename, RouteWorkbook). All arguments are optional to specify. Omitting the SaveChanges argument will prompt the user to save changes, in case any change has been made to the workbook after being saved last. Setting SaveChanges argument to True will save any changes made to the workbook, and if set to False, the workbook will close without saving any changes made and in both settings the workbook closes without displaying a prompt to save changes. The Filename argument should be used to specify a file name to close a workbook which does not yet have an associated file name (viz. a new workbook), else omitting this argument will ask the user to specify a file name before closing it. The Filename argument can be used optionally if you want to save an existing workbook (ie. which already has an associated name), with a new file name. Set the RouteWorkbook argument to True if the workbook is to be routed or sent to the next recipient, where a routing slip is present. Examples of using this method: for closing the active workbook after saving changes - ActiveWorkbook.Close SaveChanges:=True; to close the workbook named "Book1.xlsx", after saving changes - Workbooks("Book1.xlsx").Close SaveChanges:=True.

 

 

Example 5 - Open workbook, Close workbook(s)

 

Sub WorkbookOpenClose()
'open workbook, close workbooks

 

Application.ScreenUpdating = False
 
Dim wb As Workbook
Dim strPath As String, strName As String
Dim i As Integer, count As Integer

 

'open the workbook by assigning the path & file name to variables:
strPath = "C:\Users\User Name\Documents"

strName = "Book1.xlsx"
'open the workbook in read-only mode:
Workbooks.Open (strPath & "\" & strName), ReadOnly:=True

'omitting the path will look-up the file name in the current directory:
'change the current directory using the ChDir Statement - this sets the the new default directory or folder:
ChDir "C:\Users\User Name\Documents\Folder1"
'open workbook (omitting the path) without updating external links or references:
Workbooks.Open "Book2.xlsx", UpdateLinks:=0

'specifying the password required to open workbook which is password-protected:
Workbooks.Open fileName:="C:\Users\User Name\Documents\Folder2\Book3.xlsx", Password:="123"

 

'specifying the password required to open a write-reserved workbook (ie. opening this file without the password will make it read-only):
Workbooks.Open fileName:="C:\Users\User Name\Documents\Folder3\Book4.xlsx", WriteResPassword:="abc123"

'define the path to the same directory as this workbook (containing the code), to look-up the file name:
strPath = ThisWorkbook.Path
Workbooks.Open (strPath & "\" & "Book5.xls")

 

'close all open workbooks, except the workbook with this code:

'For Each wb In Application.Workbooks

'If Not wb.Name = ThisWorkbook.Name Then wb.Close SaveChanges:=True

'alternately:

'If wb.Name <> ThisWorkbook.Name Then wb.Close SaveChanges:=True

'Next

 

'Alternate - close all open workbooks, except the workbook with this code:

count = Workbooks.count

For i = count To 1 Step -1

If Workbooks(i).Name <> ThisWorkbook.Name Then Workbooks(i).Close SaveChanges:=True

Next i

 

Application.ScreenUpdating = True

 

End Sub

 

 

Example 6 - Create new workbook(s) using the Add method, then save and close.

For live code of this example, click to download excel file.

 

Sub WorkbookAdd()
'create new workbook(s) using the Add method, then save and close.

 

'set DisplayAlerts property to False to not display any prompts or alerts:
Application.DisplayAlerts = False
'change the current directory to ThisWorkbook directory, using the ChDir Statement - this sets the the new default directory or folder:

ChDir ThisWorkbook.Path

 

'-------------

'specifying the name of an existing file (in the same folder as ThisWorkbook) which is used as the template for the new workbook created using the Add method:
Workbooks.Add "Book1.xlsx"
MsgBox "New Workbook name and number of sheets: " & ActiveWorkbook.Name & "; " & ActiveWorkbook.Sheets.count

'save as .xls file, the Excel 97 - Excel 2003 file format, using the FileFormat Enumeration xlExcel8 (value 56):
ActiveWorkbook.SaveAs fileName:=ActiveWorkbook.Name, FileFormat:=xlExcel8
'enter "xlsFile" in cell (1,1) in active sheet of the new workbook:
ActiveWorkbook.ActiveSheet.Range("A1") = "xlsFile"
'close workbook after saving changes:

ActiveWorkbook.Close SaveChanges:=True

 

'-------------

'creates a new workbook with three worksheets (this is the Excel 2007 default for number of worksheets), unless the default has been changed using the Application.SheetsInNewWorkbook property:
Workbooks.Add
MsgBox "New Workbook name and number of sheets: " & ActiveWorkbook.Name & "; " & ActiveWorkbook.Sheets.count

'save as .xlsx file, the default Excel 2007 file format, using the FileFormat Enumeration xlOpenXMLWorkbook (value 51):
ActiveWorkbook.SaveAs fileName:=ActiveWorkbook.Name, FileFormat:=xlOpenXMLWorkbook
'enter "xlsxFile" in cell (1,1) in active sheet of the new workbook:
ActiveWorkbook.ActiveSheet.Range("A1") = "xlsxFile"
'close workbook after saving changes:

ActiveWorkbook.Close SaveChanges:=True

 

'-------------

'change the default number of worksheets to 5:
Application.SheetsInNewWorkbook = 5
'creates a new workbook with five worksheets
Workbooks.Add
MsgBox "New Workbook name and number of sheets: " & ActiveWorkbook.Name & "; " & ActiveWorkbook.Sheets.count

'save the new workbook as a .xlsm file, the Excel 2007 macro-enabled file format, using the FileFormat Enumeration xlOpenXMLWorkbookMacroEnabled (value 52) :
ActiveWorkbook.SaveAs fileName:="NewWorkbookSaved.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
'enter "xlsmFile" in cell (1,1) in active sheet of the new workbook:
ActiveWorkbook.ActiveSheet.Range("A1") = "xlsmFile"
'close workbook after saving changes:

ActiveWorkbook.Close SaveChanges:=True

 

'-------------

'create a new workbook containing one worksheet:
Workbooks.Add (xlWBATWorksheet)
MsgBox "New Workbook name and number of sheets: " & ActiveWorkbook.Name & "; " & ActiveWorkbook.Sheets.count

'save the new workbook, omitting the file format, this will save in the default file format of your current Excel version when you are saving a new file:
ActiveWorkbook.SaveAs fileName:=ActiveWorkbook.Name
'enter "DefaultFileFormat" in cell (1,1) in active sheet of the new workbook:
ActiveWorkbook.ActiveSheet.Range("A1") = "DefaultFileFormat"
'close workbook after saving changes:

ActiveWorkbook.Close SaveChanges:=True

 

'-------------

'create a new workbook containing one Chart sheet:
Workbooks.Add (xlWBATChart)
MsgBox "New Workbook name and number of sheets: " & ActiveWorkbook.Name & "; " & ActiveWorkbook.Sheets.count

'save as .xlsx file, the default Excel 2007 file format, using the FileFormat Enumeration xlOpenXMLWorkbook (value 51):
ActiveWorkbook.SaveAs fileName:=ActiveWorkbook.Name, FileFormat:=xlOpenXMLWorkbook
'close workbook:
ActiveWorkbook.Close

'restore DisplayAlerts property to True to display prompts or alerts:
Application.DisplayAlerts = True

'You might want to revert to the Excel 2007 default of three blank sheets, because the value set by using the SheetsInNewWorkbook property will be retained in the application after this session.

Application.SheetsInNewWorkbook = 3


End Sub

 

 

 

Workbooks.OpenDatabase Method

 

Use the Workbooks.OpenDatabase Method to get data from a database, stored in a table or a query, into an excel file. Syntax: WorkbooksObject.OpenDatabase(Filename, CommandText, CommandType, BackgroundQuery, ImportDataAs). This method returns a workbook object ie. a new workbook is opened, representing the database. All arguments except Filename are optional to specify. Use the Filename argument to specify the name of the database file from which you want to retreive data. Use the CommandText argument to specify the query string (SQL statement, table name, ...), to get the data. The CommandType argument specifies the command type of the query. You can specify one of the five constants: xlCmdTable (a table name); xlCmdSql (a SQL statement);  xlCmdDefault (command text that the OLE DB provider understands); xlCmdList (pointer to list data); xlCmdCube (cube name for an OLAP data source). Setting the BackgroundQuery to True will perform the query in the background (asynchronously), whereas the default value is False. Use the ImportDataAs argument to specify the format in which data is retreived from a database - specifying argument value as xlPivotTableReport returns the data as a PivotTable and specifying value as xlQueryTable (default value) returns data as Query Table.

 

Using the Workbooks.OpenDatabase Method when only the Filename argument is specified and not the optional arguments, a dialog box is displayed listing all the tables and queries in the database from which the user has to make a selection, after which a new workbook opens containing data from the selected table or query.

 

 

 

Example 7 - Use the Workbooks.OpenDatabase Method to import data into an Excel file as a query table.

 

 

 

Sub OpenDatabase_QueryTableFormat()
'get data from a database table, into an excel file, using the Workbooks.OpenDatabase Method.
'refer Image 1a which shows the "SalesManager" table in Access file "SalesReport.accdb; refer Image 1b which shows the excel file "OpenDatabase_QueryTable" into which data is imported.

 

'change the current directory to ThisWorkbook directory, in which the new workbook will be saved:
ChDir ThisWorkbook.Path
      
'use the Workbooks.OpenDatabase Method to import data into an Excel file as a query table, from the "SalesManager" table in Access file "SalesReport.accdb:
Workbooks.OpenDatabase fileName:="SalesReport.accdb", CommandText:="SalesManager", CommandType:=xlCmdTable, BackgroundQuery:=False, ImportDataAs:=xlQueryTable

'save as .xlsx file, the default Excel 2007 file format:
ActiveWorkbook.SaveAs fileName:="OpenDatabase_QueryTable", FileFormat:=xlOpenXMLWorkbook

'close workbook:

ActiveWorkbook.Close


End Sub

 

 

Example 8 - Use the Workbooks.OpenDatabase Method to import data into an Excel file as a pivot table.

 

 

 


 

Sub OpenDatabase_PivotTableFormat()
'get data from a database table, into an excel file, using the Workbooks.OpenDatabase Method.
'refer Image 1c which shows the "Performance" table in Access file "SalesReport.accdb;
'refer Image 1d which shows the excel file "OpenDatabase_PivotTable" into which data is imported;
'refer Image 1e which shows the excel file "OpenDatabase_PivotTable" wherein the fields displayed in the pivot report have been selected manually.

 

'change the current directory to ThisWorkbook directory, in which the new workbook will be saved:
ChDir ThisWorkbook.Path
      
'use the Workbooks.OpenDatabase Method to import data into an Excel file as a pivot table from Access file "SalesReport.accdb, using an SQL statement:
Workbooks.OpenDatabase fileName:="SalesReport.accdb", CommandText:="SELECT * FROM Performance WHERE EmployeeId >= 21", CommandType:=xlCmdSql, BackgroundQuery:=True, ImportDataAs:=xlPivotTableReport

'save as .xlsx file, the default Excel 2007 file format:
ActiveWorkbook.SaveAs fileName:="OpenDatabase_PivotTable", FileFormat:=xlOpenXMLWorkbook

'close workbook:
ActiveWorkbook.Close

 

End Sub

 

 

 

Workbook.SaveCopyAs Method

 

Use the Workbook.SaveCopyAs Method to save a copy of the workbook. The method doesn't modify the open workbook in memory. Syntax: WorkbookObject.SaveCopyAs(Filename). The Filename argument is optional to specify, and is used to specify the file name with which the workbook copy is saved. Note that the Workbook.SaveCopyAs Method allows saving with the same File Format only (ie. the workbook and its copy being saved should be of same file format), else while opening the saved copy you will get a prompt - "The file you are trying to open is in a different format than specified by the file extension ..." - and the file may or may not open. Whereas the Workbook.SaveAs Method allows you to specify the File Format for saving the file.

 

 

Example 9: Use the Workbook.SaveCopyAs Method to save a copy of Active Workbook which your are working in.

 

Sub WorkbookSaveCopyAs1()
'use the Workbook.SaveCopyAs Method to save a copy of Active Workbook which your are working in:

 

Dim lLastRow As Long


'determine last row with Data in a column (column "A"):
lLastRow = ActiveWorkbook.ActiveSheet.Range("A" & Rows.count).End(xlUp).Row

 

'save a copy of the workbook if data in a column of active sheet is over a specific number of rows:

If lLastRow >= 100 Then


'save a copy of Active Workbook which your are working in, specifying a file name (of same file format as the active workbook) - use this method to save your existing work, while your current workbook remains the active workbook:

ActiveWorkbook.SaveCopyAs "C:\MyDocuments\Folder1\WorkbookCopy.xlsm"

 

End If

 

'your current workbook remains the active workbook, the saved copy remains closed:

MsgBox ActiveWorkbook.Name

 

End Sub

 

 

 

Example 10: Use the Workbook.SaveCopyAs Method to save a copy of ThisWorkbook which your are working in, with a unique name everytime.

 

Sub WorkbookSaveCopyAs2()
'use the Workbook.SaveCopyAs Method to save a copy of ThisWorkbook which your are working in, with a unique name everytime:

 

Dim strName As String, strExtn As String, MyStr As String
Dim i As Integer, iLastDot As Integer

'change the current directory to the ThisWorkbook directory:
ChDir ThisWorkbook.Path

 

'find position of last dot, to distinguish file extension:

For i = 1 To Len(ThisWorkbook.Name)

If Mid(ThisWorkbook.Name, i, 1) = "." Then

iLastDot = i

End If

Next i

 

'extract file extension and dot before extension:
strExtn = Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - iLastDot + 1)
'extract workbook name excluding its name extension and dot before extension:
MyStr = Left(ThisWorkbook.Name, iLastDot - 1)

'specify name for the copy - the time part in the file name will help in indentifying the last backup, besides making the name unique:
strName = MyStr & "_" & Format(Now(), "yyyy-mm-dd hh-mm-ss AMPM") & strExtn

'save a copy of ThisWorkbook which your are working in, specifying a file name - use this method to save your existing work, while your current workbook remains the active workbook:
ThisWorkbook.SaveCopyAs strName

'your current workbook remains the active workbook, the saved copy remains closed:

MsgBox ActiveWorkbook.Name


End Sub

 

 

 

Example 11: Use the Workbook.SaveCopyAs Method to save a copy of a specified workbook.

 

Sub WorkbookSaveCopyAs3()
'use the Workbook.SaveCopyAs Method to save a copy of a specified workbook:

 

'open a specified workbook in the current directory:
Workbooks.Open "C:\MyDocuments\Folder2\ExcelFile.xlsx"

'the opened workbook becomes the active workbook:
MsgBox ActiveWorkbook.Name

'save a copy of the opened workbook, specifying a file name (the workbook and its copy being saved should be of same file format).
ActiveWorkbook.SaveCopyAs "C:\MyDocuments\Folder2\Copy of ExcelFile.xlsx"

'the opened workbook remains the active workbook, the saved copy remains closed:
MsgBox ActiveWorkbook.Name

'close the opened workbook - note that neither the opened workbook or the saved copy will be open after this command:

ActiveWorkbook.Close


End Sub

 

 

 

 

Saving Workbooks

 

Use the Workbook.Save Method (Save Method of the Workbook object) to save a specified workbook. Syntax: WorkbookObject.Save.

 

Save the workbook named "ExcelVBA1.xlsm":

Workbooks("ExcelVBA1.xlsm").Save

 

Use the SaveChanges argument in the Close Method of the Workbook object, to save the workbook before closing (refer to Close Method for details).

 

Close the workbook opened last, after saving it:

Workbooks(Workbooks.Count).Close SaveChanges:=True

 

 

Use the Workbook.SaveAs Method (SaveAs Method of the Workbook object) to save workbook changes in a separate file. Syntax: WorkbookObject.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local). All arguments are optional to specify. We detail only some arguments here. FileName is a string value, specifying the name and full path of the file to be saved and omitting the path will save the file in the current folder. FileFormat specifies the format of the file to be saved, as per the XlFileFormat enumeration list. The default file format is your current Excel version when you are saving a new file, whereas for an existing file the default is the file format which was last specified. Remember that in Excel 2007-2010 while using SaveAs, it is necessary to specify the FileFormat parameter to save a FileName with a .xlsm extension if the workbook being saved is not a .xlsm file viz. FileFormat:=xlOpenXMLWorkbookMacroEnabled or FileFormat:=52. You can specify a case-sensitive password upto 15 characters for file opening, using the Password argument. You can specify a password to open a file wherein opening the file without entering the password will open it as read-only - use the WriteResPassword argument to specify this password.

 

XlFileFormat Enumeration, some options are being mentioned here in the order of Constants, Value (Description): For Excel 2007-2013:- xlExcel12, 50 (".xlsb", Excel12, Excel Binary Workbook with macros or without in Excel 2007-13, Excel 2007 is version 12); xlOpenXMLWorkbook, 51 (".xlsx", Open XML Workbook, Workbook without macros in Excel 2007-13); xlOpenXMLWorkbookMacroEnabled, 52 (".xlsm", Open XML Workbook Macro Enabled, Macro-enabled workbook in Excel 2007-13); xlExcel8, 56 (".xls", Excel8, 97-2003 format in Excel 2007-13, Excel 97 is version 8); For Excel 97-2003:- xlWorkbookNormal, -4143 (".xls", Workbook Normal);

 

 

Example 12 - save an existing workbook with a new name & file format:

 

Sub workbookSaveAs1()
'save the workbook with index number 2 with a new name & file format.
'while dealing with multiple excel workbooks, remember that all files should have been opened in a single instance of Excel.

 

'Remember that in Excel 2007-2010 while using SaveAs, it is necessary to specify the FileFormat parameter to save a FileName with a .xlsm extension if the workbook being saved is not a .xlsm file viz. FileFormat:=xlOpenXMLWorkbookMacroEnabled or FileFormat:=52.

'to save .xlsm file as a .xlsm file, in Excel 2007:
Workbooks(2).SaveAs "WorkbookNameChanged.xlsm"

'to save non .xlsm file as a .xlsm file, in Excel 2007:
'Workbooks(2).SaveAs "WorkbookNameChanged.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled


End Sub

 

 

 

Example 13 - saving a new workbook:

 

Sub workbookSaveAs2()
'saving a new workbook.
'while dealing with multiple excel workbooks, remember that all files should have been opened in a single instance of Excel.

 

'Remember that in Excel 2007-2010 while using SaveAs, it is necessary to specify the FileFormat parameter to save a FileName with a .xlsm extension if the workbook being saved is not a .xlsm file viz. FileFormat:=xlOpenXMLWorkbookMacroEnabled or FileFormat:=52.

'add a new workbook:
Workbooks.Add

'save the new workbook, which becomes the active workbook, as password protected:
ActiveWorkbook.SaveAs fileName:="NewWorkbookSaved.xlsx", Password:="abcXYZ123"

'save the new workbook as a .xlsm file with password protection:
ActiveWorkbook.SaveAs fileName:="NewWorkbookSaved.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="abcXYZ123"

'save the new workbook as a .xlsm file with password protection, but can be opened as read-only without supplying the password:

ActiveWorkbook.SaveAs fileName:="NewWorkbookSaved.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, WriteResPassword:="abcXYZ123"

 

End Sub

 

 

 

Use the Workbook.Saved Property to determine whether any changes have been made to the workbook after saving it last. Syntax: WorkbookObject .Saved. This property returns a Boolean value - True indicates that the workbook was not modified after it was last saved. Setting this property to True before closing a workbook wherein changes have been made will not prompt to save the workbook and will also not save the changes.

 

 

Example 14 - Set the Workbook.Saved Property to TRUE to close the workbook with NO prompt to save changes:

 

Sub workbookSaved()
'Workbook.Saved Property - close the workbook with NO prompt to save changes:

 

'open a workbook named "ExcelFile.xlsx"
Workbooks.Open "C:\MyDocuments\ExcelFile.xlsx"

'Workbook.Saved Property will return True indicating no changes have been made to the workbook after it was last saved:
MsgBox Workbooks("ExcelFile.xlsx").Saved

'make changes to the workbook:
Workbooks("ExcelFile.xlsx").ActiveSheet.Range("A1") = "hello"

'Workbook.Saved Property will return False indicating that changes have been made to the workbook after it was last saved:
MsgBox Workbooks("ExcelFile.xlsx").Saved

'set Workbook.Saved Property to True:
Workbooks("ExcelFile.xlsx").Saved = True

'close the workbook with NO prompt to save changes and this will also NOT save any changes - if the Saved property was not set to True as above you will get a prompt to save changes while closing the workbook:

Workbooks("ExcelFile.xlsx").Close


End Sub

 

 

 

 

Some often used Methods & Properties of the Workbook Object

 

Workbook.Activate Method

 

Use the Workbook.Activate Method to activate a workbook. If the workbook has multiple windows, the method activates the first window. Syntax: WorkbookObject.Activate. To activate the workbook named "Workbook2.xlsx", use the code Workbooks("Workbook2.xlsx").Activate.

 

 

Workbook.PrintPreview Method

 

Use the Workbook.PrintPreview Method to view a preview of how the printed workbook will look. Syntax: WorkbookObject.PrintPreview(EnableChanges). It is optional to specify the EnableChanges argument, which accepts a Boolean value (default value is True), to allow or disallow the user to change the page setup options (ex. page orientation, scaling, margins, etc) available in print preview.

 

 

Example 15 - PrintPreview

 

Sub PrintPreview()

 

'print preview of active sheet of workbook named "ExcelFile.xlsx, disallowing the user to change the page setup options available in print preview.
Workbooks("ExcelFile.xlsx").PrintPreview EnableChanges:=False

'print preview of "Sheet3" of workbook "ExcelFile.xlsx, allowing the user to change the page setup options available in print preview.

Workbooks("ExcelFile.xlsx").Worksheets("Sheet3").PrintPreview EnableChanges:=True


End Sub

 

 

 

Workbook.SendMail Method

 

Users often use Outlook to email from Excel. One option is to automate Outlook from within Excel, allowing you more functionality to send emails, by working with Outlook objects using VBA in Excel. Automation is a process by which one application communicates with or controls another application. Refer our section "Automate Outlook using vba: Sending Email from Excel using Outlook" for details. Another option to send mails from Excel is using the Workbook.SendMail Method, which is a simple way to email a workbook. However, this method has some limitations - you can only specify recipient(s), subject & option of a return receipt. You do not have options to specify CC or BCC, include text in the body of your mail or send other files as attachment.

 

With the Workbook.SendMail Method, you use the installed mail system to send or email a workbook. Syntax: WorkbookObject.SendMail(Recipients, Subject, ReturnReceipt). The Recipients argument is necessary wherein you can specify a single recipient or multiple recipients as text or an array of text strings respectively. The Subject argument is optional, and is used to specify the subject of the mail. Omitting this argument will default the workbook name as the subject. The ReturnReceipt argument is also optional, wherein specifying True or False will request or not request a return receipt respectively, default value being False.

 

If you wish to add text to the mail of the body, use "" for the Recipient argument which displays the mail viz. ActiveWorkbook.SendMail Recipients:=""

 

 

Example 16 - Email workbook with sendmail method, specifying the recipient name or email.

 

Sub SendMail_1()
'email workbook with sendmail method, specifying the recipient name or email

 

Workbooks.Open ("C:\MyDocuments\Folder1\Book1.xlsx")

'specifies the recipient name as text:
ActiveWorkbook.SendMail Recipients:="David Kelly", Subject:="Hi", ReturnReceipt:=True

'specifies the recipient email:

ActiveWorkbook.SendMail Recipients:="This email address is being protected from spambots. You need JavaScript enabled to view it.", Subject:="Hi", ReturnReceipt:=True


End Sub

 

 

Example 17 - Email workbook with sendmail method, specifying multiple recipients as array.

 

Sub SendMail_2()
'email workbook with sendmail method, specifying multiple recipients as array

 

'specifies multiple recipients' names as array:
ThisWorkbook.SendMail Array("David Kelly", "Tracy Murray")

'specifies multiple recipients' emails as array:

ThisWorkbook.SendMail Array("This email address is being protected from spambots. You need JavaScript enabled to view it.", "This email address is being protected from spambots. You need JavaScript enabled to view it.")


End Sub

 

 

Example 18 - Email a Single Worksheet using the Sendmail method.

For live code of this example, click to download excel file.

 

Sub SendMail_3()
'email a specified worksheet(s) using the Sendmail method

 

Dim strName1 As String, strName2 As String
Dim wb As Workbook

'change the current directory to ThisWorkbook directory, using the ChDir Statement - this sets the new default directory or folder:
ChDir ThisWorkbook.Path

'creates a new workbook with a single sheet as copied from ThisWorkbook:
ThisWorkbook.Worksheets("Sheet1").Copy
'set variable to the new workbook, which becomes the active workbook:
Set wb = ActiveWorkbook
'rename the sheet in the new workbook:
wb.Sheets("Sheet1").Name = "NewSheet"
'save new workbook with a file name, in the default folder:
wb.SaveAs fileName:="NewBook.xlsx"

'assign variables for recipients' names:
strName1 = "David Kelly"
strName2 = "Tracy Murray"

'specifies multiple recipients' names as array; pick subject text from workbook range:
wb.SendMail Array(strName1, strName2), Subject:=ThisWorkbook.Worksheets("Sheet1").Range("A1"), ReturnReceipt:=False

'close the new workbook:

wb.Close


End Sub

 

 

Example 19 - Email workbook with sendmail method, specifying recipient from worksheet cell, with a date stamp in the subject.

 

Sub SendMail_4()
'email workbook with sendmail method, specifying recipient from worksheet cell, with a date stamp in the subject

 

Dim strRec As String

'Range("A14") contains: This email address is being protected from spambots. You need JavaScript enabled to view it.
strRec = ThisWorkbook.Sheets("Sheet1").Range("A14").Value

'subject will display as "Please Check 12/18/2013 10:43:06 AM"

ActiveWorkbook.SendMail Recipients:=strRec, Subject:="Please Check " & Format(Now, "mm/dd/yyyy hh:mm:ss AMPM")


End Sub

 

 

Example 20 - Email workbook with sendmail method, specifying multiple recipients from worksheet range.

 

Sub SendMail_5()
'email workbook with sendmail method, specifying multiple recipients from worksheet range

 

Dim MyArr As Variant

'Range("A14")=This email address is being protected from spambots. You need JavaScript enabled to view it., Range("A15")=This email address is being protected from spambots. You need JavaScript enabled to view it.
ActiveWorkbook.SendMail Recipients:=ThisWorkbook.Sheets("Sheet1").Range("A14:A15").Value

'MyArr = ThisWorkbook.Sheets("Sheet1").Range("A14:A15")
ActiveWorkbook.SendMail Recipients:=MyArr

'Range("A10")=David Kelly, Range("A11")=Tracy Murray; Range("A14")=This email address is being protected from spambots. You need JavaScript enabled to view it., Range("A15")=This email address is being protected from spambots. You need JavaScript enabled to view it.
'named ranges: "NameList"=Range("A10:A11"), "EmailList"=Range("A14:A15")
MyArr = ThisWorkbook.Sheets("Sheet1").Range("NameList")
MyArr = ThisWorkbook.Sheets("Sheet1").Range("EmailList")

ActiveWorkbook.SendMail Recipients:=MyArr


End Sub

 

 

 

Workbook.ActiveSheet Property

 

This property returns the currently Active Sheet in a workbook or window. Syntax: WorkbookObject.ActiveSheet. For a workbook appearing in multiple windows, the ActiveSheet might be different for each window. If there is no active sheet, this property returns Nothing. Use the Activate method of the Worksheet object to activate a sheet viz. ActiveWorkbook.Sheets("Sheet3").Activate will activate the sheet named "Sheet3" in the active workbook, which name will be displayed by MsgBox ActiveWorkbook.ActiveSheet.Name.

 

 

Workbook.ActiveChart Property

 

This property returns the currently Active Chart, which can be a chart sheet or an embedded chart. Syntax: WorkbookObject.ActiveChart. If there is no active chart, this property returns Nothing. Use the Activate method of the 'Chart object' or 'ChartObject object' to activate a chart sheet or embedded chart respectively. A chart sheet is active if selected by user or activated using the Activate method. An embedded chart is active if selected by the user or the ChartObject object is activated using the Activate method. See below example.

 

 

Example 21 - Illustrate ActiveChart property & Activate method.

 

Sub ActiveChart()
'illustrate ActiveChart property

 

'activate chart sheet named "Chart1":
ActiveWorkbook.Sheets("Chart1").activate

'alternatively, to activate chart sheet one:
'ActiveWorkbook.Charts(1).activate

'returns active chart - "Chart1"
MsgBox ActiveWorkbook.ActiveChart.Name

'activate embedded chart named "Chart 13" in "Sheet2", using the Activate method of the 'ChartObject object':
ActiveWorkbook.Sheets("Sheet2").ChartObjects("Chart 13").activate

'alternatively, to activate ChartObject one in "Sheet2":
'ActiveWorkbook.Sheets("Sheet2").ChartObjects(1).activate

'returns active chart - "Sheet2 Chart 13"
MsgBox ActiveWorkbook.ActiveChart.Name

 

'add a title to the active chart (embedded chart named "Chart 13" in "Sheet2"):

With ActiveWorkbook.ActiveChart

.HasTitle = True

.ChartTitle.Text = "EmbChart1"

End With


'returns title text of active chart - "EmbChart1"

MsgBox ActiveWorkbook.ActiveChart.ChartTitle.Text


End Sub

 

 

 

Workbook.FileFormat Property

 

This property returns the file format (value as per XlFileFormat enumeration list) of the workbook. Syntax: WorkbookObject.FileFormat. This property is read-only, and you can specify or set the file format while saving an existing or new workbook using the Workbook.SaveAs Method (discussed above).

 

 

Workbook.Name Property

 

This property returns the name of a workbook (string value). Syntax: WorkbookObject.Name. This property is read-only and you cannot change a workbook name. You can however use the Workbook.SaveAs Method to: (i) save an existing workbook (in a separate file) with a new name; or (ii) save a new workbook with a name; as discussed above.

 

 

Sub WorkbookName()
'return the names of all open workbooks

 

Dim i As Integer, count As Integer
'returns the number of workbooks in the collection:
count = Workbooks.count

'return names of all workbooks:

For i = 1 To count

MsgBox Workbooks(i).Name

Next i


End Sub

 

 

 

Workbook.Password Property

 

Use this property to set or return a password for opening a workbook. It is a read/write property. Syntax: WorkbookObject.Password. See below example which shows how to set password to open a workbook and change & delete an existing password.

 

 

Example 22 - Set workbook Password to open; Change existing workbook Password; Delete existing workbook Password.

 

Sub WorkbookPswd()
'set workbook password to open; change existing workbook password; delete existing workbook password

 

'this will not display the "replace file" prompt while using the SaveAs method:
Application.DisplayAlerts = False
Dim strPath As String, strName As String, strPswd As String, strNewPswd As String

'specify full name (ie. path & name) of existing file to open:
strPath = "C:\MyDocuments\Folder1"
strName = "NewBook.xlsx"

'specify password(s) to open workbook:
strPswd = "123"
strNewPswd = "abc"

'---------------------
'open workbook without password:
Workbooks.Open fileName:=(strPath & "\" & strName)
'set the first workbook password:
ActiveWorkbook.Password = strPswd

'close workbook after saving changes (ie. password):
ActiveWorkbook.Close SaveChanges:=True

'---------------------
'open workbook with password, updating external links or references:
Workbooks.Open fileName:=(strPath & "\" & strName), UpdateLinks:=3, Password:=strPswd

'will replace the existing file in the folder (file name is the same using SaveAs method) without displaying a prompt (Application.DisplayAlerts = False)
ActiveWorkbook.SaveAs fileName:=(strPath & "\" & strName), Password:=strNewPswd
'close workbook after saving changes (ie. new password):
ActiveWorkbook.Close SaveChanges:=True

'---------------------
'open workbook with password, updating external links or references:
Workbooks.Open fileName:=(strPath & "\" & strName), UpdateLinks:=3, Password:=strNewPswd

'will replace the existing file in the folder (file name is the same using SaveAs method) without displaying a prompt (Application.DisplayAlerts = False):
ActiveWorkbook.SaveAs fileName:=(strPath & "\" & strName), Password:=""
'close workbook after saving changes (ie. removing password):
ActiveWorkbook.Close SaveChanges:=True

'---------------------
Application.DisplayAlerts = True


End Sub

 

 

 

Workbook.Path Property

 

Use this property to return the full workbook path (string value). Syntax: WorkbookObject.Path. Refer example 22 wherein it is used.