VBA - 从关闭的工作簿复制数据的最佳方法

VBA - 从关闭的工作簿复制数据的最佳方法

Sub CopyDataFromClosedWorkbook()
    Dim srcWorkbook As Object
    Dim destWorkbook As Workbook
    Dim srcWorksheet As Object
    Dim destWorksheet As Worksheet
    
    ' 创建一个Excel对象
    Set srcWorkbook = CreateObject("Excel.Application")
    
    ' 打开目标工作簿
    srcWorkbook.Workbooks.Open "C:\目标工作簿路径.xlsx"
    
    ' 将目标工作簿的引用分配给对象变量
    Set destWorkbook = srcWorkbook.Workbooks("目标工作簿名")
    
    ' 设置源工作表和目标工作表
    Set srcWorksheet = destWorkbook.Sheets("源工作表名")
    Set destWorksheet = ThisWorkbook.Sheets("目标工作表名")
    
    ' 复制数据
    srcWorksheet.UsedRange.Copy destWorksheet.Range("A1")
    
    ' 关闭工作簿
    destWorkbook.Close False
    
    ' 释放对象变量
    Set srcWorksheet = Nothing
    Set destWorksheet = Nothing
    Set destWorkbook = Nothing
    
    ' 退出Excel应用程序
    srcWorkbook.Quit
    Set srcWorkbook = Nothing
End Sub

Sub CopyDataFromClosedWorkbook()
    Dim srcWorkbook As Workbook
    Dim destWorkbook As Workbook
    Dim srcWorksheet As Worksheet
    Dim destWorksheet As Worksheet
    Dim filePath As Variant
    
    ' 选择关闭的工作簿
    filePath = Application.GetOpenFilename("Excel文件(*.xlsx), *.xlsx")
    
    ' 打开目标工作簿
    Set srcWorkbook = Workbooks.Open(filePath)
    
    ' 设置源工作表和目标工作表
    Set srcWorksheet = srcWorkbook.Sheets("源工作表名")
    Set destWorksheet = ThisWorkbook.Sheets("目标工作表名")
    
    ' 复制数据
    srcWorksheet.UsedRange.Copy destWorksheet.Range("A1")
    
    ' 关闭工作簿
    srcWorkbook.Close False
    
    ' 释放对象变量
    Set srcWorksheet = Nothing
    Set destWorksheet = Nothing
    Set destWorkbook = Nothing
End Sub