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 = 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

For Each

Here is an example of processing the Workbook items in a collection.

Dim wb As Workbook
For Each wb In Workbooks
	
Next wb

Themes

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

Methods

These are the main methods of the Workbook class

Activate - Activates the first window associated with the workbook.

ActiveWorkbook.Activate

Close - Closes the object.

ActiveWorkbook.Close

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.Close

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)

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.

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.

ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly

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.

ActiveWorkbook.HighlightChangesOptions

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.

ActiveWorkbook.ProtectSharing

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.

ActiveWorkbook.RejectAllChanges

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.

ActiveWorkbook.SendFaxOverInternet

SendForReview - Sends a workbook in an email message for review to the specified recipients.

ActiveWorkbook.SendForReview

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).

ActiveWorkbook.UpdateLink

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.

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.

ActiveWorkbook.ChangeHistoryDuration =

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.

ActiveWorkbook.DefaultPivotTableStyle =

DefaultSlicerStyle specifies the style from the TableStyles tablestyles that is used as the default style for slicers. Read/write.

ActiveWorkbook.DefaultSlicerStyle =

DefaultTableStyle specifies the table style from the TableStyles collection that is used as the default table style. Read/write Variant.

ActiveWorkbook.DefaultTableStyle =

DefaultTimelineStyle the name of the default slicer style of the workbook.

ActiveWorkbook.DefaultTimelineStyle =

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.

ActiveWorkbook.EncryptionProvider =

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

WorkIdentity

ActiveWorkbook.WorkIdentity =

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