vba - 如何以编程方式在 Excel VBA 中的 Workbooks.Opentext 上定义 FieldInfo 参数？

Question

Workbooks.OpenText 方法采用名为 FieldInfo 的参数来确定每列中的格式类型。例如，以下代码将打开一个文本文件，并为第 1 列和第 2 列指定文本格式，并为第 3 列指定常规范式。

Workbooks.OpenText fileName:="C:\somefile.txt", _
    FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1))

假设在运行时我确定我的文本文件有 4 列，第 2 列和第 4 列应该是文本。我怎样才能以编程方式传递它？

Answer 1

根据documentation :

FieldInfo _{^{(optional, Variant)}}

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.

XlColumnDataType Enumeration

   Constant         Value    Description       
 ----------------- ------- ----------------------- 
  xlDMYFormat           4   DMY date format.       
  xlDYMFormat           7   DYM date format.       
  xlEMDFormat          10   EMD date format.       
  xlGeneralFormat       1   General.               
  xlMDYFormat           3   MDY date format.       
  xlMYDFormat           6   MYD date format.       
  xlSkipColumn          9   Column is not parsed.  
  xlTextFormat          2   Text.                  
  xlYDMFormat           8   YDM date format.       
  xlYMDFormat           5   YMD date format.

使用您的数据，如果第 2 列和第 4 列应该是 Text ，那些将是:

Array(2,2) 
Array(4,2)

您没有指定第 1 列和第 3 列，所以如果它们是 General那么整个数组将是:

FieldInfo:=Array(Array(1,1), Array(2,2), Array(3,1),Array(4,2))

...或者，为了清楚起见，您可以使用:

FieldInfo:=Array( _
                  Array(1,xlGeneralFormat), _
                  Array(2,xlTextFormat), _
                  Array(3,xlGeneralFormat), _
                  Array(4,xlTextFormat) _
                )

Notes:

• 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.

• 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). 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.

更多信息:

MSDN : Workbooks.OpenText Method (Excel)

MSDN : xlColumnDataType Enumeration (Excel)