VBA脚本运行报错：下标越界（错误9），求CSV导入Excel解决方案

问题背景 #

我有一个包含12个工作表（如DecisionEvent、invoices）的Excel主模板，每个工作表都带有列标题。每周需手动将2个CSV文件的数据导入对应工作表，CSV文件的表头与模板匹配（仅其中一个工作表末尾多一列）。CSV文件命名规则示例：3724_DecisionEventWFAD_20230521060003、3725_DecisionEventWFCH_20230521060010。

需求：在DecisionEvent工作表中，先检查源文件夹内文件名含DecisionEventWFAD的CSV文件，将其所有数据复制到首空行；再检查文件名含DecisionEventWFCH的CSV文件，同样将数据复制到首空行。

现有如下VBA脚本，但在语句Set decisionEventSheet = ThisWorkbook.Sheets("DecisionEvent")处出现运行时错误9（下标越界），请协助解决：

Sub ImportDataFromCSV()
    Dim sourceFolder As String
    Dim decisionEventSheet As Worksheet
    Dim csvFile As String
    Dim csvFilePath As String
    Dim lastRow As Long
    Dim ws As Worksheet
    
    ' 设置源文件夹路径
    sourceFolder = " C:\Users\Juan_Sans_Eros\Documents\Data Extract import test"
    
    ' 设置DecisionEvent工作表的引用
    Set decisionEventSheet = ThisWorkbook.Sheets("DecisionEvent")
    
    ' 检查源文件夹中的每个文件
    csvFile = Dir(sourceFolder & "\*DecisionEventWFAD*.csv")
    Do While csvFile <> ""
    csvFilePath = sourceFolder & "\" & csvFile
        
    ' 打开CSV文件
    Workbooks.Open Filename:=csvFilePath
        
    ' 将数据复制到DecisionEvent工作表
    Set ws = ActiveWorkbook.Sheets(1)
    lastRow = decisionEventSheet.Cells(decisionEventSheet.Rows.Count, 1).End(xlUp).Row + 1
    ws.UsedRange.Copy decisionEventSheet.Cells(lastRow, 1)
        
    ' 关闭CSV文件，不保存更改
    ActiveWorkbook.Close SaveChanges:=False
        
    ' 查找下一个CSV文件
    csvFile = Dir
    Loop
    
    ' 检查源文件夹中的每个文件
    csvFile = Dir(sourceFolder & "\*DecisionEventWFCH*.csv")
    Do While csvFile <> ""
    csvFilePath = sourceFolder & "\" & csvFile
        
    ' 打开CSV文件
    Workbooks.Open Filename:=csvFilePath
        
    ' 将数据复制到DecisionEvent工作表
    Set ws = ActiveWorkbook.Sheets(1)
    lastRow = decisionEventSheet.Cells(decisionEventSheet.Rows.Count, 1).End(xlUp).Row + 1
    ws.UsedRange.Copy decisionEventSheet.Cells(lastRow, 1)
        
    ' 关闭CSV文件，不保存更改
    ActiveWorkbook.Close SaveChanges:=False
        
    ' 查找下一个CSV文件
    csvFile = Dir
    Loop
    
    MsgBox "数据导入成功。"
End Sub

错误原因及解决办法 #

1. 工作表名称不匹配 #

• 检查主模板中是否存在名为DecisionEvent的工作表，注意排查拼写错误（比如多空格、字母大小写差异）。
• 可运行以下代码确认当前工作簿的所有工作表名称：

Sub ListAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        Debug.Print ws.Name
    Next ws
End Sub

运行后打开VBA编辑器的立即窗口（快捷键Ctrl+G）查看输出的工作表名称。

2. 工作簿引用错误 #

• 确保代码在主模板工作簿中运行，ThisWorkbook指向的是包含当前VBA代码的工作簿，如果代码保存在其他文件中，会找不到目标工作表。
• 也可以替换为明确的工作簿名称，比如主模板文件名为DataTemplate.xlsx，则修改为：

Set decisionEventSheet = Workbooks("DataTemplate.xlsx").Sheets("DecisionEvent")

注意需确保该工作簿已打开。

3. 工作表被隐藏或保护 #

• 如果DecisionEvent工作表被隐藏（包括非常隐藏），右键点击工作表标签选择取消隐藏查看；若工作表被保护，需先解除保护再操作。

优化后的完整代码 #

同时修复了原代码的细节问题（比如文件夹路径开头的空格、避免使用ActiveWorkbook、跳过CSV表头）：

Sub ImportDataFromCSV()
    Dim sourceFolder As String
    Dim decisionEventSheet As Worksheet
    Dim csvFile As String
    Dim csvFilePath As String
    Dim lastRow As Long
    Dim csvWB As Workbook
    Dim ws As Worksheet
    
    ' 修正文件夹路径：去掉开头的空格
    sourceFolder = "C:\Users\Juan_Sans_Eros\Documents\Data Extract import test"
    
    ' 先确认工作表存在，避免错误
    On Error Resume Next
    Set decisionEventSheet = ThisWorkbook.Sheets("DecisionEvent")
    On Error GoTo 0
    
    If decisionEventSheet Is Nothing Then
        MsgBox "未找到名为DecisionEvent的工作表，请检查工作表名称！", vbCritical
        Exit Sub
    End If
    
    ' 导入DecisionEventWFAD的CSV文件
    csvFile = Dir(sourceFolder & "\*DecisionEventWFAD*.csv")
    Do While csvFile <> ""
        csvFilePath = sourceFolder & "\" & csvFile
        
        ' 打开CSV文件，避免依赖ActiveWorkbook
        Set csvWB = Workbooks.Open(Filename:=csvFilePath)
        Set ws = csvWB.Sheets(1)
        
        ' 获取目标表首空行，跳过CSV表头（从第2行开始复制）
        lastRow = decisionEventSheet.Cells(decisionEventSheet.Rows.Count, 1).End(xlUp).Row + 1
        ws.UsedRange.Offset(1).Copy decisionEventSheet.Cells(lastRow, 1)
        
        ' 关闭CSV文件
        csvWB.Close SaveChanges:=False
        
        csvFile = Dir
    Loop
    
    ' 导入DecisionEventWFCH的CSV文件
    csvFile = Dir(sourceFolder & "\*DecisionEventWFCH*.csv")
    Do While csvFile <> ""
        csvFilePath = sourceFolder & "\" & csvFile
        
        Set csvWB = Workbooks.Open(Filename:=csvFilePath)
        Set ws = csvWB.Sheets(1)
        
        lastRow = decisionEventSheet.Cells(decisionEventSheet.Rows.Count, 1).End(xlUp).Row + 1
        ws.UsedRange.Offset(1).Copy decisionEventSheet.Cells(lastRow, 1)
        
        csvWB.Close SaveChanges:=False
        
        csvFile = Dir
    Loop
    
    MsgBox "数据导入成功。"
End Sub

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