Workbook Object (Excel)

Represents a Microsoft Excel workbook.

Remarks

The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel.

ThisWorkbook Property

The ThisWorkbook property returns the workbook where the Visual Basic code is running. In most cases, this is the same as the active workbook. However, if the Visual Basic code is part of an add-in, the ThisWorkbook property won't return the active workbook. In this case, the active workbook is the workbook calling the add-in, whereas the ThisWorkbook property returns the add-in workbook.

If you'll be creating an add-in from your Visual Basic code, you should use the ThisWorkbook property to qualify any statement that must be run on the workbook you compile into the add-in.

Example

Use Workbooks ( index ), where index is the workbook name or index number, to return a single Workbook object. The following example activates workbook one.

Workbooks(1).Activate

The index number denotes the order in which the workbooks were opened or created. Workbooks(1) is the first workbook created, and Workbooks(Workbooks.Count) is the last one created. Activating a workbook doesn't change its index number. All workbooks are included in the index count, even if they're hidden.

The Name property returns the workbook name. You cannot set the name by using this property; if you need to change the name, use the SaveAs method to save the workbook under a different name. The following example activates Sheet1 in the workbook named Cogs.xls (the workbook must already be open in Microsoft Excel).

Workbooks("Cogs.xls").Worksheets("Sheet1").Activate

The ActiveWorkbook property returns the workbook that's currently active. The following example sets the name of the author for the active workbook.

ActiveWorkbook.Author = "Jean Selva"

Sample code provided by: Holy Macro! Books, Holy Macro! It's 2,500 Excel VBA Examples

This example emails a worksheet tab from the active workbook using a specified email address and subject. To run this code, the active worksheet must contain the email address in cell A1, the subject in cell B1, and the name of the worksheet to send in cell C1.

Sub SendTab()
   'Declare and initialize your variables, and turn off screen updating.
   Dim wks As Worksheet
   Application.ScreenUpdating = False
   Set wks = ActiveSheet
   
   'Copy the target worksheet, specified in cell C1, to the clipboard.
   Worksheets(Range("C1").Value).Copy
   
   'Send the content in the clipboard to the email account specified in cell A1,
   'using the subject line specified in cell B1.
   ActiveWorkbook.SendMail wks.Range("A1").Value, wks.Range("B1").Value
   
   'Do not save changes and turn screen updating back on.
   ActiveWorkbook.Close savechanges:=False
   Application.ScreenUpdating = True
End Sub

Events

Name
Activate
AddinInstall
AddinUninstall
AfterSave
AfterXmlExport
AfterXmlImport
BeforeClose
BeforePrint
BeforeSave
BeforeXmlExport
BeforeXmlImport
Deactivate
ModelChange
NewChart
NewSheet
Open
PivotTableCloseConnection
PivotTableOpenConnection
RowsetComplete
SheetActivate
SheetBeforeDelete
SheetBeforeDoubleClick
SheetBeforeRightClick
SheetCalculate
SheetChange
SheetDeactivate
SheetFollowHyperlink
SheetLensGalleryRenderComplete
SheetPivotTableAfterValueChange
SheetPivotTableBeforeAllocateChanges
SheetPivotTableBeforeCommitChanges
SheetPivotTableBeforeDiscardChanges
SheetPivotTableChangeSync
SheetPivotTableUpdate
SheetSelectionChange
SheetTableUpdate
Sync
WindowActivate
WindowDeactivate
WindowResize

Methods

Name
AcceptAllChanges
Activate
AddToFavorites
ApplyTheme
BreakLink
CanCheckIn
ChangeFileAccess
ChangeLink
CheckIn
CheckInWithVersion
Close
DeleteNumberFormat
EnableConnections
EndReview
ExclusiveAccess
ExportAsFixedFormat
FollowHyperlink
ForwardMailer
GetWorkflowTasks
GetWorkflowTemplates
HighlightChangesOptions
LinkInfo
LinkSources
LockServerFile
MergeWorkbook
NewWindow
OpenLinks
PivotCaches
Post
PrintOut
PrintPreview
Protect
ProtectSharing
PurgeChangeHistoryNow
RefreshAll
RejectAllChanges
ReloadAs
RemoveDocumentInformation
RemoveUser
Reply
ReplyAll
ReplyWithChanges
ResetColors
RunAutoMacros
Save
SaveAs
SaveAsXMLData
SaveCopyAs
SendFaxOverInternet
SendForReview
SendMail
SendMailer
SetLinkOnData
SetPasswordEncryptionOptions
ToggleFormsDesign
Unprotect
UnprotectSharing
UpdateFromFile
UpdateLink
WebPagePreview
XmlImport
XmlImportXml
CreateForecastSheet

Properties

Name
AccuracyVersion
ActiveChart
ActiveSheet
ActiveSlicer
Application
AutoUpdateFrequency
AutoUpdateSaveChanges
BuiltinDocumentProperties
CalculationVersion
CaseSensitive
ChangeHistoryDuration
ChartDataPointTrack
Charts
CheckCompatibility
CodeName
Colors
CommandBars
ConflictResolution
Connections
ConnectionsDisabled
Container
ContentTypeProperties
CreateBackup
Creator
CustomDocumentProperties
CustomViews
CustomXMLParts
Date1904
DefaultPivotTableStyle
DefaultSlicerStyle
DefaultTableStyle
DefaultTimelineStyle
DisplayDrawingObjects
DisplayInkComments
DocumentInspectors
DocumentLibraryVersions
DoNotPromptForConvert
EnableAutoRecover
EncryptionProvider
EnvelopeVisible
Excel4IntlMacroSheets
Excel4MacroSheets
Excel8CompatibilityMode
FileFormat
Final
ForceFullCalculation
FullName
FullNameURLEncoded
HasPassword
HasVBProject
HighlightChangesOnScreen
IconSets
InactiveListBorderVisible
IsAddin
IsInplace
KeepChangeHistory
ListChangesOnNewSheet
Mailer
Model
MultiUserEditing
Name
Names
Parent
Password
PasswordEncryptionAlgorithm
PasswordEncryptionFileProperties
PasswordEncryptionKeyLength
PasswordEncryptionProvider
Path
Permission
PersonalViewListSettings
PersonalViewPrintSettings
PivotTables
PrecisionAsDisplayed
ProtectStructure
ProtectWindows
PublishObjects
ReadOnly
ReadOnlyRecommended
RemovePersonalInformation
Research
RevisionNumber
Saved
SaveLinkValues
ServerPolicy
ServerViewableItems
SharedWorkspace
Sheets
ShowConflictHistory
ShowPivotChartActiveFields
ShowPivotTableFieldList
Signatures
SlicerCaches
SmartDocument
Styles
Sync
TableStyles
TemplateRemoveExtData
Theme
UpdateLinks
UpdateRemoteReferences
UserStatus
UseWholeCellCriteria
UseWildcards
VBASigned
VBProject
WebOptions
Windows
Worksheets
WritePassword
WriteReserved
WriteReservedBy
XmlMaps
XmlNamespaces
Queries

About the Contributor

Holy Macro! Books publishes entertaining books for people who use Microsoft Office. See the complete catalog at MrExcel.com.

See also

Excel Object Model Reference