Class Workbooks (Excel VBA)

A collection of all the Workbook objects that are currently open in the Microsoft Excel application. To use a Workbooks class variable it first needs to be instantiated, for example

Dim wrks as Workbooks
Set wrks = Workbooks

For Each

Here is an example of processing the Workbooks items in a collection.

Dim wb As Workbook
For Each wb In Workbooks
	
Next wb

Add

Creates a new workbook. The new workbook becomes the active workbook.

If the Template argument specifies a file, the file name can include a path.

Add (Template)

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 following XlWBATemplate 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).

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

CanCheckOut

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

CanCheckOut (Filename)

Filename: The name of the file to check out.

Dim strFilename As String: strFilename = 
Dim booCanCheckOut As Boolean
booCanCheckOut = Workbooks.CanCheckOut(Filename:=strFilename)

CheckOut

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

CheckOut (Filename)

Filename: The name of the file to check out.

Dim strFilename As String: strFilename = 
Workbooks.CheckOut Filename:=strFilename

Close

Closes the object.

Closing a workbook from Visual Basic doesn't run any Auto_Close macros in the workbook. Use the RunAutoMacros method to run the auto close macros.

Workbooks.Close

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = Workbooks.Count

Item

Returns a single Workbook object from the collection.

Item (Index)

Index: The name or index number of the object.

Dim wbItem As Workbook
Set wbItem = Workbooks(Index:=1)

Open

Opens a workbook.

By default, macros are enabled when opening files programmatically. Use the AutomationSecurity property to set the macro security mode used when opening files programmatically. You can specify one of the following values in the UpdateLinks parameter to determine whether external references (links) are updated when the workbook is opened.

Open (Filename, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

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

Arguments

The following argument is required

Filename (String) - The file name of the workbook to be opened.

Optional arguments

The following arguments are optional

UpdateLinks - Specifies the way external references (links) in the file, such as the reference to a range in the Budget.xls workbook in the following formula =SUM([Budget.xls]Annual!C10:C25), are updated. If this argument is omitted, the user is prompted to specify how links will be updated. For more information about the values used by this parameter, see the Remarks section. If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 0, no charts are created; otherwise, Microsoft Excel generates charts from the graphs attached to the file

ReadOnly (Boolean) - True to open the workbook in read-only mode.

Format (String) - If Microsoft Excel opens a text file, this argument specifies the delimiter character. If this argument is omitted, the current delimiter is used. For more information about the values used by this parameter, see the Remarks section.

Password (String) - A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.

WriteResPassword - A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password

IgnoreReadOnlyRecommended (Boolean) - True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).

Origin (XlPlatform) - If the file is a text file, this argument indicates where it originated, so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly. Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.

Possible return values are xlMacintosh - Macintosh, xlMSDOS - MS-DOS, xlWindows - Microsoft Windows.

Delimiter (String) - If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use "," for commas, use ";" for semicolons, or use a custom character. Only the first character of the string is used.

Editable (Boolean) - If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it is a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option does not apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False.

Notify (Boolean) - If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.

Converter - The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter does not recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property

AddToMru (Boolean) - True to add this workbook to the list of recently used files. The default value is False.

Local - True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically United States English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project)

CorruptLoad (XlCorruptLoad) - Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The default behavior if no value is specified is xlNormalLoad, and does not attempt recovery when initiated through the OM.

Possible return values are xlExtractData - Workbook is opened in extract data mode, xlNormalLoad - Workbook is opened normally, xlRepairFile - Workbook is opened in repair mode.

OpenDatabase

Returns a Workbook object representing a database.

OpenDatabase (Filename, CommandText, CommandType, BackgroundQuery, ImportDataAs)

Dim strFilename As String: strFilename = 
Dim wbOpenDatabase As Workbook
Set wbOpenDatabase = Workbooks.OpenDatabase(Filename:=strFilename)

Arguments

The following argument is required

Filename (String) - The connection string that contains the location and file name of the database.

Optional arguments

The following arguments are optional

CommandText (String) - The command text of the query.

CommandType (XlCmdType) - The command type of the query. Specify one of the constants of the XlCmdType enumeration: xlCmdCube, xlCmdList, xlCmdSql, xlCmdTable, and xlCmdDefault.

Possible values are

