Class Workbook (Excel VBA)
The class Workbook represents a Microsoft Excel workbook.
The main procedures of class Workbook are Activate, Close, Workbooks.Add, Workbooks.Close, Workbooks.Open, Workbooks.OpenDatabase, Workbooks.OpenText and Workbooks.OpenXML
Set
To use a Workbook class variable it first needs to be instantiated, for example
Dim wb As Workbook
Set wb = Application.Workbooks.Add()
The following procedures can be used to set variables of type Workbook: Application.ActiveWorkbook, Application.ThisWorkbook, ProtectedViewWindow.Edit, ProtectedViewWindow.Workbook, RecentFile.Open, Sheets.Parent, Sheets.Parent, SlicerCaches.Parent, Workbooks.Item, Workbooks.Add, Workbooks.Open, Workbooks.OpenDatabase, Workbooks.OpenXML, Worksheet.Parent and Application.Workbooks
For Each
Here is an example of processing the Workbook items in a collection.
Dim wbWorkbook As Workbook
For Each wbWorkbook In Application.Workbooks
Next wbWorkbook
Themes
Some procedures in this class have been grouped together in themes and are described on separate theme pages
CheckIn/Out with its procedures CheckIn and Workbooks.CheckOut
Name with its procedures CodeName, FullName and FullNameURLEncoded
Display/Show with its procedures DisplayDrawingObjects, DisplayInkComments, ShowConflictHistory, ShowPivotChartActiveFields and ShowPivotTableFieldList
Enable with its procedures EnableAutoRecover and EnableConnections
Import/Export with its procedures ExportAsFixedFormat, XmlImport and XmlImportXml
Password with its procedures HasPassword, Password, PasswordEncryptionAlgorithm, PasswordEncryptionFileProperties, PasswordEncryptionKeyLength, PasswordEncryptionProvider, SetPasswordEncryptionOptions and WritePassword
Print with its procedures PersonalViewPrintSettings, PrintOut, PrintPreview and WebPagePreview
Protect with its procedures Protect and Unprotect
Save with its procedures Save and SaveAs
Add with its procedures Workbooks.Add, Workbooks.Open, Workbooks.OpenDatabase and Workbooks.OpenText
Methods
These are the main methods of the Workbook class
Activate - Activates the first window associated with the workbook.
Workbooks("BOOK4.XLS").Activate
Close - Closes the object.
Workbooks("BOOK1.XLS").Close SaveChanges:=False
Workbooks.Close - Closes the object.
Workbooks.Close
Workbooks.OpenXML - Opens an XML data file. Returns a Workbook object.
Sub UseOpenXML()
Application.Workbooks.OpenXML _
Filename:="Customers.xml", _
LoadOption:=xlXmlLoadImportToList
End Sub
Other Methods
AcceptAllChanges - Accepts all changes in the specified shared workbook.
ActiveWorkbook.AcceptAllChanges
BreakLink - Converts formulas linked to other Microsoft Excel sources or OLE sources to values.
Sub UseBreakLink()
Dim astrLinks As Variant
' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
' Break the first link in the active workbook.
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks
End Sub
ChangeFileAccess - Changes the access permissions for the workbook. This may require an updated version to be loaded from the disk.
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
ChangeLink - Changes a link from one document to another.
ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
"c:\excel\book2.xls", xlExcelLinks
CheckInWithVersion - Saves a workbook to a server from a local computer, and sets the local workbook to read-only so that it cannot be edited locally.
Private Sub WorkbookCheckIn()
If ThisWorkbook.CanCheckIn Then
ThisWorkbook.CheckInWithVersion True, "Changed sheet 1 and 2 etc...", True, XlCheckInVersionType.xlCheckInMinorVersion
Else
Msgbox "This workbook cannot be checked in"
End If
End Sub
FollowHyperlink - Displays a cached document if it has already been downloaded. Otherwise, this method resolves the hyperlink, downloads the target document, and displays the document in the appropriate application.
ActiveWorkbook.FollowHyperlink Address:="https://example.microsoft.com"
HighlightChangesOptions - Controls how changes are shown in a shared workbook.
With ActiveWorkbook
.HighlightChangesOptions _
When:=xlSinceMyLastSave, _
Who:="Everyone"
.ListChangesOnNewSheet = True
End With
LinkInfo - Returns the link date and update status.
If ActiveWorkbook.LinkInfo( _
"Word.Document|Document1!'!DDE_LINK1", xlUpdateState, _
xlOLELinks) = 1 Then
MsgBox "Link updates automatically"
End If
LinkSources - Returns an array of links in the workbook. The names in the array are the names of the linked documents, editions, or DDE or OLE servers. Returns Empty if there are no links.
aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If
ProtectSharing - Saves the workbook and protects it for sharing.
Sub ProtectWorkbook()
Dim wbAWB As Workbook
Dim strPwd As String
Dim strSharePwd As String
Set wbAWB = Application.ActiveWorkbook
strPwd = InputBox("Enter password for the file")
strSharePwd = InputBox("Enter password for sharing")
wbAWB.ProtectSharing Password:=strPwd, _
SharingPassword:=strSharePwd
End Sub
PurgeChangeHistoryNow - Removes entries from the change log for the specified workbook.
ActiveWorkbook.PurgeChangeHistoryNow Days:=1
RejectAllChanges - Rejects all changes in the specified shared workbook.
ActiveWorkbook.RejectAllChanges
SaveAsXMLData - Exports the data that has been mapped to the specified XML schema map to an XML data file.
Sub ExportAsXMLData()
Dim objMapToExport As XmlMap
Set objMapToExport = ActiveWorkbook.XmlMaps("Customer")
If objMapToExport.IsExportable Then
ActiveWorkbook.SaveAsXMLData "Customer Data.xml", objMapToExport
Else
MsgBox "Cannot use " & objMapToExport.Name & _
"to export the contents of the worksheet to XML data."
End If
End Sub
SendFaxOverInternet - Sends a worksheet as a fax to the specified recipients.
ActiveWorkbook.SendFaxOverInternet _
"14255550101@consolidatedmessenger.com", _
"For your review", True
SendForReview - Sends a workbook in an email message for review to the specified recipients.
Sub WebReview()
ActiveWorkbook.SendForReview _
Recipients:="someone@example.com; amy jones; lewjudy", _
Subject:="Please review this document.", _
ShowMessage:=False, _
IncludeAttachment:=True
End Sub
SendMail - Sends the workbook by using the installed mail system.
ActiveWorkbook.SendMail recipients:="Jean Selva"
SetLinkOnData - Sets the name of a procedure that runs whenever a DDE link is updated.
ActiveWorkbook.SetLinkOnData _
"WinWord|'C:\MSGFILE.DOC'!DDE_LINK1", _
"my_Link_Update_Macro"
UpdateLink - Updates a Microsoft Excel, DDE, or OLE link (or links).
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Properties
AccuracyVersion specifies whether certain worksheet functions use the latest accuracy algorithms to calculate their results.
ActiveWorkbook.AccuracyVersion = 1
ActiveChart returns a Chart object that represents the active chart (either an embedded chart or a chart sheet). An embedded chart is considered active when it's either selected or activated. When no chart is active, this property returns Nothing.
ActiveChart.HasLegend = True
ActiveSheet returns a Worksheet worksheet that represents the active sheet (the sheet on top) in the active workbook or specified workbook. Returns Nothing if no sheet is active.
MsgBox "The name of the active sheet is " & ActiveSheet.Name
ActiveSlicer returns an object that represents the active slicer in the active workbook or in the specified workbook. Returns Nothing if no slicer is active.
AutoSaveOn true if the edits in the workbook are automatically saved.
Sub UseAutoSaveOn()
MsgBox "This workbook is being saved automatically: " & ActiveWorkbook.AutoSaveOn
End Sub
AutoUpdateFrequency returns or sets the number of minutes between automatic updates to the shared workbook.
ActiveWorkbook.AutoUpdateFrequency = 5
AutoUpdateSaveChanges true if current changes to the shared workbook are posted to other users whenever the workbook is automatically updated. False if changes aren't posted (this workbook is still synchronized with changes made by other users). The default value is True.
ActiveWorkbook.AutoUpdateSaveChanges = True
BuiltinDocumentProperties returns a DocumentProperties collection that represents all the built-in document properties for the specified workbook. Read-only.
BuiltinDocumentProperties.Item(1)
BuiltinDocumentProperties(1)
CalculationVersion returns the information about the version of Excel that the workbook was last fully recalculated by.
CaseSensitive true if the workbook distinguishes between uppercase and lowercase when comparing content.
ChangeHistoryDuration returns or sets the number of days shown in the shared workbook's change history.
With ActiveWorkbook
If .KeepChangeHistory Then
.ChangeHistoryDuration = 7
End If
End With
ChartDataPointTrack true causes all charts in the current document to track the actual data point to which it's attached. False reverts back to tracking the index of the data point.
Charts returns a Sheets collection that represents all the chart sheets in the specified workbook.
ActiveWorkbook.Charts.Delete
CheckCompatibility controls whether or not the compatibility checker is run automatically when the workbook is saved.
Colors returns or sets colors in the palette for the workbook. The palette has 56 entries, each represented by an RGB value.
ActiveWorkbook.Colors = Workbooks("BOOK2.XLS").Colors
CommandBars returns a CommandBars object that represents the Microsoft Excel command bars.
For Each bar In Application.CommandBars
If Not bar.BuiltIn And Not bar.Visible Then bar.Delete
Next
ConflictResolution returns or sets the way conflicts are to be resolved whenever a shared workbook is updated.
ActiveWorkbook.ConflictResolution = xlLocalSessionChanges
Connections establishes a connection between the workbook and an ODBC or an OLEDB data source and refreshes the data without prompting the user.
ActiveWorkbook.Connections(1).ODBCConnection.Refresh
ActiveWorkbook.Connections(1).OLEDBConnection.Refresh
ConnectionsDisabled disables the external connections or links in the workbook.
Private Sub Workbook_Open()
ThisWorkbook.ConnectionsDisabled
End Sub
Container returns the object that represents the container application for the specified OLE object. Read-only object.
ContentTypeProperties returns a MetaProperties collection that describes the metadata stored in the workbook.
CreateBackup true if a backup file is created when this file is saved.
If ActiveWorkbook.CreateBackup = True Then
MsgBox "Remember, there is a backup copy of this workbook"
End If
CustomDocumentProperties returns or sets a DocumentProperties collection that represents all the custom document properties for the specified workbook.
CustomDocumentProperties.Item("Complete")
CustomDocumentProperties("Complete")
CustomViews returns a CustomViews collection that represents all the custom views for the workbook.
ActiveWorkbook.CustomViews.Add "Summary", True, True
CustomXMLParts returns a CustomXMLParts collection that represents the custom XML in the XML data store.
Date1904 true if the workbook uses the 1904 date system.
ActiveWorkbook.Date1904 = True
DefaultPivotTableStyle specifies the table style from the TableStyles collection that is used as the default style for PivotTables. Read/write.
DefaultSlicerStyle specifies the style from the TableStyles tablestyles that is used as the default style for slicers. Read/write.
DefaultTableStyle specifies the table style from the TableStyles collection that is used as the default table style. Read/write Variant.
DefaultTimelineStyle the name of the default slicer style of the workbook.
DocumentInspectors returns a DocumentInspectors collection that represents the Document Inspector modules for the specified workbook.
DocumentLibraryVersions returns a DocumentLibraryVersions collection that represents the collection of versions of a shared workbook that has versioning enabled and that is stored in a document library on a server.
DoNotPromptForConvert returns or sets if the user should be prompted to convert the workbook if the workbook contains features that are not supported by versions of Excel earlier than Excel 2007.
EncryptionProvider returns a String specifying the name of the algorithm encryption provider that Microsoft Excel uses when encrypting documents.
EnvelopeVisible true if the email composition header and the envelope toolbar are both visible.
If Workbooks(1).EnvelopeVisible = True Then
strSubject = "Please read: Review immediately"
End If
Excel8CompatibilityMode provides developers with a way to check if the workbook is in compatibility mode.
FileFormat returns the file format and/or type of the workbook.
If ActiveWorkbook.FileFormat = xlExcel9795 Then
ActiveWorkbook.SaveAs fileFormat:=xlExcel12
End If
Final returns or sets a Boolean that indicates whether a workbook is final.
ForceFullCalculation returns or sets the specified workbook to forced calculation mode.
ActiveWorkbook.ForceFullCalculation = True
HasVBProject returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project.
HighlightChangesOnScreen true if changes to the shared workbook are highlighted on-screen.
ThisWorkbook.HighlightChangesOnScreen
IconSets this property is used to filter data in a workbook based on a cell icon from the IconSets collection.
Selection.AutoFilter Field:=1, Criteria1:=ActiveWorkbook.IconSets(xl3Arrows).Item(1), Operator:=xlFilterIcon
InactiveListBorderVisible a Boolean value that specifies whether list borders are visible when a list is not active. Returns True if the border is visible.
Sub HideListBorders()
ActiveWorkbook.InactiveListBorderVisible = False
End Sub
IsAddin true if the workbook is running as an add-in.
If ThisWorkbook.IsAddin Then
' this code runs when the workbook is an add-in
End If
IsInplace true if the specified workbook is being edited in place. False if the workbook has been opened in Microsoft Excel for editing.
Private Sub Workbook_Open()
If ThisWorkbook.IsInPlace = True Then
MsgBox "Editing in place"
Else
MsgBox "Editing in Microsoft Excel"
End If
End Sub
KeepChangeHistory true if change tracking is enabled for the shared workbook.
With ActiveWorkbook
If .KeepChangeHistory Then
.ChangeHistoryDuration = 7
End If
End With
ListChangesOnNewSheet true if changes to the shared workbook are shown on a separate worksheet.
With ActiveWorkbook
.HighlightChangesOptions _
When:=xlSinceMyLastSave, _
Who:="Everyone"
.ListChangesOnNewSheet = True
End With
Model returns the top-level Model object that is the one data model for the workbook.
MultiUserEditing true if the workbook is open as a shared list.
If Not ActiveWorkbook.MultiUserEditing Then
ActiveWorkbook.SaveAs fileName:=ActiveWorkbook.FullName, _
accessMode:=xlShared
End If
Name returns a String value that represents the name of the object.
Names returns a Names collection that represents all the names in the specified workbook (including all worksheet-specific names).
ActiveWorkbook.Names.Add Name:="myName", RefersToR1C1:= _
"=Sheet1!R1C1"
Parent returns the parent object for the specified object. Read-only.
Path returns a String that represents the complete path to the workbook/file that this workbook object represents.
Permission returns a Permission object that represents the permission settings in the specified workbook.
Dim objPermission As Permission
Set objPermission = ActiveWorkbook.Permission
PersonalViewListSettings true if filter and sort settings for lists are included in the user's personal view of the shared workbook.
With Workbooks(2)
.PersonalViewListSettings = False
.PersonalViewPrintSettings = False
End With
PivotTables returns an pivottables that represents a collection of all the PivotTable reports on a worksheet. Read-only.
PrecisionAsDisplayed true if calculations in this workbook are done by using only the precision of the numbers as they're displayed.
ActiveWorkbook.PrecisionAsDisplayed = True
ProtectStructure true if the order of the sheets in the workbook is protected.
If ActiveWorkbook.ProtectStructure = True Then
MsgBox "Remember, you cannot delete, add, or change " & _
Chr(13) & _
"the location of any sheets in this workbook."
End If
ProtectWindows true if the windows of the workbook are protected.
If ActiveWorkbook.ProtectWindows = True Then
MsgBox "Remember, you cannot rearrange any" & _
" window in this workbook."
End If
PublishObjects returns the PublishObjects collection.
Set objPObjs = ActiveWorkbook.PublishObjects
For Each objPO in objPObjs
If objPO.HtmlType = xlHTMLStatic Then
objPO.Publish
End If
Next objPO
Queries returns a Queries collection that represents all the Get & Transform queries in the specified Workbook. Starting from Excel 2016, Get & Transform features enable you to connect, combine, and shape data from a variety of sources to meet your analysis needs.
ReadOnly returns True if the object has been opened as read-only.
If ActiveWorkbook.ReadOnly Then
ActiveWorkbook.SaveAs fileName:="NEWFILE.XLS"
End If
ReadOnlyRecommended true if the workbook was saved as read-only recommended.
If ActiveWorkbook.ReadOnlyRecommended = True Then
MsgBox "This workbook is saved as read-only recommended"
End If
RemovePersonalInformation true if personal information can be removed from the specified workbook. The default value is False.
Sub UsePersonalInformation()
Dim wkbOne As Workbook
Set wkbOne = Application.ActiveWorkbook
' Determine settings and notify user.
If wkbOne.RemovePersonalInformation = True Then
MsgBox "Personal information can be removed."
Else
MsgBox "Personal information cannot be removed."
End If
End Sub
Research returns a Research object that represents the research service for a workbook.
RevisionNumber returns the number of times the workbook has been saved while open as a shared list. If the workbook is open in exclusive mode, this property returns 0 (zero).
If ActiveWorkbook.RevisionNumber = 0 Then
ActiveWorkbook.SaveAs _
filename:=ActiveWorkbook.FullName, _
accessMode:=xlShared, _
conflictResolution:= _
xlOtherSessionChanges
End If
Saved true if no changes have been made to the specified workbook since it was last saved.
If Not ActiveWorkbook.Saved Then
MsgBox "This workbook contains unsaved changes."
End If
SaveLinkValues true if Microsoft Excel saves external link values with the workbook.
ActiveWorkbook.SaveLinkValues = True
ServerPolicy returns a ServerPolicy object that represents a policy specified for a workbook stored on a server running SharePoint Server 2007 or later.
ServerViewableItems allows a developer to interact with the list of published objects in the workbook that are shown on the server.
Sheets returns a Sheets collection that represents all the sheets in the specified workbook.
Set newSheet = Sheets.Add(Type:=xlWorksheet)
For i = 1 To Sheets.Count
newSheet.Cells(i, 1).Value = Sheets(i).Name
Next i
Signatures returns the digital signatures for a workbook.
Sub AddSignature()
ActiveWorkbook.Signatures.AddSignatureLine
End Sub
SlicerCaches returns the SlicerCaches object associated with the workbook.
SmartDocument returns a SmartDocument object that represents the settings for a smart document solution.
Styles returns a Styles collection that represents all the styles in the specified workbook.
ActiveWorkbook.Styles("Stock Quote Style").Delete
TableStyles returns a TableStyles collection object for the current workbook that refers to the styles used in the current workbook.
TemplateRemoveExtData true if external data references are removed when the workbook is saved as a template.
With ThisWorkbook
.TemplateRemoveExtData = True
.SaveAs "current", xlTemplate
.TemplateRemoveExtData = False
End With
Theme returns the theme applied to the current workbook.
UpdateLinks returns or sets an XlUpdateLink constant indicating a workbook's setting for updating embedded OLE links.
Sub UseUpdateLinks()
Dim wkbOne As Workbook
Set wkbOne = Application.Workbooks(1)
Select Case wkbOne.UpdateLinks
Case xlUpdateLinksAlways
MsgBox "Links will always be updated " & _
"for the specified workbook."
Case xlUpdateLinksNever
MsgBox "Links will never be updated " & _
"for the specified workbook."
Case xlUpdateLinksUserSetting
MsgBox "Links will update according " & _
"to user settting for the specified workbook."
End Select
End Sub
UpdateRemoteReferences true if Microsoft Excel updates remote references in the workbook.
ActiveWorkbook.UpdateRemoteReferences = True
UserStatus returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list.
users = ActiveWorkbook.UserStatus
With Workbooks.Add.Sheets(1)
For row = 1 To UBound(users, 1)
.Cells(row, 1) = users(row, 1)
.Cells(row, 2) = users(row, 2)
Select Case users(row, 3)
Case 1
.Cells(row, 3).Value = "Exclusive"
Case 2
.Cells(row, 3).Value = "Shared"
End Select
Next
End With
UseWholeCellCriteria true if the workbook uses search patterns that match the entire content of a cell.
UseWildcards true if the workbook enables wildcards for character string comparisons and searching.
VBASigned true if the Visual Basic for Applications project for the specified workbook has been digitally signed.
Workbooks.Open FileName:="c:\My Documents\mybook.xls", _
ReadOnly:=False
If Workbook.VBASigned = False Then
MsgBox "Warning! The project " _ &
"has not been digitally signed." _ &
, vbCritical, "Digital Signature Warning"
End If
VBProject returns a VBProject object that represents the Visual Basic project in the specified workbook.
ThisWorkbook.VBProject.Name = "TestProject"
WebOptions returns the WebOptions collection, which contains workbook-level attributes used by Microsoft Excel when you save a document as a webpage or open a webpage.
Set objWO = Workbooks(1).WebOptions
objWO.RelyOnCSS = True
objWO.Encoding = msoEncodingWestern
Windows returns a Windows collection that represents all the windows in the specified workbook.
ActiveWorkbook.Windows(1).Caption = "Consolidated Balance Sheet"
ActiveWorkbook.Windows("Consolidated Balance Sheet") _
.ActiveSheet.Calculate
WorkIdentity
Worksheets returns a Sheets collection that represents all the worksheets in the specified workbook.
MsgBox Worksheets("Sheet1").Range("A1").Value
WriteReserved true if the workbook is write-reserved.
With ActiveWorkbook
If .WriteReserved = True Then
MsgBox "Please contact " & .WriteReservedBy & Chr(13) & _
" if you need to insert data in this workbook."
End If
End With
WriteReservedBy returns the name of the user who currently has write permission for the workbook.
With ActiveWorkbook
If .WriteReserved = True Then
MsgBox "Please contact " & .WriteReservedBy & Chr(13) & _
" if you need to insert data in this workbook."
End If
End With
XmlMaps returns an XmlMaps collection that represents the schema maps that have been added to the specified workbook.
XmlNamespaces returns an XmlNamespaces collection that represents the XML namespaces contained in the specified workbook.
Workbooks.Count returns a Long value that represents the number of objects in the collection.
Workbooks.Item returns a single Workbook object from the collection.
Set wb = Workbooks.Item("myaddin.xla")
Workbooks.Parent returns the parent object for the specified object. Read-only.