Excel宏打开首个工作簿后停止运行问题求助

问题背景 #

我编写了一个Excel VBA宏，用于批量处理工作簿：遍历工作表中存储的文件名列表，依次打开模板CSV工作簿和目标XLSX工作簿，调用子宏完成内容复制后另存为CSV，关闭工作簿后处理下一个文件。其中第二个和第三个子宏单独测试均可正常运行，但主宏在打开第一个工作簿后便停止运行。

原代码 #

Dim fpath, fname, path, wname, tempname, fpath2 As String
Dim mainwkbk, trgwkbk, srcwkbk As Workbook
Dim main_ws As Worksheet
Dim main_lr, name_col As Integer
Dim q As Long

Set mainwkbk = Workbooks("title1.xlsx")
Set main_ws = mainwkbk.Worksheets(1)

main_ws.Activate
main_lr = main_ws.Cells(Rows.Count, 10).End(xlUp).Row
fpath = Range("I2")

For q = 2 To main_lr
    tempname = "_template_name.csv") ' 语法错误：多余右括号
    fpath2 = "C:\temp\locaiton\" ' 拼写错误：location写成locaiton
    Workbooks.Open(fpath2 & tempname)
    Set trgwkbk = Workbooks(tempname)

    fname = main_ws.Cells(q, 10).Value
    path = fpath & fname & ".xlsx"
    Workbooks.Open(path)
    Set srcwkbk = Workbooks(fname & ".xlsx")

    2nd_macro (fname)

Next q

排查与修复步骤 #

1. 修正语法与拼写错误

• 删掉tempname = "_template_name.csv")末尾的多余右括号；
• 修正路径拼写错误：C:\temp\locaiton\改为C:\temp\location\；
• VBA中多变量声明需逐个指定类型，否则仅最后一个变量为指定类型，其余为Variant：

  Dim fpath As String, fname As String, path As String, wname As String, tempname As String, fpath2 As String
  Dim mainwkbk As Workbook, trgwkbk As Workbook, srcwkbk As Workbook
  Dim main_lr As Integer, name_col As Integer
  

• 指定Range("I2")所属工作表，避免激活其他工作表时引用错误：

  fpath = main_ws.Range("I2").Value
  

2. 加入错误捕获与资源清理

在循环中加入错误处理，捕获文件不存在、路径无效等问题，同时确保每次循环后关闭已打开的工作簿：

For q = 2 To main_lr
    On Error GoTo LoopErrorHandler
    Set trgwkbk = Nothing
    Set srcwkbk = Nothing

    tempname = "_template_name.csv"
    fpath2 = "C:\temp\location\"
    Set trgwkbk = Workbooks.Open(fpath2 & tempname)

    fname = main_ws.Cells(q, 10).Value
    path = fpath & fname & ".xlsx"
    Set srcwkbk = Workbooks.Open(path)

    ' 建议将工作簿对象作为参数传给子宏，避免名称引用冲突
    2nd_macro trgwkbk, srcwkbk

    ' 保存处理后的CSV并关闭工作簿
    trgwkbk.SaveAs Filename:=fpath2 & "processed_" & fname & ".csv", FileFormat:=xlCSV
    trgwkbk.Close SaveChanges:=False
    srcwkbk.Close SaveChanges:=False

LoopContinue:
    Next q
    Exit Sub

LoopErrorHandler:
    MsgBox "处理文件 " & fname & " 出错：" & Err.Description
    ' 清理已打开的工作簿
    If Not trgwkbk Is Nothing Then trgwkbk.Close SaveChanges:=False
    If Not srcwkbk Is Nothing Then srcwkbk.Close SaveChanges:=False
    Resume LoopContinue

3. 优化子宏参数传递

修改2nd_macro，直接接收工作簿对象作为参数，避免依赖工作簿名称或激活状态：

Sub 2nd_macro(trgWB As Workbook, srcWB As Workbook)
    ' 子宏逻辑：直接使用trgWB和srcWB操作，无需激活
    ' 示例：复制srcWB的列到trgWB
    srcWB.Worksheets(1).Columns("A:C").Copy trgWB.Worksheets(1).Range("A1")
    ' 调用第三个宏时也传递工作簿对象
    3rd_macro trgWB, srcWB
End Sub

4. 移除不必要的激活操作

删除main_ws.Activate，所有单元格操作直接通过main_ws引用，避免激活状态变化导致的错误。

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