Workbooks.OpenText 方法 (Excel)Workbooks.OpenText method (Excel)

以具有單一工作表 (包含已剖析文字檔資料) 的活頁簿形式載入及剖析文字檔。Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.

語法Syntax

運算式OpenTextFileName原點StartRowDataTypeTextQualifierConsecutiveDelimiter] 索引標籤分號逗號空間其他OtherCharFieldInfo TextVisualLayoutDecimalSeparatorThousandsSeparatorTrailingMinusNumbers本機)expression.OpenText (FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

_運算式_代表**Workbooks** 物件的變數。expression A variable that represents a Workbooks object.

參數Parameters

名稱Name 必要/選用Required/Optional 資料類型Data type 描述Description
FileNameFileName 必要Required StringString 會指定要開啟並剖析的文字檔名稱。Specifies the file name of the text file to be opened and parsed.
OriginOrigin 選用Optional VariantVariant Specifies the origin of the text file.Specifies the origin of the text file. 可以是下列其中一個**XlPlatform** 常數: xlMacintosh省略本引數xlMSDOSCan 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.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).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.
StartRowStartRow 選用Optional VariantVariant 要開始剖析文字的列號。The row number at which to start parsing text. 預設值為 1。The default value is 1.
DataTypeDataType 選用Optional VariantVariant 指定檔案中資料的欄格式。Specifies the column format of the data in the file. 可以為下列其中一個 XlTextParsingType 常數: xlDelimitedxlFixedWidthCan be one of the following XlTextParsingType constants: xlDelimited or xlFixedWidth. 如果未指定此引數,Microsoft Excel 在開啟檔案時會嘗試判斷欄格式。If this argument is not specified, Microsoft Excel attempts to determine the column format when it opens the file.
TextQualifierTextQualifier 選用Optional XlTextQualifierXlTextQualifier 會指定文字辨識符號。Specifies the text qualifier.
ConsecutiveDelimiterConsecutiveDelimiter 選用Optional VariantVariant 若為 True ,將連續分隔符號視為一個分隔符號處理。True to have consecutive delimiters considered one delimiter. 預設值為 FalseThe default is False.
TabTab 選用Optional VariantVariant 若為 True,使定位字元成為分隔符號 (DataType 必須是 xlDelimited)。True to have the tab character be the delimiter (DataType must be xlDelimited). 預設值為 FalseThe default value is False.
SemicolonSemicolon 選用Optional VariantVariant 若為 True,使分號字元成為分隔符號 (DataType 必須是 xlDelimited)。True to have the semicolon character be the delimiter (DataType must be xlDelimited). 預設值為 FalseThe default value is False.
CommaComma 選用Optional VariantVariant 若為 True,使逗號字元成為分隔符號 (DataType 必須是 xlDelimited)。True to have the comma character be the delimiter (DataType must be xlDelimited). 預設值為 FalseThe default value is False.
SpaceSpace 選用Optional VariantVariant 若為 True,使空格字元成為分隔符號 (DataType 必須是 xlDelimited)。True to have the space character be the delimiter (DataType must be xlDelimited). 預設值為 FalseThe default value is False.
其他Other 選用Optional VariantVariant 則為true ,使_OtherChar_引數所指定的字元成為的分隔符號 (DataType 必須是xlDelimited)。True to have the character specified by the OtherChar argument be the delimiter (DataType must be xlDelimited). 預設值為 FalseThe default value is False.
OtherCharOtherChar 選用Optional VariantVariant 如果被必要的_其他_則為 TrueRequired if Other is True. _其他_為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.
FieldInfoFieldInfo 選用Optional VariantVariant 包含個別資料欄剖析資訊的陣列。An array containing parse information for individual columns of data. 解譯取決於_DataType_的值。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. 第一個元素是欄號碼 (1 為基礎),和第二個元素是其中一個**XlColumnDataType** 常數,指定如何剖析欄。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.
TextVisualLayoutTextVisualLayout 選用Optional VariantVariant 文字的視覺配置。The visual layout of the text.
DecimalSeparatorDecimalSeparator 選用Optional VariantVariant Microsoft Excel 在識別數字時使用的小數分隔符號。The decimal separator that Microsoft Excel uses when recognizing numbers. 預設值為系統設定。The default setting is the system setting.
ThousandsSeparatorThousandsSeparator 選用Optional VariantVariant Excel 使用的是千分位分隔符號。The thousands separator that Excel uses when recognizing numbers. 預設值為系統設定。The default setting is the system setting.
TrailingMinusNumbersTrailingMinusNumbers 選用Optional VariantVariant 如果必須將結尾字元為負號的數字視為負數,則會指定 TrueSpecify True if numbers with a minus character at the end should be treated as negative numbers. 如果為 False 或省略,則會將結尾字元為負號的數字視為文字。If False or omitted, numbers with a minus character at the end are treated as text.
LocalLocal 選用Optional VariantVariant 如果分隔符號、 數字及資料格式應該使用電腦的地區設定,則指定 ,則為 TrueSpecify True if regional settings of the machine should be used for separators, numbers and data formatting.

