excel - 计算查询表vba问题中的列

Question

我之前在链接上的这个主题中有一个关于导入和导出 csv 的问题： 当通过 vba 导入 csv 时将数字类型更改为文本类型 当我添加此代码时问题就开始了

.TextFileColumnDataTypes = Array(2, 2, _
                            2, 2, 2, 2, 2, 2, 2, 2, 2, 2)

示例：我有一个如下的 csv：

"a","b","c","e","f","g","h","i","j","k"

"1","2","3","4","5","6","7","8","9","10"

当我导出 csv 时，输出文件是这样的：

"a","b","c","e","f","g","h","i","j","k","",""

"1","2","3","4","5","6","7","8","9","10","",""

该脚本声明 12 列的类型为文本类型。我必须用多种 csv 来解决，在这个例子中，当我导出它时，结果 csv 在标题中有空白值（如果标题列在 12 以下）

我们可以在这里看到，当我以静态方式声明列的类型时（而不是列的可变类型）。为此，我必须获取由 QueryTable 导入的列数。

我已经使用此代码来获取列数：

ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress)).ResultRange.Columns.Count

但这行不通。

这是我在下面的代码：

Dim xFileName As Variant
Dim Rg As Range
Dim xAddress As String
xFileName = Application.GetOpenFilename("CSV File (*.csv), *.csv", , , , 
False)
If xFileName = False Then Exit Sub
On Error Resume Next
xAddress = Range("A1").Address

With ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 65001
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileTrailingMinusNumbers = True
    .TextFileColumnDataTypes = Array(2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
                                    2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
    .Refresh BackgroundQuery:=False
End With

我希望我可以以可变方式声明列，例如

Dim ExArr As Variant
Dim UsedColumns As Long
'
'This script to get number of Columns as UsedColumns
'
For i = 0 to UsedColumns
ExArr(i) = 2
Next i 
 With ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress))
.TextFileColumnDataTypes = Array(ExArr)
End With

标签： excelvba