修改VBA代码更新现有xlsx文件，解决Power Automate与SharePoint异常

问题背景 #

使用宏启用工作簿（xlsm）捕获记录数据，但Power Automate对xlsm格式有限制，因此生成xlsx格式副本。原方案采用删除重建同名xlsx文件的方式，导致SharePoint中文件每次生成新唯一ID，引发Power Automate“List Row in a Table”动作的引用错误。

修改后的VBA代码 #

Sub UpdateXlsx()
    ' 文件存放路径
    Dim path As String
    path = "C:\Users\user\Testing\"
    
    ' 目标xlsx文件名
    Dim targetFile As String
    targetFile = "Test.xlsx"
    Dim fullPath As String
    fullPath = path & targetFile
    
    Dim targetWorkbook As Workbook
    Dim targetSheet As Worksheet
    
    Application.DisplayAlerts = False
    
    ' 判断文件是否存在
    If Dir(fullPath) <> "" Then
        ' 打开现有xlsx文件
        Set targetWorkbook = Workbooks.Open(fullPath)
        Set targetSheet = targetWorkbook.Sheets(1)
        ' 清空工作表所有内容（若需清除格式可替换为.Clear）
        targetSheet.UsedRange.ClearContents
    Else
        ' 文件不存在则新建工作簿
        Set targetWorkbook = Workbooks.Add
        Set targetSheet = targetWorkbook.Sheets(1)
    End If
    
    ' 复制原工作表数据到目标文件
    ThisWorkbook.Sheet1.UsedRange.Copy
    targetSheet.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats ' 按需选择粘贴类型
    targetSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats ' 需保留格式可添加此行
    
    ' 保存并关闭目标文件
    targetWorkbook.SaveAs Filename:=fullPath, FileFormat:=xlOpenXMLWorkbook
    targetWorkbook.Close SaveChanges:=False
    
    Application.CutCopyMode = False ' 清除剪贴板
    Application.DisplayAlerts = True
End Sub

关键修改说明 #

• 移除Kill删除逻辑：不再删除原有文件，避免SharePoint生成新的文件唯一ID，保证Power Automate的引用一致性
• 复用现有文件：优先打开已存在的xlsx文件，清空内容后重新写入数据，保留原文件的SharePoint属性
• 灵活粘贴选项：通过PasteSpecial控制粘贴内容（值、格式等），可根据需求调整
• 兼容首次运行：处理文件不存在的场景，自动新建文件，不影响首次执行流程

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