註解Remarks

_FieldInfo_參數FieldInfo parameter

如果您安裝並選定台灣版的語言支援,只可使用 xlEMDFormatYou can use xlEMDFormat only if you have installed and selected Taiwanese language support. xlEMDFormat 常數指定使用台灣年代資料。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.

本範例導致第三欄被剖析為 MDY (例如,01/10/1970)、第一欄被剖析為文字,來源資料中的剩餘欄會用 [一般] 設定來剖析。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.

Array(Array(3, 3), Array(1, 2))

如果來源資料具有固定寬度欄,則每個二元素陣列的第一個元素,會指定起始字元在欄中的位置 (用整數表示,第一個字元為 0 (零))。If the source data has fixed-width columns, the first element in each two-element array specifies the position of the starting character in the column (as an integer; character 0 (zero) is the first character). 二元素陣列的第二個元素,會用 0 到 9 之間的數字指定欄的剖析選項,如先前表格所列。The second element in the two-element array specifies the parse option for the column as a number between 0 and 9, as listed in the preceding table.

_ThousandsSeparator_參數ThousandsSeparator parameter

以下表格所示為使用不同匯入設定將文字匯入 Excel 中的結果。The following table shows the results of importing text into Excel for various import settings. 數值結果顯示在最右方的欄中。Numeric results are displayed in the rightmost column.

系統小數點分隔字元System decimal separator 系統千分位分隔符號System thousands separator 小數分隔符號值Decimal separator value 千分位分隔符號值Thousands separator value 匯入的文字Text imported 儲存格的值 (資料類型)Cell value (data type)
期間Period 逗號Comma 逗號Comma 期間Period 123.123,45123.123,45 123,123.45 (數值)123,123.45 (numeric)
期間Period 逗號Comma 逗號Comma 逗號Comma 123.123,45123.123,45 123.123,45 (文字)123.123,45 (text)
逗號Comma 間隔Period 間隔Period 逗號Comma 123,123.45123,123.45 123,123.45 (數值)123,123.45 (numeric)
期間Period 逗號Comma 期間Period 逗號Comma 123 123.45123 123.45 123,123.45 (文字)123 123.45 (text)
間隔Period 逗號Comma 間隔Period 空格Space 123 123.45123 123.45 123,123.45 (數值)123,123.45 (numeric)

範例Example

本範例會開啟 Data.txt 檔案,並使用定位分隔符號將該文字檔案剖析至工作表。This example opens the file Data.txt and uses tab delimiters to parse the text file into a worksheet.

Workbooks.OpenText filename:="DATA.TXT", _ 
    dataType:=xlDelimited, tab:=True

支援和意見反應Support and feedback

有關於 Office VBA 或這份文件的問題或意見反應嗎?Have questions or feedback about Office VBA or this documentation? 如需取得支援服務並提供意見反應的相關指導,請參閱 Office VBA 支援與意見反應Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.