Working With Other Workbooks And Files

PARAMETER

DESCRIPTION

UpdateLinks

Specifies how to handle links within the workbook. Type 0 for no updates, 1 to update external references, and 2 to update remote references. A value of 3 updates external and remote references.

Format

Indicates the delimiter character in the text file to separate data into cells in a worksheet.

Value Delimiter Value Delimiter

1 Tabs 4 Semicolons

2 Commas 5 Nothing

3 Spaces 6 Delimiter parameter value

Origin

Indicates original platform of text files and has three constant values.

xlMacintosh, xlWindows, and xlMSDOS. If omitted, Excel uses the current operating system.

Delimiter

Specifies a delimiter character when the Format parameter has a value of 6.

Editable

Type True to view an Excel Add-in or to edit an Excel template.

Notify

Use True to add a file that cannot be opened as Read/Write to the notification list.

Converter

Index of a file converter to use when opening a file.

Local

Use True to save file using the language being used by Excel. False saves the file using the language used by VBA.

CorruptLoad

Indicates method used to retry a corrupt load of a file. xlNormalLoad, xlRepairFile, and xl<Extra>ctData are the three options.

OpenConflictDocument

True opens the local conflict document.

Conflict Name Excel Workbook

—0 Type FileName:="WorkbookName"

replacing WorkbookName with the name and path of the workbook to open.

■ You can type a comma and a space and then type optional parameters that you want to include with the Open method.

□ Switch to Excel and run the macro.

■ The specified workbook opens as the active workbook.

—0 Type FileName:="WorkbookName"

replacing WorkbookName with the name and path of the workbook to open.

■ You can type a comma and a space and then type optional parameters that you want to include with the Open method.

□ Switch to Excel and run the macro.

Note: See Chapter 1 to run a macro.

■ The specified workbook opens as the active workbook.

OPEN A TEXT FILE AS A WORKBOOK

You can open a text file within Excel using the OpenText method of the Workbooks collection. When you use this method, Excel opens the text file as a single worksheet within a new workbook. The file remains a text file, but you can modify it using Excel as the editor.

You can specify how the text file opens using the parameters associated with the OpenText method. The list of parameters for this method is pretty extensive, but only the FileName parameter is actually required to specify the name of the text file to open.

When you use the FileName parameter, you typically want the name to include the complete path of the file to ensure that Excel locates the file. If you place the workbook activating the macro in a different location than the text file, Excel cannot locate it.

Because the list of parameters is so extensive with the OpenText method, you should use named parameters with the method to eliminate the need to specify all parameters. With named parameters, you can indicate the name of each parameter along with the associated value, for example: Workbooks.OpenText FileName:="C:\Excel Files\Sample.txt", DataType:+ xlDelimited, Tab:=True.

This code opens the text file using the tab character as the delimiter. The delimiter is the character that indicates a separation of data. In this file, the tab indicates that the data following the tab should be placed in the next cell. As illustrated, the OpenText method has parameters for the standard delimiter characters. If you know the delimiter for the text file, you can specify the delimiter character to ensure that the text file opens correctly.

OPEN A TEXT FILE AS A WORKBOOK

OPEN A TEXT FILE AS A WORKBOOK

—n Create a new subroutine

—0 Type Workbooks.OpenText.

—n Create a new subroutine

—0 Type Workbooks.OpenText.

Was this article helpful?

0 0

Responses

Post a comment