How to use Excel class Workbook

Set Workbook

You can Set a Workbook variable by calling a method that returns a variable of that type, for example:

Dim wbs As Workbooks Set wb = wbs.Add()

The following methods can be used to set a Workbook variable. Click the link to visit the page that describes the method: Application.ActiveWorkbook, Application.NextLetter, Application.ThisWorkbook, ProtectedViewWindow.Edit, ProtectedViewWindow.Workbook, RecentFile.Open, Workbooks.Add, Workbooks.Item, Workbooks.Open, Workbooks.OpenDatabase, Workbooks.OpenXML.

The other common way to Set a Workbook variable is using the Item method on the Workbooks collection:

Dim wb As Workbook Set wb = Workbooks.Item(Index:= )

Test if workbook is open (function)

Public Function WorkbookIsOpen(wbname) As Boolean 
    Dim wb As Workbook 
    On Error Resume Next 
    Set wb = Workbooks(wbname) 
    If Err = 0 Then 
        WorkbookIsOpen = True 
    Else 
        WorkbookIsOpen = False 
    End If 
End Function

For Each in collection Workbook

The code below shows how you can loop through a Workbooks collection.

Dim wb As Workbook For Each wb In Application.Workbooks Next wb

Add Workbook

Create a new Workbook - read more How to create a new Workbook

Dim wbs As Workbooks Set wb = wbs.Add()

Arguments:

AddToFavorites Workbook

Adds a shortcut to the workbook or hyperlink to the Favorites folder.

Dim wb As Workbook wb.AddToFavorites 

Open Workbook

Opens a workbook.

Open a Workbook - read more How to open a Workbook

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:= )

OpenXML Workbook

Opens an XML data file. Returns a Workbook object.

Dim wbs As Workbooks
 Set wb = wbs.OpenXML(Filename:= )

Arguments:

OpenLinks Workbook

Opens the supporting documents for a link or links.

Dim wb As Workbook wb.OpenLinks Name:= 

Arguments:

Save Workbook

Saves changes to the specified workbook.

Dim wb As Workbook
wb.Save 

Protect Workbook

Protects a workbook so that it cannot be modified.

Dim wb As Workbook
wb.Protect 

Protect a Workbook against structure changes - read more How to protect a Workbook against structure changes

ProtectSharing Workbook

Saves the workbook and protects it for sharing.

Dim wb As Workbook
wb.ProtectSharing 

Protect Workbook Sharing setting - read more Protect Workbook Sharing setting

SaveAs Workbook

Saves changes to the workbook in a different file.

Save a Workbook as another file - read more How to save a Workbook as another file

SaveAsXMLData Workbook

Exports the data that has been mapped to the specified XML schema map to an XML data file.

Dim wb As Workbook 
wb.SaveAsXMLData Filename:= ,Map:= 

Arguments:

SaveCopyAs Workbook

Saves a copy of the workbook to a file but doesn't modify the open workbook in memory.

Dim wb As Workbook 
wb.SaveCopyAs 

Arguments:

Close Workbook

close workbook

Closes the workbook.

Close a Workbook and save options - read more How to close a Workbook

Activate Workbook

Activates the first window associated with the workbook.

Dim wb As Workbook
wb.Activate 

AcceptAllChanges Workbook

Accepts all changes in the specified shared workbook.

Dim wb As Workbook
wb.AcceptAllChanges 

Arguments:

ApplyTheme Workbook

Applies the specified theme to the current workbook.

Dim wb As Workbook
 wb.ApplyTheme Filename:= 

Arguments:

BreakLink Workbook

Converts formulas linked to other Microsoft Excel sources or OLE sources to values.

Dim wb As Workbook
 wb.BreakLink Name:= ,Type:= 

Arguments:

CanCheckIn Workbook

True if Microsoft Excel can check in a specified workbook to a server.

Dim wb As Workbook 
Set wb = wb.CanCheckIn()

ChangeFileAccess Workbook

Changes the access permissions for the workbook. This may require an updated version to be loaded from the disk.

Dim wb As Workbook
 wb.ChangeFileAccess Mode:= 

Arguments:

ChangeLink Workbook

Changes a link from one document to another.

Dim wb As Workbook
 wb.ChangeLink Name:= ,NewName:= 

Arguments:

CheckIn Workbook

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.

Dim wb As Workbook wb.CheckIn 

Arguments:

CheckInWithVersion Workbook

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.

Dim wb As Workbook
wb.CheckInWithVersion 

Arguments:

DeleteNumberFormat Workbook

Deletes a custom number format from the workbook.

Dim wb As Workbook
wb.DeleteNumberFormat NumberFormat:= 

Arguments:

EnableConnections Workbook

The EnableConnections method allows developers to programmatically enable data connections within the workbook for the user.

Dim wb As Workbook
 wb.EnableConnections 

EndReview Workbook

Terminates a review of a file that has been sent for review using the SendForReview method.

Dim wb As Workbook
wb.EndReview 

ExclusiveAccess Workbook

Assigns the current user exclusive access to the workbook that's open as a shared list.

Dim wb As Workbook 
Set wb = wb.ExclusiveAccess()

ExportAsFixedFormat Workbook

The ExportAsFixedFormat method is used to publish a workbook to either the PDF or XPS format.

Dim wb As Workbook
 wb.ExportAsFixedFormat Type:= 

Arguments:

FollowHyperlink Workbook

Displays a cached document, if it's already been downloaded. Otherwise, this method resolves the hyperlink, downloads the target document, and displays the document in the appropriate application.

Dim wb As Workbook
wb.FollowHyperlink Address:= 

Arguments:

ForwardMailer Workbook

You have requested Help for a Visual Basic keyword used only on the Macintosh. For information about this keyword, consult the language reference Help included with Microsoft Office Macintosh Edition.