BackgroundQuery (Boolean) - This parameter is a variant data type but you can only pass a Boolean value. If you pass True, the query is performed in the background (asynchronously). The default value is False.

ImportDataAs (XlImportDataAs) - This parameter uses one of the values of the XlImportDataAs enumeration. The two values of this enum are xlPivotTableReport and xlQueryTable. Pass one of these values to return the data as a PivotTable or QueryTable. The default value is xlQueryTable.

Possible return values are xlPivotTableReport - Returns the data as a PivotTable, xlQueryTable - Returns the data as a QueryTable, xlTable.

OpenText

Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.

FieldInfo parameter You can use xlEMDFormat only if you have installed and selected Taiwanese language support. The xlEMDFormat constant specifies that Taiwanese era dates are being used. The column specifiers can be in any order. If there's no column specifier for a particular column in the input data, the column is parsed with the General setting. This example causes the third column to be parsed as MDY (for example, 01/10/1970), the first column to be parsed as text, and the remaining columns in the source data to be parsed with the General setting.

OpenText (Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

Dim strFilename As String: strFilename = 
Workbooks.OpenText Filename:=strFilename

Arguments

The following argument is required

Filename (String) - Specifies the file name of the text file to be opened and parsed.

Optional arguments

The following arguments are optional

Origin (XlPlatform) - Specifies the origin of the text file. Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. Additionally, this could be an integer representing the code page number of the desired code page. For example, "1256" would specify that the encoding of the source text file is Arabic (Windows). If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

Possible return values are xlMacintosh - Macintosh, xlMSDOS - MS-DOS, xlWindows - Microsoft Windows.

StartRow (Long) - The row number at which to start parsing text. The default value is 1.

DataType (XlTextParsingType) - Specifies the column format of the data in the file. Can be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.

Possible return values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

TextQualifier (XlTextQualifier) - Specifies the text qualifier.

Possible return values are xlTextQualifierDoubleQuote - Double quotation mark ("), xlTextQualifierNone - No delimiter, xlTextQualifierSingleQuote - Single quotation mark (').

ConsecutiveDelimiter (Boolean) - True to have consecutive delimiters considered one delimiter. The default is False.

Tab (Tab) - True to have the tab character be the delimiter (DataType must be xlDelimited). The default value is False.

Semicolon (XlTextParsingType) - True to have the semicolon character be the delimiter (DataType must be xlDelimited). The default value is False.

Possible return values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

Comma (XlTextParsingType) - True to have the comma character be the delimiter (DataType must be xlDelimited). The default value is False.

Possible return values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

Space (XlTextParsingType) - True to have the space character be the delimiter (DataType must be xlDelimited). The default value is False.

Possible return values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

Other (XlTextParsingType) - True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). The default value is False.

Possible return values are xlDelimited - Default. Indicates that the file is delimited by delimiter characters, xlFixedWidth - Indicates that the data in the file is arranged in columns of fixed widths.

OtherChar (String) - Required if Other is True. Specifies the delimiter character when Other is True. If more than one character is specified, only the first character of the string is used; the remaining characters are ignored.

FieldInfo (XlColumnDataType) - An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

Here you can find possible values for XlColumnDataType

TextVisualLayout - The visual layout of the text

DecimalSeparator (String) - The decimal separator that Microsoft Excel uses when recognizing numbers. The default setting is the system setting.

ThousandsSeparator (String) - The thousands separator that Excel uses when recognizing numbers. The default setting is the system setting.

TrailingMinusNumbers (Boolean) - Specify True if numbers with a minus character at the end should be treated as negative numbers. If False or omitted, numbers with a minus character at the end are treated as text.

Local (Boolean) - Specify True if regional settings of the machine should be used for separators, numbers and data formatting.

OpenXML

Opens an XML data file. Returns a Workbook object.

OpenXML (Filename, Stylesheets, LoadOption)

Dim strFilename As String: strFilename = 
Dim wbOpenXML As Workbook
Set wbOpenXML = Workbooks.OpenXML(Filename:=strFilename)

Arguments

The following argument is required

Filename (String) - The name of the file to open.

Optional arguments

The following arguments are optional

Stylesheets (String) - Either a single value or an array of values that specify which XSL Transformation (XSLT) stylesheet processing instructions to apply.

LoadOption (XlXmlLoadOption) - Specifies how Excel opens the XML data file. Can be one of the XlXmlLoadOption constants.

Possible values are