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.