VBA删除OneDrive同步文件夹文件提示File not found错误如何解决
问题原因
OneDrive同步状态下的文件,VBA的Workbook.FullName属性会返回云端共享链接,而Kill语句仅能识别本地磁盘路径,因此会抛出「File not found」错误。
解决方案
新增辅助函数自动将OneDrive云端链接转换为本地磁盘路径,修改后的完整可运行代码如下:
' 辅助函数:获取工作簿的本地磁盘绝对路径,兼容OneDrive同步文件 Function GetLocalFilePath(wb As Workbook) As String Dim oneDrivePath As String Dim wbPath As String Dim relativePath As String Dim pathStartPos As Long ' 已经是本地路径的情况直接返回 If Left(wb.FullName, 4) <> "http" Then GetLocalFilePath = wb.FullName Exit Function End If ' 读取系统环境变量中的OneDrive本地根目录,依次适配商业版、个人版、通用标识 oneDrivePath = Environ("OneDriveCommercial") If oneDrivePath = "" Then oneDrivePath = Environ("OneDriveConsumer") If oneDrivePath = "" Then oneDrivePath = Environ("OneDrive") wbPath = wb.FullName ' 匹配OneDrive URL中相对路径的起始位置,默认适配Documents同步目录 pathStartPos = InStr(wbPath, "/Documents/") + Len("/Documents/") ' 若匹配不到Documents可替换为下方代码适配商业版SharePoint路径 ' pathStartPos = InStr(wbPath, ".sharepoint.com/") + Len(".sharepoint.com/") ' 提取相对路径并将URL斜杠替换为本地路径反斜杠 relativePath = Mid(wbPath, pathStartPos) relativePath = Replace(relativePath, "/", "\") ' 拼接得到完整本地路径 GetLocalFilePath = oneDrivePath & "\" & relativePath End Function ' 修改后的主程序 Sub Resave_and_open() Dim fn As String Dim fnold As String Dim l As Long Dim wb As Workbook Set wb = ActiveWorkbook ' 调用辅助函数获取本地路径,解决OneDrive返回URL的问题 fnold = GetLocalFilePath(wb) l = InStrRev(fnold, ".") fn = Left(fnold, l - 1) & ".xlsx" wb.SaveAs Filename:=fn, FileFormat:=xlOpenXMLWorkbook wb.Close SaveChanges:=False Workbooks.Open Filename:=fn ' 若偶发文件占用报错,可在下一行前加 DoEvents 等待系统释放文件句柄 Kill fnold End Sub
注意事项
- 如果你的OneDrive同步根目录不是默认的
Documents文件夹,可根据实际路径修改辅助函数中的匹配规则 - 商业版OneDrive如果适配失败,可替换辅助函数中
pathStartPos的取值逻辑,使用注释中适配SharePoint路径的代码即可
内容的提问来源于stack exchange,提问作者Lika
相关产品推荐
相关产品推荐

