如何通过VBA或R删除OneDrive中被锁定的同步文件

我有一份包含VBA宏的Excel文件，设计了如下自动化执行流程：

1. 将名为SavingsExport的工作表复制到全新的空白工作簿
2. 将新生成的工作簿保存到指定OneDrive路径
3. 关闭这个新生成的临时文件
4. 触发运行R脚本，读取临时文件中的数据写入业务数据库
5. 执行删除操作清理临时文件

故障现象 #

代码运行后始终无法完成最后一步的文件删除操作：执行完关闭文件的步骤后，本地系统已经显示文件物理关闭，但OneDrive服务端始终判定文件处于占用打开状态，阻止删除操作。手动登录OneDrive网页端执行删除时会弹出如下警告：

It looks like someone has this file open. Are you sure you want to delete?
手动点击确认后可以正常删除，但无论用VBA还是R代码都无法自动完成删除，文件锁始终无法被正常释放。

VBA侧尝试 #

• 方案1：使用SharePoint在线URL完成文件保存、打开、关闭操作后，调用Kill命令删除，代码如下：

'Copy Savings Export (Step 1 from Above)
Sheets("SavingsExport").Select
Sheets("SavingsExport").Copy
       
'Save file to OneDrive (Step 2 from Above)
ActiveWorkbook.SaveAs Filename:=FPath & fName1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Set DestinWB = Workbooks.Open("https://ORG.sharepoint.com/teams/EPSDataAnalytics/Shared Documents/DART App/Temp Files/SavingsExport.xlsx")

'Close the temporary file (Step 3 from Above)
DestinWB.Close SaveChanges:=True
      
'Run R Code (Step 4 from Above)
Irrelevant here... Removed for brevity

'Delete Temp File (Step 5 from Above)
Kill ("https://ORG.sharepoint.com/teams/EPSDataAnalytics/Shared Documents/DART App/Temp Files/SavingsExport.xlsx")

• 方案2：替换为本地映射的OneDrive磁盘路径执行相同的保存、打开、关闭、删除逻辑，代码如下：

'Copy Savings Export (Step 1 from Above)
Sheets("SavingsExport").Select
Sheets("SavingsExport").Copy

'Save file to OneDrive (Step 2 from Above)
ActiveWorkbook.SaveAs Filename:=FPath & fName1, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Set DestinWB = Workbooks.Open("C:\UserData\" & UserName() & "\Siemens AG\EPS Data Analytics - Documents\DART App\Temp Files\SavingsExport.xlsx")

'Close the temporary file (Step 3 from Above)
DestinWB.Close SaveChanges:=True
      
'Run R Code (Step 4 from Above)
Irrelevant here... Removed for brevity

'Delete Temp File (Step 5 from Above)
Kill ("C:\UserData\" & UserName() & "\Siemens AG\EPS Data Analytics - Documents\DART App\Temp Files\SavingsExport.xlsx")

• 方案3：调整保存关闭逻辑，直接在Close方法中指定保存路径，同时添加5秒Application.Wait延时等待文件同步完成后再执行后续步骤，代码如下：

'Copy Savings Export (Step 1 from Above)
Sheets("SavingsExport").Select
Sheets("SavingsExport").Copy

'Save and close Temp file (Steps 2 & 3 from Above)
Application.Wait (Now + TimeValue("0:00:05"))
ActiveWorkbook.Close _
SaveChanges:=True, _
Filename:="https://ORG.sharepoint.com/teams/EPSDataAnalytics/Shared Documents/DART App/Temp Files/SavingsExport.xlsx"
Application.Wait (Now + TimeValue("0:00:05"))

'Run R Code (Step 4 from Above)
Irrelevant here... Removed for brevity

'Delete Temp File (Step 5 from Above)
Kill ("https://ORG.sharepoint.com/teams/EPSDataAnalytics/Shared Documents/DART App/Temp Files/SavingsExport.xlsx")

R侧尝试 #

尝试在R脚本中调用file.remove函数执行删除，测试了所有能想到的UNC路径、URL路径写法，所有写法均返回[1] FALSE并提示Invalid argument，测试的路径写法如下：

file.remove(paste("\\\\ORG.sharepoint.com\\teams\\EPSDataAnalytics\\Shared%20Documents\\DART%20App\\Temp%20Files\\SavingsExport.xlsx", sep = ""))

file.remove(paste("https:\\\\ORG.sharepoint.com\\teams\\EPSDataAnalytics\\Shared%20Documents\\DART%20App\\Temp%20Files\\SavingsExport.xlsx", sep = ""))

file.remove(paste("\\\\ORG.sharepoint.com\\teams\\EPSDataAnalytics\\Shared Documents\\DART App\\Temp Files\\SavingsExport.xlsx", sep = ""))

file.remove(paste("https:\\\\ORG.sharepoint.com\\teams\\EPSDataAnalytics\\Shared Documents\\DART App\\Temp Files\\SavingsExport.xlsx", sep = ""))

file.remove(paste("//ORG.sharepoint.com/teams/EPSDataAnalytics/Shared%20Documents/DART%20App/Temp%20Files/SavingsExport.xlsx", sep = ""))

file.remove(paste("https://ORG.sharepoint.com/teams/EPSDataAnalytics/Shared%20Documents/DART%20App/Temp%20Files/SavingsExport.xlsx", sep = ""))

file.remove(paste("//ORG.sharepoint.com/teams/EPSDataAnalytics/Shared Documents/DART App/Temp Files/SavingsExport.xlsx", sep = ""))

file.remove(paste("https://ORG.sharepoint.com/teams/EPSDataAnalytics/Shared Documents/DART App/Temp Files/SavingsExport.xlsx", sep = ""))

以上所有方案均无法实现OneDrive同步临时文件的自动删除，现寻求可落地的技术解决方案。

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