Class Workbook (Excel VBA)
The class Workbook represents a Microsoft Excel workbook.
To use a Workbook class variable it first needs to be instantiated, for example
Dim wb as Workbook Set wb = ActiveWorkbook
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
Here is an example of processing the Workbook items in a collection.
Dim wb As Workbook For Each wb In Workbooks Next wb
Some procedures in this class have been grouped together in themes and are described on separate theme pages
Save with its procedures CheckIn, EnableAutoRecover, FileFormat, RemoveDocumentInformation, RemovePersonalInformation, Save and SaveAs
Name with its procedures CodeName, FullName and FullNameURLEncoded
Import/Export with its procedures ExportAsFixedFormat, XmlImport and XmlImportXml
Password/Protect with its procedures HasPassword, Password, PasswordEncryptionAlgorithm, PasswordEncryptionFileProperties, PasswordEncryptionKeyLength, PasswordEncryptionProvider, Protect, SetPasswordEncryptionOptions, Unprotect and WritePassword
Print with its procedures PersonalViewPrintSettings, PrintOut, PrintPreview and WebPagePreview
These are the main methods of the Workbook class
Activate - Activates the first window associated with the workbook.
Close - Closes the object.
Workbooks.Add - Creates a new workbook. The new workbook becomes the active workbook.
Dim wb As Workbook Set wb = Workbooks.Add()
Workbooks.Close - Closes the object.
Workbooks.Open - Opens a workbook.
Dim strFilename As String: strFilename = Dim wb As Workbook Set wb = Workbooks.Open(Filename:=strFilename)
Workbooks.OpenDatabase - Returns a Workbook object representing a database.
Dim strFilename As String: strFilename = Dim wbOpenDatabase As Workbook Set wbOpenDatabase = Workbooks.OpenDatabase(Filename:=strFilename)
Workbooks.OpenText - Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.
Dim strFilename As String: strFilename = Workbooks.OpenText Filename:=strFilename
Workbooks.OpenXML - Opens an XML data file. Returns a Workbook object.
Dim strFilename As String: strFilename = Dim wbOpenXML As Workbook Set wbOpenXML = Workbooks.OpenXML(Filename:=strFilename)
AcceptAllChanges - Accepts all changes in the specified shared workbook.
BreakLink - Converts formulas linked to other Microsoft Excel sources or OLE sources to values.
Dim strName As String: strName = ActiveWorkbook.BreakLink Name:=strName, Type:=xlLinkTypeExcelLinks
ChangeFileAccess - Changes the access permissions for the workbook. This may require an updated version to be loaded from the disk.
ChangeLink - Changes a link from one document to another.
Dim strName As String: strName = Dim strNewName As String: strNewName = ActiveWorkbook.ChangeLink Name:=strName, NewName:=strNewName
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.
ThisWorkbook.CheckInWithVersion True, "Changed sheet 1 and 2 etc...", True, XlCheckInVersionType.xlCheckInMinorVersion
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.
Dim strAddress As String: strAddress = ActiveWorkbook.FollowHyperlink Address:=strAddress
HighlightChangesOptions - Controls how changes are shown in a shared workbook.
LinkInfo - Returns the link date and update status.
Dim strName As String: strName = Dim varLinkInfo As Variant varLinkInfo = ActiveWorkbook.LinkInfo(Name:=strName, LinkInfo:=xlEditionDate)
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.
Dim arrLinkSources() As Variant arrLinkSources() = ActiveWorkbook.LinkSources()
ProtectSharing - Saves the workbook and protects it for sharing.
PurgeChangeHistoryNow - Removes entries from the change log for the specified workbook.
Dim lngDays As Long: lngDays = ActiveWorkbook.PurgeChangeHistoryNow Days:=lngDays
RejectAllChanges - Rejects all changes in the specified shared workbook.
SaveAsXMLData - Exports the data that has been mapped to the specified XML schema map to an XML data file.
Dim strFilename As String: strFilename = ActiveWorkbook.SaveAsXMLData Filename:=strFilename, Map:=
SendFaxOverInternet - Sends a worksheet as a fax to the specified recipients.
SendForReview - Sends a workbook in an email message for review to the specified recipients.
SendMail - Sends the workbook by using the installed mail system.
Dim strRecipients() As String: strRecipients() = ActiveWorkbook.SendMail Recipients:=strRecipients()
SetLinkOnData - Sets the name of a procedure that runs whenever a DDE link is updated.
Dim strName As String: strName = ActiveWorkbook.SetLinkOnData Name:=strName
UpdateLink - Updates a Microsoft Excel, DDE, or OLE link (or links).
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.
Dim chrActiveChart As Chart Set chrActiveChart = ActiveWorkbook.ActiveChart
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.
Dim wsActiveSheet As Worksheet Set wsActiveSheet = ActiveWorkbook.ActiveSheet
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.
Dim slcActiveSlicer As Slicer Set slcActiveSlicer = ActiveWorkbook.ActiveSlicer
AutoSaveOn true if the edits in the workbook are automatically saved.
ActiveWorkbook.AutoSaveOn = True
AutoUpdateFrequency returns or sets the number of minutes between automatic updates to the shared workbook.
ActiveWorkbook.AutoUpdateFrequency = 4
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.
Dim dpsBuiltinDocumentProperties As DocumentProperties dpsBuiltinDocumentProperties = ActiveWorkbook.BuiltinDocumentProperties
CalculationVersion returns the information about the version of Excel that the workbook was last fully recalculated by.
Dim lngCalculationVersion As Long lngCalculationVersion = ActiveWorkbook.CalculationVersion
CaseSensitive true if the workbook distinguishes between uppercase and lowercase when comparing content.
Dim booCaseSensitive As Boolean booCaseSensitive = ActiveWorkbook.CaseSensitive
ChangeHistoryDuration returns or sets the number of days shown in the shared workbook's change history.
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.
ActiveWorkbook.ChartDataPointTrack = True
Charts returns a Sheets collection that represents all the chart sheets in the specified workbook.
Dim shtsCharts As Sheets Set shtsCharts = ActiveWorkbook.Charts
CheckCompatibility controls whether or not the compatibility checker is run automatically when the workbook is saved.
ActiveWorkbook.CheckCompatibility = True
Colors returns or sets colors in the palette for the workbook. The palette has 56 entries, each represented by an RGB value.
ActiveWorkbook.Colors(5) = RGB(0,0,255)'blue
CommandBars returns a CommandBars object that represents the Microsoft Excel command bars.
Dim cbsCommandBars As Office.CommandBars Set cbsCommandBars = ActiveWorkbook.CommandBars
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.
Dim cnnsConnections As Connections Set cnnsConnections = ActiveWorkbook.Connections
ConnectionsDisabled disables the external connections or links in the workbook.
Dim booConnectionsDisabled As Boolean booConnectionsDisabled = ActiveWorkbook.ConnectionsDisabled
Container returns the object that represents the container application for the specified OLE object. Read-only object.
Dim objContainer As Object Set objContainer = ActiveWorkbook.Container
ContentTypeProperties returns a MetaProperties collection that describes the metadata stored in the workbook.
Dim mpsContentTypeProperties As Office.MetaProperties Set mpsContentTypeProperties = ActiveWorkbook.ContentTypeProperties
CreateBackup true if a backup file is created when this file is saved.
Dim booCreateBackup As Boolean booCreateBackup = ActiveWorkbook.CreateBackup
CustomDocumentProperties returns or sets a DocumentProperties collection that represents all the custom document properties for the specified workbook.
Dim dpsCustomDocumentProperties As DocumentProperties dpsCustomDocumentProperties = ActiveWorkbook.CustomDocumentProperties
CustomViews returns a CustomViews collection that represents all the custom views for the workbook.
Dim cvsCustomViews As CustomViews Set cvsCustomViews = ActiveWorkbook.CustomViews
CustomXMLParts returns a CustomXMLParts collection that represents the custom XML in the XML data store.
Dim cxmsCustomXMLParts As Office.CustomXMLParts Set cxmsCustomXMLParts = ActiveWorkbook.CustomXMLParts
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.
DisplayDrawingObjects returns or sets how shapes are displayed.
ActiveWorkbook.DisplayDrawingObjects = xlDisplayShapes
DisplayInkComments a Boolean value that determines whether ink comments are displayed in the workbook.
ActiveWorkbook.DisplayInkComments = True
DocumentInspectors returns a DocumentInspectors collection that represents the Document Inspector modules for the specified workbook.
Dim disDocumentInspectors As Office.DocumentInspectors Set disDocumentInspectors = ActiveWorkbook.DocumentInspectors
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.
Dim dlvsDocumentLibraryVersions As Office.DocumentLibraryVersions Set dlvsDocumentLibraryVersions = ActiveWorkbook.DocumentLibraryVersions
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.
ActiveWorkbook.DoNotPromptForConvert = True
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.
ActiveWorkbook.EnvelopeVisible = True
Excel8CompatibilityMode provides developers with a way to check if the workbook is in compatibility mode.
Dim booExcel8CompatibilityMode As Boolean booExcel8CompatibilityMode = ActiveWorkbook.Excel8CompatibilityMode
Final returns or sets a Boolean that indicates whether a workbook is final.
ActiveWorkbook.Final = True
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.
Dim booHasVBProject As Boolean booHasVBProject = ActiveWorkbook.HasVBProject
HighlightChangesOnScreen true if changes to the shared workbook are highlighted on-screen.
ActiveWorkbook.HighlightChangesOnScreen = True
IconSets this property is used to filter data in a workbook based on a cell icon from the IconSets collection.
Dim issIconSets As IconSets Set issIconSets = ActiveWorkbook.IconSets
InactiveListBorderVisible a Boolean value that specifies whether list borders are visible when a list is not active. Returns True if the border is visible.
ActiveWorkbook.InactiveListBorderVisible = True
IsAddin true if the workbook is running as an add-in.
ActiveWorkbook.IsAddin = True
IsInplace true if the specified workbook is being edited in place. False if the workbook has been opened in Microsoft Excel for editing.
Dim booIsInplace As Boolean booIsInplace = ActiveWorkbook.IsInplace
KeepChangeHistory true if change tracking is enabled for the shared workbook.
ActiveWorkbook.KeepChangeHistory = True
ListChangesOnNewSheet true if changes to the shared workbook are shown on a separate worksheet.
ActiveWorkbook.ListChangesOnNewSheet = True
Model returns the top-level Model object that is the one data model for the workbook.
Dim mdlModel As Model Set mdlModel = ActiveWorkbook.Model
MultiUserEditing true if the workbook is open as a shared list.
Dim booMultiUserEditing As Boolean booMultiUserEditing = ActiveWorkbook.MultiUserEditing
Name returns a String value that represents the name of the object.
Dim strName As String strName = ActiveWorkbook.Name
Names returns a Names collection that represents all the names in the specified workbook (including all worksheet-specific names).
Dim nmsNames As Names Set nmsNames = ActiveWorkbook.Names
Parent returns the parent object for the specified object. Read-only.
Dim appParent As Application Set appParent = ActiveWorkbook.Parent
Path returns a String that represents the complete path to the workbook/file that this workbook object represents.
Dim strPath As String strPath = ActiveWorkbook.Path
Permission returns a Permission object that represents the permission settings in the specified workbook.
Dim prmPermission As Office.Permission Set prmPermission = ActiveWorkbook.Permission
PersonalViewListSettings true if filter and sort settings for lists are included in the user's personal view of the shared workbook.
ActiveWorkbook.PersonalViewListSettings = True
PivotTables returns an pivottables that represents a collection of all the PivotTable reports on a worksheet. Read-only.
Dim ptsPivotTables As PivotTables Set ptsPivotTables = ActiveWorkbook.PivotTables
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.
Dim booProtectStructure As Boolean booProtectStructure = ActiveWorkbook.ProtectStructure
ProtectWindows true if the windows of the workbook are protected.
Dim booProtectWindows As Boolean booProtectWindows = ActiveWorkbook.ProtectWindows
PublishObjects returns the PublishObjects collection.
Dim posPublishObjects As PublishObjects Set posPublishObjects = ActiveWorkbook.PublishObjects
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.
Dim qrsQueries As Queries Set qrsQueries = ActiveWorkbook.Queries
ReadOnly returns True if the object has been opened as read-only.
Dim booReadOnly As Boolean booReadOnly = ActiveWorkbook.ReadOnly
ReadOnlyRecommended true if the workbook was saved as read-only recommended.
ActiveWorkbook.ReadOnlyRecommended = True
Research returns a Research object that represents the research service for a workbook.
Dim rsrResearch As Research Set rsrResearch = ActiveWorkbook.Research
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).
Dim lngRevisionNumber As Long lngRevisionNumber = ActiveWorkbook.RevisionNumber
Saved true if no changes have been made to the specified workbook since it was last saved.
ActiveWorkbook.Saved = True
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.
Dim spyServerPolicy As Office.ServerPolicy Set spyServerPolicy = ActiveWorkbook.ServerPolicy
ServerViewableItems allows a developer to interact with the list of published objects in the workbook that are shown on the server.
Dim svisServerViewableItems As ServerViewableItems Set svisServerViewableItems = ActiveWorkbook.ServerViewableItems
Sheets returns a Sheets collection that represents all the sheets in the specified workbook.
Dim shtsSheets As Sheets Set shtsSheets = ActiveWorkbook.Sheets
ShowConflictHistory true if the Conflict History worksheet is visible in the workbook that's open as a shared list.
ActiveWorkbook.ShowConflictHistory = True
ShowPivotChartActiveFields this property controls the visibility of the PivotChart Filter pane.
ActiveWorkbook.ShowPivotChartActiveFields = True
ShowPivotTableFieldList true (default) if the PivotTable field list can be shown.
ActiveWorkbook.ShowPivotTableFieldList = True
Signatures returns the digital signatures for a workbook.
Dim sstSignatures As Office.SignatureSet Set sstSignatures = ActiveWorkbook.Signatures
SlicerCaches returns the SlicerCaches object associated with the workbook.
Dim scsSlicerCaches As SlicerCaches Set scsSlicerCaches = ActiveWorkbook.SlicerCaches
SmartDocument returns a SmartDocument object that represents the settings for a smart document solution.
Dim sdtSmartDocument As Office.SmartDocument Set sdtSmartDocument = ActiveWorkbook.SmartDocument
Styles returns a Styles collection that represents all the styles in the specified workbook.
Dim stysStyles As Styles Set stysStyles = ActiveWorkbook.Styles
TableStyles returns a TableStyles collection object for the current workbook that refers to the styles used in the current workbook.
Dim tssTableStyles As TableStyles Set tssTableStyles = ActiveWorkbook.TableStyles
TemplateRemoveExtData true if external data references are removed when the workbook is saved as a template.
ActiveWorkbook.TemplateRemoveExtData = True
Theme returns the theme applied to the current workbook.
Dim oteTheme As Office.OfficeTheme Set oteTheme = ActiveWorkbook.Theme
UpdateLinks returns or sets an XlUpdateLink constant indicating a workbook's setting for updating embedded OLE links.
ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways
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.
Dim varUserStatus As Variant varUserStatus = ActiveWorkbook.UserStatus
UseWholeCellCriteria true if the workbook uses search patterns that match the entire content of a cell.
Dim booUseWholeCellCriteria As Boolean booUseWholeCellCriteria = ActiveWorkbook.UseWholeCellCriteria
UseWildcards true if the workbook enables wildcards for character string comparisons and searching.
Dim booUseWildcards As Boolean booUseWildcards = ActiveWorkbook.UseWildcards
VBASigned true if the Visual Basic for Applications project for the specified workbook has been digitally signed.
Dim booVBASigned As Boolean booVBASigned = ActiveWorkbook.VBASigned
VBProject returns a VBProject object that represents the Visual Basic project in the specified workbook.
Dim vbpVBProject As VBIDE.VBProject Set vbpVBProject = ActiveWorkbook.VBProject
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.
Dim wosWebOptions As WebOptions Set wosWebOptions = ActiveWorkbook.WebOptions
Windows returns a Windows collection that represents all the windows in the specified workbook.
Dim wndsWindows As Windows Set wndsWindows = ActiveWorkbook.Windows
Worksheets returns a Sheets collection that represents all the worksheets in the specified workbook.
Dim shtsWorksheets As Sheets Set shtsWorksheets = ActiveWorkbook.Worksheets
WriteReserved true if the workbook is write-reserved.
Dim booWriteReserved As Boolean booWriteReserved = ActiveWorkbook.WriteReserved
WriteReservedBy returns the name of the user who currently has write permission for the workbook.
Dim strWriteReservedBy As String strWriteReservedBy = ActiveWorkbook.WriteReservedBy
XmlMaps returns an XmlMaps collection that represents the schema maps that have been added to the specified workbook.
Dim xmsXmlMaps As XmlMaps Set xmsXmlMaps = ActiveWorkbook.XmlMaps
XmlNamespaces returns an XmlNamespaces collection that represents the XML namespaces contained in the specified workbook.
Dim xnsXmlNamespaces As XmlNamespaces Set xnsXmlNamespaces = ActiveWorkbook.XmlNamespaces
Workbooks.Count returns a Long value that represents the number of objects in the collection.
Dim lngCount As Long lngCount = Workbooks.Count
Workbooks.Item returns a single Workbook object from the collection.
Dim wbItem As Workbook Set wbItem = Workbooks(Index:=1)
Workbooks.Parent returns the parent object for the specified object. Read-only.
Dim objParent As Object Set objParent = Workbooks.Parent