The Workbooks object is a collection of all the Workbook objects currently open in the Excel application.
1. How to create a new workbook using code
The Workbooks.add method returns a Workbook object representing the newly created workbook.
Add(Template)
Template: Optional parameter. Specifies the template based on which the workbook is created.
If the template is specified as an existing Excel file, the new workbook will be created based on the specified Excel file. If the template parameter is a constant, the new workbook will contain a worksheet of the specified type. If this parameter is omitted, a new workbook containing a certain number of blank worksheets will be created (the number can be specified by Application.SheetsInNewWorkbook).
Note that the newly created workbook automatically becomes the current active workbook.
Sub Samp()
Dim wb As Workbook
Workbooks.Add
’Create a new workbook
Workbooks.Add Template:=xlWBATChart
’Create a new workbook, specifying that the type of worksheet included in the workbook is a chart
Set wb = Workbooks.Add(Template:=xlWBATExcel4IntlMacroSheet)
’Create a new workbook, specifying that the type of worksheet included in the workbook is a macro sheet
’wb refers to the newly created workbook object
Application.SheetsInNewWorkbook = 5
’Excel automatically inserts the number of worksheets into the new workbook
Workbooks.Add
’Create a new workbook, containing5 blank worksheets
Application.SheetsInNewWorkbook = 3
End Sub
2. How to use code to close all open Workbook objects
Workbooks.Close
If any open workbook has unsaved changes, Microsoft Excel will display a dialog box asking whether to save changes.
Before using this method, make sure to save your data first; if you accidentally close all files, you cannot claim compensation for lost data.
Workbooks.close
3. How to use code to open a workbook
The Workbooks.open method returns a Workbook object representing the opened workbook.
Note: The opened workbook becomes the current active workbook.
Sub Samp2()
Workbooks.Open Filename:=”c:\1.xlsm”
’Open the c:\1.xlsm file
’Note: When reopening an already opened EXCEL file, there will be
End Sub
4. How to check the number of currently open workbooks in Excel using code
Workbooks.count represents the number of objects in the collection.
Sub Samp3()
MsgBox Workbooks.Count
’Display the number of open workbooks in the current EXCEL application via a message box
End Sub
For other properties and methods, please refer to the VBA help. Additionally, practice coding more, and you will naturally become proficient.
OpenDatabase returns a Workbook object that represents a database.
OpenText loads a text file and processes it as a new workbook containing a single worksheet, then places the processed text file data in this worksheet.
OpenXML opens an XML data file. Returns a Workbook object.
Example Custom Function
Sub Test()
Dim strWorkbookname As String
strWorkbookname = “Workbook12″
MsgBox “Workbook ” & strWorkbookname & ” is open:“& WorkbookIsOpen1(strWorkbookname)
MsgBox WorkbookIsOpen1(“”)
MsgBox WorkbookIsOpen2(“”)
End Sub
Function WorkbookIsOpen1(strWorkbookname As String) As Boolean
’Check if the specified workbook file is open
’No full path check, not perfect
’Everyone can improve it themselves.
’Method 1
Dim wb As Workbook
Dim str As String
str = LCase(strWorkbookname)
’Define a string variable, converting uppercase letters in the passed parameter to lowercase and assigning it to str
For Each wb In Workbooks
If wb.Name = strWorkbookname Then
WorkbookIsOpen1 = True
Exit Function
End If
Next
End Function
Function WorkbookIsOpen2(strWorkbookname As String) As Boolean
’Check if the specified workbook file is open
’No full path check, not perfect
’Everyone can improve it themselves.
’Method 2: Use error handling
On Error Resume Next
If Len(Workbooks(strWorkbookname).Name) = 0 Then
WorkbookIsOpen2 = False
’The above line sets the function return value, which can be omitted
’Boolean type variable defaults to False
Else
WorkbookIsOpen2 = True
End If
End Function
Workbook object is the Workbook object currently open in the Excel application.
When using Excel software, you must open a workbook, operate on worksheets, cells, charts, pivot tables, VBA projects, and so on, all of which are directly or indirectly included in the workbook object.
How to reference a workbook object?
The Workbooks.open method creates a new reference.
Example:
Sub Samp1()
Dim wb As Workbook
Set wb = Workbooks.Add
End Sub
Returns an object from the workbook collection
Workbooks.item(index)
SinceItem is the default property, this Item can be omitted.
Index can be the index number of a workbook object in the workbook collection
It can also be the name of a workbook object (filename, e.g., Workbook1.xlsm)
Sub Samp2()
Dim wb As Workbook
Set wb = Workbooks(1)
’Set wb = Workbooks(“Workbook1.xlsm”)
End Sub
When returning an object from a collection, be sure to check whether the provided index number or name is valid, otherwise, an out-of-bounds error may occur.
Once the object reference is set, it involves specific properties and methods, starting with the properties of the workbook object.
Workbook.Activesheet property
Returns an object representing the active worksheet in the active workbook or the specified window or workbook (the top worksheet). If there is no active worksheet, it returns Nothing.
If no workbook object is specified, it returns the active worksheet in the active workbook.
Example: MsgBox “The current active worksheet is ” &ActiveSheet.Name
’Display the name of the active worksheet in the current active workbook via a message box
Workbook.CodeName property
Returns the code name of the object.String type, read-only.
The value displayed in the cell next to “(Name)” in the “Properties” window is the code name of the selected object. This value can be changed during design but cannot be modified at runtime.
For an expression that returns a specified object, the expression can use the object’s code name.
For example, if the code name of the first worksheet is Sheet1, then the following expressions are equivalent.
Worksheets(1).Range(“a1”)
Sheet1.Range(“a1”)
The name of the worksheet can differ from its code name. When creating a worksheet, its name and code name are the same, but changing the worksheet’s name and code name does not affect each other.
Workbook.FullName property
Returns the name of the specified workbook object (as a string), including its disk path.String type, read-only.
Example: MsgBox ActiveWorkbook.FullName
’Display the full filename of the current active workbook via a message box
Workbook.Path property:
Returns a String value representing the full path of the application, excluding the trailing separator and application name.
Example: MsgBox ActiveWorkbook.path
’Display the full path of the current active workbook via a message box
Note: When the newly created workbook is unsaved, this property returns a string of length 0 (an empty string)
Workbook.Saved property Boolean type, read/write.
If the specified workbook has not been changed since the last save, the property value is True.
If the specified workbook has been modified but not saved, the property value is False.
If you want to close a modified workbook without saving it or without prompting to save, you can set this property to True.
Example: If Not ActiveWorkbook.Saved Then
MsgBox “The workbook has been modified and not saved, please remember to save“
End If
Workbook.Password property
String type, read/write. Returns or sets the password required to open the specified workbook.
Example: Dim wb As Workbook
Set wb = Workbooks.Open(“d:\Password.xlsm”)
wb.Password = InputBox(“Enter Password”)
wb.Close SaveChanges:=True
Note: When the workbook contains a macro sheet, the Password property cannot be set.
In a password-protected workbook, macro sheets cannot be inserted either.
When obtaining the Password property via code, a string of * will be returned.
Let’s talk about the tables in the workbook. Prepare the code: Sheets.Add Type:=xlWorksheet ’Insert a worksheet Sheets.Add Type:=xlChart ’Insert a chart Sheets.Add Type:=xlExcel4MacroSheet ’Insert an Excel 4 macro worksheet Sheets.Add Type:=xlExcel4IntlMacroSheet ’Insert a worksheet Excel 4 macro sheet Workbook.Sheets property Sheets object, read-only. Returns a Sheets collection representing all the worksheets in the specified workbook. Using this property without an object qualifier is equivalent to using ActiveWorkbook.Sheets Dim sht As Object Dim strMsg As String strMsg = “The current workbook has the following tables” & vbCrLf For Each sht In Sheets strMsg = strMsg & sht.Name & vbCrLf Next MsgBox prompt:=strMsg, Buttons:=vbInformation + vbOKOnly ’The dialog box displays the current chart worksheets, worksheets, and macro sheets Workbook.Charts property Returns a Sheets collection representing all the chart worksheets in the specified workbook. Dim sht As Object Dim strMsg As String strMsg = “The current workbook has the following chart worksheets” & vbCrLf For Each sht In Charts strMsg = strMsg & sht.Name & vbCrLf Next MsgBox prompt:=strMsg, Buttons:=vbInformation + vbOKOnly ’The dialog box displays the names of the chart worksheets in the current workbook Workbook.Excel4IntlMacroSheets property Sheets collection, read-only. Returns a Sheets collection representing all the Microsoft Excel 4.0 international macro sheets in the specified workbook. Dim sht As Object Dim strMsg As String strMsg = “The current workbook has the following Excel 4.0 international macro sheets” & vbCrLf For Each sht In Excel4IntlMacroSheets strMsg = strMsg & sht.Name & vbCrLf Next MsgBox prompt:=strMsg, Buttons:=vbInformation + vbOKOnly ’The dialog box displays the Excel 4.0 international macro sheets in the current workbook Workbook.Excel4MacroSheets property Sheets collection, read-only. Returns a Sheets collection representing all the Excel 4.0 macro sheets in the specified workbook. Dim sht As Object Dim strMsg As String strMsg = “The current workbook has the following Excel 4.0 macro sheets” & vbCrLf For Each sht In Excel4MacroSheets strMsg = strMsg & sht.Name & vbCrLf Next MsgBox prompt:=strMsg, Buttons:=vbInformation + vbOKOnly ’The dialog box displays the Excel 4.0 macro sheets in the current workbook Workbook.Worksheets property Sheets collection, read-only. Returns a Sheets collection representing all the worksheets in the specified workbook. Using this property without an object qualifier will return all worksheets in the active workbook. This property does not return macro sheets; use Excel4MacroSheets or Excel4IntlMacroSheets properties to return these sheets. Dim sht As Object Dim strMsg As String strMsg = “The current workbook has the following worksheets” & vbCrLf For Each sht In Worksheets strMsg = strMsg & sht.Name & vbCrLf Next MsgBox prompt:=strMsg, Buttons:=vbInformation + vbOKOnly ’The dialog box displays the worksheets in the current workbook There are basically four types of sheets; here’s a code to clear macro sheets and charts. Of course, if you find it troublesome, you can simply close the workbook without saving. Dim sht As Object Application.DisplayAlerts = False ’Since deleting a worksheet requires a confirmation dialog ’By setting Application.DisplayAlerts to False ’During macro runtimeto disable prompt and warning messages ’When a user response is needed, Microsoft Excel will choosethe default response. ’If this property is set to False, Microsoft Excel will reset it to True after the code execution ends, unless cross-process code is running. Application.ScreenUpdating = False ’Disabling screen updating can speed up macro execution.Thus, the execution process of the macro will not be visible, but the execution speed of the macro will be faster. ’After the macro ends,remember to set the ScreenUpdating property back to True. For Each sht In Charts sht.Delete Next ’Iterate to delete chart worksheets For Each sht In Excel4MacroSheets sht.Delete Next ’Iterate to delete 4.0 macro sheets For Each sht In Excel4IntlMacroSheets sht.Delete Next ’Iterate to delete 4.0 international macro sheets Application.DisplayAlerts = True Application.ScreenUpdating = True ’Restore settings MsgBox “Macro sheets and charts deletion completed”
Continuing to explore the properties of the workbook. Workbook.FileFormat property Returns a value of XlFileFormat type, read-only. Returns the file format and/or type of the workbook. There are many specific types available; when writing actual code, you can look them up in F1, and you can also try learning by recording macros. Some types in XlFileFormat may not be available, depending on the selected or installed language support (e.g., US English). Additionally, regarding versions, you may also need to pay attention. Workbook.ReadOnly property Boolean type, read-only. Returns True if the object is opened in read-only mode. If ActiveWorkbook.ReadOnly Then ActiveWorkbook.SaveAs Filename:=”New-” & ActiveWorkbook.Name, FileFormat:=ActiveWorkbook.FileFormat End If ’If the active workbook is opened in read-only, save it as New-original filename, with the same format as the current workbook. Note: In Excel 2010, if saving using the saveas method, remember to specify the file format, especially for workbooks containing code. Workbook.HasVBProject property Read-only Boolean type. Indicates whether the workbook has an attached VBA project. If ActiveWorkbook.HasVBProject Then MsgBox “The current workbook contains a VBA project” End If Workbook.HasPassword property Boolean type, read-only. Returns True if the specified workbook has an open password. ActiveWorkbook.Password = “123” ’Set open password to 123 MsgBox “Does the current active workbook have password protection?” & ActiveWorkbook.HasPassword ActiveWorkbook.Password = “” ’Set password to empty (remove password) MsgBox “Does the current active workbook have password protection?” & ActiveWorkbook.HasPassword ’Check for password protection via message box ActiveWorkbook.WritePassword = “123” ’Set write password to 123 MsgBox “Does the current active workbook have password protection?” & ActiveWorkbook.HasPassword ActiveWorkbook.WritePassword = “” ’Set write password to empty (remove write password) MsgBox “Does the current active workbook have password protection?” & ActiveWorkbook.HasPassword ’Check for password protection Workbook.WritePassword property String type, read/write. Returns or sets a String value for the write password of the workbook. ActiveWorkbook.WritePassword = “123” ’Set write password to 123 Workbook.WriteReserved property Boolean type, read-only. Checks if the specified workbook is write-protected? ActiveWorkbook.WritePassword = “123” ’Set write password to 123 MsgBox “Is the current active workbook write-protected?” & ActiveWorkbook.WriteReserved ActiveWorkbook.WritePassword = “” ’Set write password to empty (remove write password) MsgBox “Is the current active workbook write-protected?” & ActiveWorkbook.WriteReserved Workbook.Windows property Windows object, read-only. Returns a Windows collection representing all windows in the specified workbook. Using this property without an object qualifier is equivalent to using Application.Windows? MsgBox Windows(1).Caption ’Display the title of the first window in the current workbook’s window collection Workbook.Names property Names object, read-only. Returns a Names collection representing all names in the specified workbook (including all specified worksheet names). Note: Names can be hidden, and many macro viruses love to use this trick. Dim objName As Name For Each objName In Names objName.Visible = True End If ’Iterate through the names collection in the workbook, setting names to visible Workbook.RemovePersonalInformation property Boolean type, read/write. Sets whether to remove personal information from the specified workbook, with a default value of False. Many students have encountered the privacy protection dialog that pops up when saving a workbook; this is actually due to this property being set to TRUE.
“We want members, not fans!” Welcome to join<China Garment Data Analysis Association>, with the aim of“mutual assistance and common improvement”, dedicated to developing an online and offline integrated interpersonal network, sharing industry resources, discussing industry trends and development prospects. Thank you for your application! GroupQQ211210964!