跨工作簿批量复制数据的VBA问题：关闭错误工作簿与内存优化

问题1：错误关闭并保存非目标工作簿 #

原因分析 #

代码中重复3次调用Workbooks.Open FileName:=file_name，导致同一源文件被打开3次。多余的打开操作会生成多个同名工作簿实例，关闭时易引发混淆；同时源文件未做修改却设置SaveChanges:=True，会触发不必要的保存操作。

修复步骤 #

• 删除多余的两次Workbooks.Open调用，仅保留一次并赋值给WB_From
• 关闭源文件时设置SaveChanges:=False（仅读取数据无需修改源文件）

问题2：大文件复制内存占用过高 #

替代方案 #

用单元格值直接赋值替代Copy方法，该方式跳过剪贴板，内存占用远低于复制粘贴，且执行速度更快，无需拆分复制。若需保留格式，可搭配PasteSpecial，纯数据场景优先使用值赋值。

核心代码实现 #

WS_To.Range("A1:A" & FromTbl_LastRow).Value = WS_From.Range("A1:A" & FromTbl_LastRow).Value

优化后的完整代码 #

Option Explicit
Sub OpenMasterSupplierFile()
    ' 关闭Excel冗余功能提升运行效率
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim WBname As String
    WBname = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
    
    Dim file_path As String
    Dim file_name As String
    Dim WS_FileLoc As Worksheet
    Set WS_FileLoc = ThisWorkbook.Sheets("CONFIG - File Locations") ' 明确绑定当前工作簿，避免歧义
    file_path = WS_FileLoc.Range("B2").Value
    file_name = file_path & "/" & "Master Supplier Price File.xlsx"

    ' 仅打开一次源工作簿并绑定变量
    Dim WB_From As Workbook
    Dim WS_From As Worksheet
    Set WB_From = Workbooks.Open(FileName:=file_name)
    Set WS_From = WB_From.Sheets("MASTER - Supplier Price File") ' 明确从属关系，防止引用错误

    Dim WB_To As Workbook
    Dim WS_To As Worksheet ' 修正类型为单个工作表
    Set WB_To = Workbooks(WBname)
    Set WS_To = WB_To.Sheets("MASTER - Supplier File") ' 明确从属关系

    ' 获取源数据最后一行
    Dim FromTbl_LastRow As Long
    FromTbl_LastRow = WS_From.Range("A" & WS_From.Rows.Count).End(xlUp).Row ' 绑定源工作表行数，避免跨工作簿引用错误

    ' 取消目标工作表保护（有密码则添加参数，如Unprotect "yourpassword"）
    WS_To.Unprotect

    ' 清除目标区域原有数据
    WS_To.Range("A1:A" & WS_To.Range("A" & WS_To.Rows.Count).End(xlUp).Row).ClearContents

    ' 直接赋值传递数据，替代复制粘贴
    WS_To.Range("A1:A" & FromTbl_LastRow).Value = WS_From.Range("A1:A" & FromTbl_LastRow).Value

    ' 关闭源工作簿，不保存修改
    WB_From.Close SaveChanges:=False

    ' 恢复Excel默认功能
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

额外优化说明 #

• 所有工作表引用均添加工作簿前缀，避免因活动工作簿切换导致的引用错误
• 修正WS_To类型定义为Worksheet，匹配单个工作表的使用场景
• 清除目标数据时绑定目标工作表行数，防止误操作其他区域

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