VBA保存工作簿副本后无法在新文件执行粘贴值操作求助

我明白你的困扰——代码能生成新文件名的XLSX，但粘贴值的操作只在原文件生效，而且你需要的是完全无宏、无查询的纯数据副本。问题的核心是：你的代码在另存文件后，没有切换操作上下文到新生成的工作簿，所有编辑操作还是针对原文件在执行。

下面给你一套完整的解决方案，包含转值、清理查询、确保无宏的完整逻辑：

关键修复点 #

• 另存文件后，必须获取新工作簿的对象引用，所有后续操作都基于这个对象
• 批量处理新工作簿的所有工作表，将公式转成值
• 清理新工作簿中的Power Query连接（如果有的话）
• 最后保存并关闭新工作簿，避免干扰原文件

完整VBA代码示例 #

Sub SaveAsCleanXLSX()
    Dim originalWB As Workbook
    Dim newWB As Workbook
    Dim newFileName As String
    Dim ws As Worksheet
    Dim conn As WorkbookConnection
    
    ' 保存原工作簿引用，避免后续操作混淆
    Set originalWB = ThisWorkbook
    
    ' 生成新文件名（这里可以替换成你自己的命名逻辑）
    newFileName = originalWB.Path & "\CleanCopy_" & Format(Now(), "YYYYMMDD_HHMMSS") & ".xlsx"
    
    ' 1. 另存为无宏的XLSX格式
    originalWB.SaveAs Filename:=newFileName, FileFormat:=xlOpenXMLWorkbook
    ' 获取新工作簿的引用
    Set newWB = Workbooks.Open(newFileName)
    
    ' 2. 将新工作簿所有工作表的内容转成值
    For Each ws In newWB.Worksheets
        ' 选中整个已使用区域（如果需要指定范围可以修改）
        ws.UsedRange.Copy
        ' 粘贴为值和格式（只保留值的话可以用xlPasteValues）
        ws.UsedRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        ' 清除剪贴板，避免弹窗
        Application.CutCopyMode = False
    Next ws
    
    ' 3. 清理新工作簿中的Power Query连接（如果存在）
    For Each conn In newWB.Connections
        If conn.Type = xlConnectionTypeOLEDB And conn.OLEDBConnection.Connect Like "*Power Query*" Then
            conn.Delete
        End If
    Next conn
    
    ' 4. 保存并关闭新工作簿
    newWB.Save
    newWB.Close SaveChanges:=False
    
    ' 提示操作完成
    MsgBox "无宏无查询的副本已生成：" & vbCrLf & newFileName, vbInformation
    
    ' 回到原工作簿
    originalWB.Activate
End Sub

代码说明 #

• Set newWB = Workbooks.Open(newFileName)：这是关键！通过这行代码获取新工作簿的对象，后续所有操作都针对newWB，而不是原工作簿
• 转值逻辑：遍历新工作簿的每个工作表，将已使用区域的内容粘贴为值和格式，确保没有公式残留
• 清理查询：检查并删除Power Query相关的连接，避免新文件携带查询缓存
• 文件格式：xlOpenXMLWorkbook就是标准的XLSX格式，本身不支持宏，所以另存后自动去除宏

测试的时候，建议先备份原文件，确保代码执行后原文件不受影响。

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