多次运行后激活最后创建的Excel工作簿问题求助

核心问题在于你依赖ActiveWorkbook或Workbooks.Count来定位新建的工作簿，这种方式在Excel实例存在多个工作簿时，或UI响应延迟时（自由运行宏时常见）会出现引用错误。最可靠的解决方法是直接持有新建工作簿的对象引用，全程绑定到目标工作簿，彻底避免依赖活动对象的不确定性。

代码修改方案 #

1. 初始化Excel与创建工作簿时绑定引用 #

修改初始化部分代码，直接保存新建工作簿的引用，替代原来的ActiveWorkbook调用：

'set up an new instance of Excel, or if Excel is not running, start it
On Error Resume Next
Set Xl = GetObject(, "Excel.application")
If Err <> 0 Then
    On Error GoTo 0
    Set Xl = CreateObject("Excel.Application")
    If Err <> 0 Then
        MsgBox "Excel application is not available on this workstation" _
            & vbCr & "Install Excel or check network connection", vbCritical, _
            "Project Data Export - Fatal Error"
        FilterApply Name:="all tasks"
        Set Xl = Nothing
        On Error GoTo 0     'clear error function
        Exit Sub
    End If
End If
On Error GoTo 0

'创建工作簿并保存直接引用
Dim NewWB As Object
Set NewWB = Xl.Workbooks.Add
NewWB.Worksheets(1).Name = "Resource Information"
Set WS1 = NewWB.Worksheets(1)
NewWB.Worksheets(2).Name = "Monthly Data"
Set WS2 = NewWB.Worksheets(2)

'后台运行Excel，关闭屏幕更新提升速度
Xl.Visible = False
Xl.ScreenUpdating = False
Xl.DisplayAlerts = False

2. 格式化阶段直接使用绑定的引用 #

修改格式化代码，无需再通过Workbooks.Count定位，直接用NewWB操作目标工作簿：

'格式化工作表
Xl.ScreenUpdating = True
Xl.Visible = True

'激活本次创建的工作簿
NewWB.Activate

'直接使用已绑定的WS1、WS2引用执行格式化
WS1.Columns("A:F").AutoFit
WS1.Rows(2).Activate
Xl.ActiveWindow.FreezePanes = True
WS1.Range("A1").Activate

WS2.Activate
WS2.UsedRange.Columns.AutoFit
WS2.Range("C3").Activate
Xl.ActiveWindow.FreezePanes = True

TotTim = Timer - TimSt
Xl.Visible = False

MsgBox "Export is complete" & vbCr & _
    "   Export time: " & TotTim & " sec", vbInformation
Xl.Visible = True
NewWB.Activate

'清理对象引用
Set WS1 = Nothing
Set WS2 = Nothing
Set NewWB = Nothing
Set Xl = Nothing
End Sub

关键改进点 #

• 绑定工作簿引用：通过Set NewWB = Xl.Workbooks.Add，全程确保操作的是本次创建的工作簿，不受Excel中其他工作簿的干扰。
• 优化屏幕更新：初始化时关闭ScreenUpdating，格式化前恢复，提升宏运行速度并避免屏幕闪烁。
• 减少依赖活动对象：用Activate替代Select，既保留冻结窗格的功能，又降低因活动对象切换导致的错误概率。

内容的提问来源于stack exchange，提问作者john-project