Excel VBA动态导入可变列数CSV报错下标越界问题咨询

错误根源 #

• TextFileColumnDataTypes属性要求传入下标从1开始的数组，你循环从0开始生成的是0基数组，传入时索引不匹配触发下标越界错误
• 逐次ReDim Preserve属于冗余逻辑，不仅执行效率极低，也容易出现索引偏差
• 第二个子程序同时混用Sheet1（代码名）和Sheets("QuestionnaireData")（表名），如果二者不是同一个工作表会引发额外的范围引用错误

修复后完整代码 #

Sub ImportCSV()
    Dim column_types() As Variant
    Dim csv_path As Variant
    Dim i As Long
    
    csv_path = Application.GetOpenFilename("CSV文件(*.csv),*.csv", , "选择要导入的CSV文件")
    ' 处理用户取消选择的情况
    If csv_path = False Then Exit Sub
    
    ' 直接声明1基数组，无需循环重定义
    ReDim column_types(1 To 16384)
    For i = 1 To 16384
        column_types(i) = 2 ' xlTextFormat，所有列导入为文本格式
    Next i
    
    With ActiveWorkbook.Sheets(1).QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Range("A1"))
        .Name = "importCSVimporter"
        .FieldNames = True
        .AdjustColumnWidth = True
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = column_types
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Sheets(1).QueryTables("importCSVimporter").Delete
    
    ' 导入完成直接调用转置子程序，无需手动触发
    Call TransposeRawData
End Sub

Sub TransposeRawData()
    Dim ws As Worksheet
    Dim lastCol As Long
    
    ' 统一工作表引用，避免跨表错误，可根据实际表名调整
    Set ws = ThisWorkbook.Sheets("QuestionnaireData")
    
    With ws
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        ' 直接通过行列数定义范围，无需拼接地址字符串
        .Range("A3").Resize(lastCol, 2).Value = WorksheetFunction.Transpose(.Range("A1").Resize(2, lastCol))
        ' 一次性删除前两行
        .Rows("1:2").Delete
    End With
End Sub

优化说明 #

• 新增了文件选择校验逻辑，用户点取消时不会触发报错
• 移除冗余的数组重定义逻辑，执行效率大幅提升
• 统一了工作表引用，避免表名和代码名混用导致的错误
• 去掉了不必要的列号转字母逻辑，直接通过行列数操作范围，代码稳定性更高
• 新增了导入完成自动调用转置子程序的逻辑，减少手动操作步骤

内容的提问来源于stack exchange，提问作者lsmetpitt