使用Application.FileDialog复制数据时代码卡在工作表激活的问题求助

核心问题分析 #

1. sourceworkbook变量赋值错误：代码一开始就把当前工作簿赋值给sourceworkbook，打开目标文件后没有更新这个变量，导致后续操作的还是原工作簿，自然会触发激活失败的问题。
2. Workbooks.Open语法错误：正确写法是将打开的工作簿对象直接赋值给sourceworkbook，而非单独调用Open方法后不关联变量。
3. Application.Wait使用错误：Wait方法需要传入具体时间点（比如Now + TimeValue("00:00:03")），而非运行时间间隔；且Workbooks.Open本身是同步执行的，大文件加载慢的话，更应该关闭Excel冗余特性来提速，而非无效等待。
4. 冗余Activate/Select操作：这类操作依赖窗口焦点，容易出错，直接通过对象引用操作单元格更稳定高效。
5. 拼写错误：Cell("A1")应为Cells("A1")，或直接用Range("A1")。

修正后的代码 #

Sub Test()
    Dim sourceworkbook As Workbook
    Dim currentworkbook As Workbook
    Set currentworkbook = ThisWorkbook
    
    ' 关闭Excel冗余特性，加速大文件处理
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        If .Show = -1 Then ' Show返回-1代表用户选中了文件
            ' 将打开的工作簿直接赋值给sourceworkbook，锁定目标文件
            Set sourceworkbook = Application.Workbooks.Open(.SelectedItems(1))
            
            ' 直接完成复制粘贴，无需激活任何工作表
            sourceworkbook.Worksheets("sheet1").Range("D4:CM60000").Copy _
                Destination:=currentworkbook.Worksheets("sheet2").Range("A1")
            
            ' 关闭源工作簿，不保存更改
            sourceworkbook.Close SaveChanges:=False
        End If
    End With
    
    ' 恢复Excel默认设置
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
    ' 返回当前工作簿的sheet1并计算
    currentworkbook.Worksheets("sheet1").Calculate
    currentworkbook.Worksheets("sheet1").Range("A2").Select
    
    Set sourceworkbook = Nothing
    Set currentworkbook = Nothing
End Sub

关键优化说明 #

• 直接绑定Workbooks.Open的返回值到sourceworkbook，确保操作对象是选中的目标文件。
• 关闭屏幕更新、事件触发和自动计算，大幅降低大文件处理时的系统开销，避免后台计算导致的延迟。
• 完全移除Activate操作，通过对象引用直接完成复制粘贴，从根源解决激活相关的错误。
• 简化逻辑：Workbooks.Open会等待文件完全加载后再执行后续代码，若目标文件带有自动运行的宏，可在打开后添加DoEvents让Excel完成后台操作。

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