Dim wb As Workbook
wb.ForwardMailer 

GetWorkflowTasks Workbook

Returns the collection of WorkflowTask objects for the specified workbook.

Dim wb As Workbook
Set wb = wb.GetWorkflowTasks()

GetWorkflowTemplates Workbook

Returns the collection of WorkflowTemplate objects for the specified workbook.

Dim wb As Workbook
Set wb = wb.GetWorkflowTemplates()

HighlightChangesOptions Workbook

Controls how changes are shown in a shared workbook.

Dim wb As Workbook
wb.HighlightChangesOptions 

Arguments:

LinkInfo Workbook

Returns the link date and update status.

Dim wb As Workbook
wb.LinkInfo Name:= ,LinkInfo:= 

Arguments:

LinkSources Workbook

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 wb As Workbook
wb.LinkSources 

Arguments:

LockServerFile Workbook

Locks the workbook on the server to prevent modification.

Dim wb As Workbook
wb.LockServerFile 

MergeWorkbook Workbook

Merges changes from one workbook into an open workbook.

Dim wb As Workbook
wb.MergeWorkbook Filename:= 

Arguments:

NewWindow Workbook

Creates a new window or a copy of the specified window.

Dim wb As Workbook Set wb = wb.NewWindow()

PivotCaches Workbook

Returns a PivotCaches collection that represents all the PivotTable caches in the specified workbook.

Dim wb As Workbook Set wb = wb.PivotCaches()

Post Workbook

Posts the specified workbook to a public folder. This method works only with a Microsoft Exchange client connected to a Microsoft Exchange server.

Dim wb As Workbook
wb.Post 

Arguments:

PrintOut Workbook

Prints the object.

Dim wb As Workbook
wb.PrintOut 

Arguments:

PrintPreview Workbook

Shows a preview of the object as it would look when printed.

Dim wb As Workbook
wb.PrintPreview 

Arguments:

PurgeChangeHistoryNow Workbook

Removes entries from the change log for the specified workbook.

Dim wb As Workbook
wb.PurgeChangeHistoryNow Days:= 

Arguments:

RefreshAll Workbook

Refreshes all external data ranges and PivotTable reports in the specified workbook.

Dim wb As Workbook
wb.RefreshAll 

RejectAllChanges Workbook

Rejects all changes in the specified shared workbook.

Dim wb As Workbook
wb.RejectAllChanges 

Arguments:

ReloadAs Workbook

Reloads a workbook based on an HTML document, using the specified document encoding.

Dim wb As Workbook
wb.ReloadAs Encoding:= 

Arguments:

RemoveDocumentInformation Workbook

Removes all information of the specified type from the workbook.

Dim wb As Workbook
wb.RemoveDocumentInformation RemoveDocInfoType:= 

Arguments:

RemoveUser Workbook

Disconnects the specified user from the shared workbook.

Dim wb As Workbook
wb.RemoveUser Index:= 

Arguments:

ResetColors Workbook

Resets the color palette to the default colors.

Dim wb As Workbook
wb.ResetColors 

RunAutoMacros Workbook

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, Close, Activate and Deactivate events instead of these macros.

Dim wb As Workbook
wb.RunAutoMacros Which:= 

Arguments:

SendFaxOverInternet Workbook

Sends a worksheet as a fax to the specfied recipients.

Dim wb As Workbook
wb.SendFaxOverInternet 

Arguments:

SendForReview Workbook

Sends a workbook in an e-mail message for review to the specified recipients.

Dim wb As Workbook
wb.SendForReview 

Arguments:

SendMail Workbook

Sends the workbook by using the installed mail system.

Dim wb As Workbook
wb.SendMail Recipients:= 

Arguments:

SetLinkOnData Workbook

Sets the name of a procedure that runs whenever a DDE link is updated.

Dim wb As Workbook
wb.SetLinkOnData Name:= 

Arguments:

SetPasswordEncryptionOptions Workbook

Sets the options for encrypting workbooks using passwords.

Dim wb As Workbook
wb.SetPasswordEncryptionOptions 

Arguments:

ToggleFormsDesign Workbook

The ToggleFormsDesign method is used to toggle Excel into Design Mode when using forms controls.

Dim wb As Workbook wb.ToggleFormsDesign 

Unprotect Workbook

Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.

Dim wb As Workbook
wb.Unprotect 

Arguments:

UnprotectSharing Workbook

Turns off protection for sharing and saves the workbook.

Dim wb As Workbook
wb.UnprotectSharing 

Arguments:

UpdateFromFile Workbook

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.

Dim wb As Workbook
wb.UpdateFromFile 

UpdateLink Workbook

Updates a Microsoft Excel, DDE, or OLE link (or links).

Dim wb As Workbook
wb.UpdateLink 

Arguments:

WebPagePreview Workbook

Displays a preview of the specified workbook as it would look if saved as a Web page.

Dim wb As Workbook
wb.WebPagePreview 

XmlImport Workbook

Imports an XML data file into the current workbook.

Dim wb As Workbook
Set wb = wb.XmlImport(Url:= ,ImportMap:= )

Arguments:

XmlImportXml Workbook

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 will automatically list the data.

Dim wb As Workbook
Set wb = wb.XmlImportXml(Data:= ,ImportMap:= )

Arguments:

CanCheckOut Workbook

True if Microsoft Excel can check out a specified workbook from a server.

Dim wbs As Workbooks
Set wb = wbs.CanCheckOut(Filename:= )

Arguments:

CheckOut Workbook

Returns a String representing a specified workbook from a server to a local computer for editing.

Workbooks.CheckOut Filename:= 

Arguments:

For more info see

Microsoft Office Object reference on Workbook