Class Workbook (Excel VBA)
The class Workbook represents a Microsoft Excel workbook.
The classes Application, ProtectedViewWindow and RecentFile. give access to class Workbook
Dim wb as Workbook
Set wb = ActiveWorkbook
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
Methods
Activate - Activates the first window associated with the workbook.
Workbooks("BOOK4.XLS").ActivateBreakLink - Converts formulas linked to other Microsoft Excel sources or OLE sources to values.
Dim strName As String: strName =
ActiveWorkbook.BreakLink Name:=strName, Type:=xlLinkTypeExcelLinksChangeFileAccess - Changes the access permissions for the workbook. This may require an updated version to be loaded from the disk.
ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnlyChangeLink - Changes a link from one document to another.
ActiveWorkbook.ChangeLink "c:\excel\book1.xls", "c:\excel\book2.xls", xlExcelLinksClose - Closes the object.
Workbooks("BOOK1.XLS").Close SaveChanges:=FalseExportAsFixedFormat - The ExportAsFixedFormat method is used to publish a workbook to either the PDF or XPS format.
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDFFollowHyperlink - 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://codevbacom"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 IfLockServerFile - Locks the workbook on the server to prevent modification.
ActiveWorkbook.LockServerFileOpenLinks - Opens the supporting documents for a link or links.
Dim strName As String: strName =
ActiveWorkbook.OpenLinks Name:=strNamePrintOut - Prints the object.
ActiveWorkbook.PrintOutProtect - Protects a workbook so that it cannot be modified.
ActiveWorkbook.ProtectRunAutoMacros - Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. For new Visual Basic code, you should use the Open, Activate, and Deactivate events and the Close method instead of these macros.
ActiveWorkbook.RunAutoMacros Which:=xlAutoActivateSave - Saves changes to the specified workbook.
ActiveWorkbook.SaveSaveAs - Saves changes to the workbook in a different file.
ActiveWorkbook.SaveAsSaveAsXMLData - 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:=SaveCopyAs - Saves a copy of the workbook to a file but doesn't modify the open workbook in memory.
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"Unprotect - Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.
ActiveWorkbook.UnprotectUpdateLink - Updates a Microsoft Excel, DDE, or OLE link (or links).
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSourcesXmlImport - Imports an XML data file into the current workbook.
Dim strUrl As String: strUrl =
Dim xxiXmlImport As XlXmlImportResult
xxiXmlImport = ActiveWorkbook.XmlImport(Url:=strUrl, ImportMap:=)AcceptAllChanges - Accepts all changes in the specified shared workbook.
AddToFavorites - Adds a shortcut to the workbook or hyperlink to the Favorites folder.
ApplyTheme - Applies the specified theme to the current workbook.
CanCheckIn - True if Microsoft Excel can check in a specified workbook to a server.
CheckIn - Returns a workbook from a local computer to a server, and sets the local workbook to read-only so that it cannot be edited locally. Calling this method will also close the workbook.
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.
ConvertComments - Converts all legacy comments and notes to modern comments.
CreateForecastSheet - If you have historical time-based data, you can use CreateForecastSheet to create a forecast. When you create a forecast, a new worksheet is created that contains a table of the historical and predicted values and a chart showing this. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.
DeleteNumberFormat - Deletes a custom number format from the workbook.
EnableConnections - The EnableConnections method allows developers to programmatically enable data connections within the workbook for the user.
EndReview - Terminates a review of a file that has been sent for review by using the SendForReview method.
ExclusiveAccess - Assigns the current user exclusive access to the workbook that's open as a shared list.
GetWorkflowTasks - Returns the collection of WorkflowTask objects for the specified workbook.
GetWorkflowTemplates - Returns the collection of WorkflowTemplate objects for the specified workbook.
HighlightChangesOptions - Controls how changes are shown in a shared workbook.
LinkInfo - Returns the link date and update status.
MergeWorkbook - Merges changes from one workbook into an open workbook.
NewWindow - Creates a new window or a copy of the specified window.
PivotCaches - Returns a PivotCaches collection that represents all the PivotTable caches in the specified workbook.
Post - Posts the specified workbook to a public folder. This method works only with a Microsoft Exchange client connected to a Microsoft Exchange server.
PrintPreview - Shows a preview of the object as it would look when printed.
ProtectSharing - Saves the workbook and protects it for sharing.
PurgeChangeHistoryNow - Removes entries from the change log for the specified workbook.
RefreshAll - Refreshes all external data ranges and PivotTable reports in the specified workbook.
RejectAllChanges - Rejects all changes in the specified shared workbook.
ReloadAs - Reloads a workbook based on an HTML document, using the specified document encoding.
RemoveDocumentInformation - Removes all information of the specified type from the workbook.
RemoveUser - Disconnects the specified user from the shared workbook.
ReplyWithChanges - Sends an email message to the author of a workbook that has been sent out for review, notifying them that a reviewer has completed review of the workbook.
ResetColors - Resets the color palette to the default colors.
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.
SetLinkOnData - Sets the name of a procedure that runs whenever a DDE link is updated.
SetPasswordEncryptionOptions - Sets the options for encrypting workbooks by using passwords.
ToggleFormsDesign - Used to toggle Excel into Design mode when using forms controls.
UnprotectSharing - Turns off protection for sharing and saves the workbook.
UpdateFromFile - Updates a read-only workbook from the saved disk version of the workbook if the disk version is more recent than the copy of the workbook that is loaded in memory. If the disk copy hasn't changed since the workbook was loaded, the in-memory copy of the workbook isn't reloaded.
WebPagePreview - Displays a preview of the specified workbook as it would look if saved as a webpage.
XmlImportXml - Imports an XML data stream that has been previously loaded into memory. Excel uses the first qualifying map found, or if the destination range is specified, Excel automatically lists the data.
Properties
AccuracyVersion specifies whether certain worksheet functions use the latest accuracy algorithms to calculate their results.
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.
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.
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.
AutoUpdateFrequency returns or sets the number of minutes between automatic updates to the shared workbook.
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.
BuiltinDocumentProperties returns a DocumentProperties collection that represents all the built-in document properties for the specified workbook. Read-only.
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.
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.
CheckCompatibility controls whether or not the compatibility checker is run automatically when the workbook is saved.
CodeName returns the code name for the object.
Colors returns or sets colors in the palette for the workbook. The palette has 56 entries, each represented by an RGB value.
CommandBars returns a CommandBars object that represents the Microsoft Excel command bars.
ConflictResolution returns or sets the way conflicts are to be resolved whenever a shared workbook is updated.
Connections establishes a connection between the workbook and an ODBC or an OLEDB data source and refreshes the data without prompting the user.
ConnectionsDisabled disables the external connections or links in the workbook.
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.
CustomDocumentProperties returns or sets a DocumentProperties collection that represents all the custom document properties for the specified workbook.
CustomViews returns a CustomViews collection that represents all the custom views for the workbook.
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.
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.
DisplayInkComments a Boolean value that determines whether ink comments are displayed in 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.
EnableAutoRecover saves changed files of all formats on a timed interval.
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.
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.
Final returns or sets a Boolean that indicates whether a workbook is final.
ForceFullCalculation returns or sets the specified workbook to forced calculation mode.
FullName returns the name of the object, including its path on disk, as a string.
FullNameURLEncoded returns a String indicating the name of the object, including its path on disk, as a string.
HasPassword true if the workbook has a protection password.
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.
IconSets this property is used to filter data in a workbook based on a cell icon from the IconSets collection.
InactiveListBorderVisible a Boolean value that specifies whether list borders are visible when a list is not active. Returns True if the border is visible.
IsAddin true if the workbook is running as an add-in.
IsInplace true if the specified workbook is being edited in place. False if the workbook has been opened in Microsoft Excel for editing.
KeepChangeHistory true if change tracking is enabled for the shared workbook.
ListChangesOnNewSheet true if changes to the shared workbook are shown on a separate worksheet.
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.
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).
Password returns or sets the password that must be supplied to open the specified workbook.
PasswordEncryptionAlgorithm returns a String indicating the algorithm that Microsoft Excel uses to encrypt passwords for the specified workbook.
PasswordEncryptionFileProperties true if Microsoft Excel encrypts file properties for the specified password-protected workbook.
PasswordEncryptionKeyLength returns a Long indicating the key length of the algorithm that Microsoft Excel uses when encrypting passwords for the specified workbook.
PasswordEncryptionProvider returns a String specifying the name of the algorithm encryption provider that Microsoft Excel uses when encrypting passwords for the specified workbook.
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.
PersonalViewListSettings true if filter and sort settings for lists are included in the user's personal view of the shared workbook.
PersonalViewPrintSettings true if print settings are included in the user's personal view of the shared workbook.
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.
ProtectStructure true if the order of the sheets in the workbook is protected.
ProtectWindows true if the windows of the workbook are protected.
PublishObjects returns the PublishObjects collection.
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.
ReadOnlyRecommended true if the workbook was saved as read-only recommended.
RemovePersonalInformation true if personal information can be removed from the specified workbook. The default value is False.
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).
Saved true if no changes have been made to the specified workbook since it was last saved.
SaveLinkValues true if Microsoft Excel saves external link values with the workbook.
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.
ShowConflictHistory true if the Conflict History worksheet is visible in the workbook that's open as a shared list.
ShowPivotChartActiveFields this property controls the visibility of the PivotChart Filter pane.
ShowPivotTableFieldList true (default) if the PivotTable field list can be shown.
Signatures returns the digital signatures for a workbook.
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.
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.
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.
UpdateRemoteReferences true if Microsoft Excel updates remote references in the workbook.
UserStatus returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list.
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.
VBProject returns a VBProject object that represents the Visual Basic project in the specified workbook.
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.
Windows returns a Windows collection that represents all the windows in the specified workbook.
Worksheets returns a Sheets collection that represents all the worksheets in the specified workbook.
WritePassword returns or sets a String for the write password of a workbook.
WriteReserved true if the workbook is write-reserved.
WriteReservedBy returns the name of the user who currently has write permission for the workbook.
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.
Related Classes
Chart - Represents a chart in a workbook.
Connections - A collection of WorkbookConnection objects for the specified workbook.
CustomViews - A collection of custom workbook views.
IconSets - Represents a collection of icon sets used in an icon set conditional formatting rule.
Model - Model
Names - A collection of all the Name objects in the application or workbook.
PivotCaches - Represents the collection of memory caches from the PivotTable reports in a workbook.
PivotTables - A collection of all the PivotTable objects in the specified workbook.
PublishObjects - A collection of all PublishObject objects in the workbook.
Queries - The collection of WorkbookQuery objects introduced in Office 2016.
Research - Represents the controls of a Research query.
ServerViewableItems - A collection of objects that have been marked as viewable on the server.
Sheets - A collection of all the sheets in the specified or active workbook.
Slicer - Represents a slicer in a workbook.
SlicerCaches - Represents the collection of slicer caches associated with the specified workbook.
Styles - A collection of all the Style objects in the specified or active workbook.
TableStyle - Represents a single style that can be applied to a table or slicer.
TableStyles - Represents styles that can be applied to a table.
WebOptions - Contains workbook-level attributes used by Microsoft Excel when you save a document as a webpage or open a webpage.
Window - Represents a window.
Windows - A collection of all the Window objects in Microsoft Excel.
Worksheet - Represents a worksheet.
XmlMaps - Represents the collection of XmlMap objects that have been added to a workbook.
XmlNamespaces - Represents the collection of XmlNamespace objects in a workbook.