excel - Workbooks.OpenText 忽略 FieldInfo 列参数

Question

我有以下行来导入 csv 格式文件。

 Workbooks.OpenText Filename:=sPath, DataType:=xlDelimited, Comma:=True, FieldInfo:=Array(Array(18, 5), Array(19, 5)), Local:=True

来自微软的文档 here ，如果 FieldInfo 是分隔的，则不必按任何顺序。

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.

但是，无论我在第一个参数中输入什么内容，Excel似乎都会将第一个数组视为第一列，将第二个数组视为第二列Array(Array(x, 5), Array(y, 5)).因此，要到达第 18 和 19 列，我必须这样做，这不太漂亮:

Workbooks.OpenText Filename:=sPath, DataType:=xlDelimited, Comma:=True, _
    FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), _
    Array(3, 1), _
    Array(4, 1), _
    Array(5, 1), _
    Array(6, 1), _
    Array(7, 1), _
    Array(8, 1), _
    Array(9, 1), _
    Array(10, 1), _
    Array(11, 1), _
    Array(12, 1), _
    Array(13, 1), _
    Array(14, 1), _
    Array(15, 1), _
    Array(16, 1), _
    Array(17, 1), _
    Array(18, 5), _
    Array(19, 5)), _
    Local:=True

csv 文件示例数据:

fill_c1,pick_n2,po_num3,quanti4,addres5,cust_s6,color_7,size_d8,style9,shipto10,shipto11,addres12,addres13,city14,state15,zipcod16,custom17,start_18,end_da19,udford20
"52","1","2","000000001","000000000000000000000000000000","6","Z","XS","7","","","","","","","","M",20190310,20190318,"CF3"
"52","1","2","000000002","000000000000000000000000000000","6","Z","S","7","","","","","","","","M","20190310","20190318","CF3"

Answer 1

我能够使用 .txt 文件并使用 Tab=True 重现此问题:

Workbooks.OpenText Filename:=Path & "Testfile.txt", DataType:=xlDelimited, Tab:=True, FieldInfo:=Array(Array(18, 9), Array(19, 9)), Local:=True

通过使用值 9 (xlSkipColumn)，我尝试省略第 18 列和第 19 列，但跳过了第 1 列和第 2 列(Test1 和 Test2):

documentation for 明确表示“列说明符可以按任何顺序”，但这看起来是不正确的。第一个数组的第一个元素始终是第 1 列，第二个数组的第一个元素始终是第 2 列，迭代完所有数组后，其余列将使用常规设置进行解析。

IMO，这对我来说看起来像是一个错误。如果它不是一个错误，那么文档就会非常困惑，需要重新编写。