如何用VBA导出含公式工作表为无原工作簿引用的xlsx文件

当你从xlsm宏工作簿复制工作表到xlsx文件时，Excel会自动给公式添加原工作簿的外部引用，以下是几种可行的解决方法：

方法1：复制工作表后批量移除外部引用 #

先完整复制工作表，再用批量替换清除公式中的原工作簿标识：

Sub MigrateSheetWithoutExternalRefs()
    Dim sourceWB As Workbook
    Dim targetWB As Workbook
    Dim targetSheet As Worksheet
    Dim formulaRange As Range
    
    ' 定义源工作簿（当前宏工作簿）
    Set sourceWB = ThisWorkbook
    ' 新建空白xlsx工作簿
    Set targetWB = Workbooks.Add(xlWBATWorksheet)
    
    ' 复制目标工作表到新工作簿
    sourceWB.Worksheets("price-compare").Copy After:=targetWB.Worksheets(1)
    Set targetSheet = targetWB.Worksheets("price-compare")
    
    ' 定位所有包含公式的单元格
    On Error Resume Next
    Set formulaRange = targetSheet.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    
    ' 批量替换公式中的原工作簿引用
    If Not formulaRange Is Nothing Then
        formulaRange.Replace What:="[billing-compare.v.1.0.xlsm]", Replacement:="", _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    End If
    
    ' 保存并关闭新工作簿
    targetWB.SaveAs Filename:="C:\YourPath\NewFile.xlsx", FileFormat:=xlOpenXMLWorkbook
    targetWB.Close SaveChanges:=False
End Sub

注意：替换的[billing-compare.v.1.0.xlsm]要和原工作簿的实际名称完全一致，如果需要适配不同名称，可以用sourceWB.Name动态获取。

方法2：直接复制粘贴公式与数据（避免外部引用） #

不复制整个工作表，而是复制单元格内容后粘贴公式和格式，让公式自动使用新工作簿的本地引用：

Sub CopyDataAndFormulasLocally()
    Dim sourceWB As Workbook
    Dim targetWB As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    
    Set sourceWB = ThisWorkbook
    Set sourceSheet = sourceWB.Worksheets("price-compare")
    
    ' 新建xlsx工作簿并命名目标工作表
    Set targetWB = Workbooks.Add(xlWBATWorksheet)
    Set targetSheet = targetWB.Worksheets(1)
    targetSheet.Name = "price-compare"
    
    ' 复制源工作表所有内容
    sourceSheet.Cells.Copy
    ' 粘贴公式与数字格式
    targetSheet.Cells.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
    ' 粘贴列宽保持布局一致
    targetSheet.Cells.PasteSpecial Paste:=xlPasteColumnWidths
    
    Application.CutCopyMode = False
    
    ' 保存并关闭
    targetWB.SaveAs Filename:="C:\YourPath\NewFile.xlsx", FileFormat:=xlOpenXMLWorkbook
    targetWB.Close SaveChanges:=False
End Sub

方法3：迁移所有关联工作表（适用于跨表引用场景） #

如果你的公式引用了原工作簿内的其他工作表（比如示例中的raw-data），将所有关联工作表一起复制到新工作簿，Excel会自动更新引用为本地：

Sub MigrateRelatedSheets()
    Dim sourceWB As Workbook
    Dim targetWB As Workbook
    
    Set sourceWB = ThisWorkbook
    Set targetWB = Workbooks.Add(xlWBATWorksheet)
    
    ' 批量复制关联工作表
    sourceWB.Worksheets(Array("price-compare", "raw-data")).Copy After:=targetWB.Worksheets(1)
    
    ' 删除新工作簿默认的空白工作表
    Application.DisplayAlerts = False
    targetWB.Worksheets("Sheet1").Delete
    Application.DisplayAlerts = True
    
    ' 保存并关闭
    targetWB.SaveAs Filename:="C:\YourPath\NewFile.xlsx", FileFormat:=xlOpenXMLWorkbook
    targetWB.Close SaveChanges:=False
End Sub

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