How to use Excel class Workbook
Workbook represents a microsoft excel workbook. This page provides code for the methods of the Excel class Workbook: Add, AddToFavorites, Open, OpenDatabase, OpenText, OpenXML, OpenLinks, Save, SaveAs, Close, Activate, AcceptAllChanges, ApplyTheme, BreakLink, CanCheckIn, ChangeFileAccess, ChangeLink, CheckIn, CheckInWithVersion, DeleteNumberFormat, EnableConnections, EndReview, ExclusiveAccess, ExportAsFixedFormat, FollowHyperlink, ForwardMailer, GetWorkflowTasks, GetWorkflowTemplates, HighlightChangesOptions, LinkInfo, LinkSources, LockServerFile, MergeWorkbook, NewWindow, PivotCaches, Post, PrintOut, PrintPreview, Protect, ProtectSharing, PurgeChangeHistoryNow, RefreshAll, RejectAllChanges, ReloadAs, RemoveDocumentInformation, RemoveUser, ResetColors, RunAutoMacros, SaveAsXMLData, SaveCopyAs, SendFaxOverInternet, SendForReview, SendMail, SetLinkOnData, SetPasswordEncryptionOptions, ToggleFormsDesign, Unprotect, UnprotectSharing, UpdateFromFile, UpdateLink, WebPagePreview, XmlImport, XmlImportXml, CanCheckOut, CheckOut.
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 FunctionFor 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 wbAdd Workbook
Create a new Workbook - read more
Dim wbs As Workbooks Set wb = wbs.Add()Arguments:
- Template: Determines how the new workbook is created.If this argument is a string specifying the name of an existing Microsoft Excel file the new workbook is created with the specified file as a template.If this argument is a constant the new workbook contains a single sheet of the specified type.Can be one of the followingXlWBATemplate constants: xlWBATChart xlWBATExcel4IntlMacroSheet xlWBATExcel4MacroSheet or xlWBATWorksheet.If this argument is omitted Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property).
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.
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:
- Filename: The name of the file to open.
- Stylesheets: Either a single value or an array of values that specify which XSL Transformation (XSLT) stylesheet processing instructions to apply.
- LoadOption: Specifies how Excel opens the XML data file.Can be one of theXlXmlLoadOption constants.
OpenLinks Workbook
Opens the supporting documents for a link or links.
Dim wb As Workbook wb.OpenLinks Name:= Arguments:
- Name: The name of the Microsoft Excel or DDE/OLE link as returned from theLinkSources method.
- ReadOnly: True to open documents as read-only.The default value is False.
- Type: One of the constants of XlLink that specifies the link type.
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
ProtectSharing Workbook
Saves the workbook and protects it for sharing.
Dim wb As Workbook
wb.ProtectSharing Protect Workbook Sharing setting - read more
SaveAs Workbook
Saves changes to the workbook in a different file.
Save a Workbook as another file - read more
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:
- Filename: A string that indicates the name of the file to be saved.You can include a full path; if you don't Microsoft Excel saves the file in the current folder.
- Map: The schema map to apply to the data.
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:
- Filename: Specifies the file name for the copy
Close Workbook
Closes the workbook.
Close a Workbook and save options - read more
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:
- When: Specifies when all the changes are accepted.
- Who: Specifies by whom all the changes are accepted.
- Where: Specifies where all the changes are accepted.
ApplyTheme Workbook
Applies the specified theme to the current workbook.
Dim wb As Workbook
wb.ApplyTheme Filename:= Arguments:
- Filename: Name of the theme.
BreakLink Workbook
Converts formulas linked to other Microsoft Excel sources or OLE sources to values.
Dim wb As Workbook
wb.BreakLink Name:= ,Type:= Arguments:
- Name: The name of the link.
- Type: The type of link.
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:
- Mode: Specifies the new access mode.
- WritePassword: Specifies the write-reserved password if the file is write reserved andMode is xlReadWrite.Ignored if there's no password for the file or ifMode is xlReadOnly.
- Notify: True (or omitted) to notify the user if the file cannot be immediately accessed.
ChangeLink Workbook
Changes a link from one document to another.
Dim wb As Workbook
wb.ChangeLink Name:= ,NewName:= Arguments:
- Name: The name of the Microsoft Excel or DDE/OLE link to be changed as it was returned from the LinkSources method.
- NewName: The new name of the link.
- Type: The link type.
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:
- SaveChanges: True saves changes and checks in the document.False returns the document to a checked-in status without saving revision.
- Comments: Allows the user to enter check-in comments for the revision of the workbook being checked in (applies only if SaveChanges equals True).
- MakePublic: True allows the user to publish the workbook after it has been checked in.This submits the workbook for the approval process which can eventually result in a version of the workbook being published to users with read-only rights to the workbook (applies only if SaveChanges equals True).
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:
- SaveChanges: True to save the workbook to the server location.The default is True.
- Comments: Comments for the revision of the workbook being checked in (applies only ifSaveChanges is set to True).
- MakePublic: True to allow the user to publish the workbook after it is checked in.
- VersionType: Specifies versioning information for the workbook.
DeleteNumberFormat Workbook
Deletes a custom number format from the workbook.
Dim wb As Workbook
wb.DeleteNumberFormat NumberFormat:= Arguments:
- NumberFormat: Names the number format to be deleted.
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:
- Type: Can be either xlTypePDF or xlTypeXPS.
- Filename: A string that indicates the name of the file to be saved.You can include a full path or Excel saves the file in the current folder.
- Quality: Can be set to either xlQualityStandard orxlQualityMinimum.
- IncludeDocProperties: Set to True to indicate that document properties should be included or set to False to indicate that they are omitted.
- IgnorePrintAreas: If set to True ignores any print areas set when publishing.If set to False will use the print areas set when publishing.
- From: The number of the page at which to start publishing.If this argument is omitted publishing starts at the beginning.
- To: The number of the last page to publish.If this argument is omitted publishing ends with the last page
- OpenAfterPublish: If set to True displays file in viewer after it is published.If set to False the file is published but not displayed.
- FixedFormatExtClassPtr: Pointer to the FixedFormatExt class.
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:
- Address: The address of the target document.
- SubAddress: The location within the target document.The default value is the empty string.
- NewWindow: True to display the target application in a new window.The default value isFalse.
- AddHistory: Not used.Reserved for future use.
- ExtraInfo: A String or byte array that specifies additional information for HTTP to use to resolve the hyperlink.For example you can use ExtraInfo to specify the coordinates of an image map the contents of a form or a FAT file name.
- Method: Specifies the way ExtraInfo is attached.Can be one of the MsoExtraInfoMethodconstants.
- HeaderInfo: A String that specifies header information for the HTTP request.The defaut value is an empty string.
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:
- When: The changes that are shown.Can be one of the following XlHighlightChangesTimeconstants: xlSinceMyLastSave xlAllChanges or xlNotYetReviewed.
- Who: The user or users whose changes are shown.Can be Everyone Everyone but Me or the name of one of the users of the shared workbook.
- Where: An A1-style range reference that specifies the area to check for changes.
LinkInfo Workbook
Returns the link date and update status.
Dim wb As Workbook
wb.LinkInfo Name:= ,LinkInfo:= Arguments:
- Name: The name of the link.
- LinkInfo: The type of information to be returned.
- Type: One of the constants of XlLinkInfoType specifying the type of link to return.
- EditionRef: If the link is an edition this argument specifies the edition reference as a string in R1C1 style.This argument is required if there's more than one publisher or subscriber with the same name in the workbook.
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:
- Type: One of the constants of XlLink which specifies the type of link to return.
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:
- Filename: The file name of the workbook that contains the changes to be merged into the open workbook.
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:
- DestName: This argument is ignored.The Post method prompts the user to specify the destination for the workbook.
PrintOut Workbook
Prints the object.
Dim wb As Workbook
wb.PrintOut Arguments:
- From: The number of the page at which to start printing.If this argument is omitted printing starts at the beginning.
- To: The number of the last page to print.If this argument is omitted printing ends with the last page.
- Copies: The number of copies to print.If this argument is omitted one copy is printed.
- Preview: True to have Microsoft Excel invoke print preview before printing the object.False (or omitted) to print the object immediately.
- ActivePrinter: Sets the name of the active printer.
- PrintToFile: True to print to a file.If PrToFileName is not specified Microsoft Excel prompts the user to enter the name of the output file.
- Collate: True to collate multiple copies.
- PrToFileName: If PrintToFile is set to True this argument specifies the name of the file you want to print to.
- IgnorePrintAreas: True to ignore print areas and print the entire object.
PrintPreview Workbook
Shows a preview of the object as it would look when printed.
Dim wb As Workbook
wb.PrintPreview Arguments:
- EnableChanges: Pass a Boolean value to specify if the user can change the margins and other page setup options available in print preview.
PurgeChangeHistoryNow Workbook
Removes entries from the change log for the specified workbook.
Dim wb As Workbook
wb.PurgeChangeHistoryNow Days:= Arguments:
- Days: The number of days that changes in the change log are to be retained.
- SharingPassword: The password that unprotects the workbook for sharing.If the workbook is protected for sharing with a password and this argument is omitted the user is prompted for the password.
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:
- When: Specifies when all the changes are rejected.
- Who: Specifies by whom all the changes are rejected.
- Where: Specifies where all the changes are rejected.
ReloadAs Workbook
Reloads a workbook based on an HTML document, using the specified document encoding.
Dim wb As Workbook
wb.ReloadAs Encoding:= Arguments:
- Encoding: The encoding that is to be applied to the workbook.
RemoveDocumentInformation Workbook
Removes all information of the specified type from the workbook.
Dim wb As Workbook
wb.RemoveDocumentInformation RemoveDocInfoType:= Arguments:
- RemoveDocInfoType: Type of information to be removed.
RemoveUser Workbook
Disconnects the specified user from the shared workbook.
Dim wb As Workbook
wb.RemoveUser Index:= Arguments:
- Index: The user index.
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:
- Which: Specifies the automatic macro to run.
SendFaxOverInternet Workbook
Sends a worksheet as a fax to the specfied recipients.
Dim wb As Workbook
wb.SendFaxOverInternet Arguments:
- Recipients: A String that represents the fax numbers and e-mail addresses of the people to whom the fax will be sent.Separate multiple recipients with a semicolon.
- Subject: A String that represents the subject line for the faxed document.
- ShowMessage: True displays the fax message before sending it.False sends the fax without displaying the fax message.
SendForReview Workbook
Sends a workbook in an e-mail message for review to the specified recipients.
Dim wb As Workbook
wb.SendForReview Arguments:
- Recipients: A string that lists the people to whom to send the message.These can be unresolved names and aliases in an e-mail phone book or full e-mail addresses.Separate multiple recipients with a semicolon (;).If left blank and ShowMessage is False you will receive an error message and the message will not be sent.
- Subject: A string for the subject of the message.If left blank the subject will be: Please review filename.
- ShowMessage: A Boolean value that indicates whether the message should be displayed when the method is executed.The default value is True.If set to False the message is automatically sent to the recipients without first showing the message to the sender.
- IncludeAttachment: A Boolean value that indicates whether the message should include an attachment or a link to a server location.The default value is True.If set to False the document must be stored at a shared location.
SendMail Workbook
Sends the workbook by using the installed mail system.
Dim wb As Workbook
wb.SendMail Recipients:= Arguments:
- Recipients: Specifies the name of the recipient as text or as an array of text strings if there are multiple recipients.At least one recipient must be specified and all recipients are added as To recipients.
- Subject: Specifies the subject of the message.If this argument is omitted the document name is used.
- ReturnReceipt: True to request a return receipt.False to not request a return receipt.The default value is False.
SetLinkOnData Workbook
Sets the name of a procedure that runs whenever a DDE link is updated.
Dim wb As Workbook
wb.SetLinkOnData Name:= Arguments:
- Name: The name of the DDE/OLE link as returned from the LinkSources method.
- Procedure: The name of the procedure to be run when the link is updated.This can be either a Microsoft Excel 4.0 macro or a Visual Basic procedure.Set this argument to an empty string () to indicate that no procedure should run when the link is updated
SetPasswordEncryptionOptions Workbook
Sets the options for encrypting workbooks using passwords.
Dim wb As Workbook
wb.SetPasswordEncryptionOptions Arguments:
- PasswordEncryptionProvider: Optional
- PasswordEncryptionAlgorithm: Optional
- PasswordEncryptionKeyLength: Optional
- PasswordEncryptionFileProperties: Optional
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:
- Password: A string that denotes the case-sensitive password to use to unprotect the sheet or workbook. If the sheet or workbook isn't protected with a password this argument is ignored.If you omit this argument for a sheet that's protected with a password you'll be prompted for the password.If you omit this argument for a workbook that's protected with a password the method fails.
UnprotectSharing Workbook
Turns off protection for sharing and saves the workbook.
Dim wb As Workbook
wb.UnprotectSharing Arguments:
- SharingPassword: The workbook password.
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:
- Name: The name of the Microsoft Excel or DDE/OLE link to be updated as returned from theLinkSources method.
- Type: One of the constants of XlLinkType specifying the type of link.
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:
- Url: A uniform resource locator (URL) or a uniform naming convention (UNC) path to a XML data file.
- ImportMap: The schema map to apply when importing the file.If the data was previously imported contains a reference to the XmlMap object containing the data.
- Overwrite: If a value is not specified for the Destination parameter then this parameter specifies whether or not to overwrite data that has been mapped to the schema map specified in the ImportMap parameter.Set to True to overwrite the data orFalse to append the new data to the existing data.The default value is True. If a value is specified for the Destination parameter then this parameter specifies whether or not to overwrite existing data. Set to True to overwrite existing data or False to cancel the import if data would be overwritten.The default value isTrue.
- Destination: Specifies the range where the list will be created.You only use the top left corner of the range.
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:
- Data: The data to import.
- ImportMap: The schema map to apply when importing the file.
- Overwrite: If a value is not specified for the Destination parameter then this parameter specifies whether or not to overwrite data that has been mapped to the schema map specified in the ImportMap parameter.Set to True to overwrite the data orFalse to append the new data to the existing data. The default value is True.If a value is specified for the Destination parameter then this parameter specifies whether or not to overwrite existing data. Set to True to overwrite existing data or False to cancel the import if data would be overwritten.The default value isTrue.
- Destination: Specifies the range where the list will be created.Excel only uses the top left corner of the range.
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:
- Filename: The name of the file to check out.
CheckOut Workbook
Returns a String representing a specified workbook from a server to a local computer for editing.
Workbooks.CheckOut Filename:= Arguments:
- Filename: The name of the file to check out.