VBA Deleting file from One Drive

Question

I have code that saves file copy with .xlsx extension, then opens it and deletes old one. It works perfectly with files that are located only on my computer but if I try to use it with folders that are synced with OneDrive it gives me "File not found" error. It has to be because of that fact that wb.FullName in this case looks like web link to a file on OneDrive and not path+file name on my computer. Any ideas how to avoid this without moving files to another folder and then copying it back manually?

Sub Resave_and_open()

Dim fn As String
Dim fnold As String
Dim l As Long
Dim wb As Workbook

Set wb = ActiveWorkbook
fnold = wb.FullName
l = InStrRev(fnold, ".")
fn = Left(fnold, l)
fn = fn & "xlsx"

wb.SaveAs Filename:=fn, FileFormat:=xlOpenXMLWorkbook
wb.Close

Workbooks.Open Filename:=fn

Kill fnold 
End Sub

With files on my computer only it looks like this and works fine

Files from folder synced with OneDrive, does not work

https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive discusses converting http onedrive paths to local paths — Tim Williams, Jun 25 '21 at 16:00

score 0 · Answer 1 · answered Jun 25 '21 at 16:46

You can open files with HTTP address but you can not delete remote files without a proper HTTP-request, and the server should support that, too.

Anyway, as mentioned in comments, try to translate the OneDrive's URL to a Local-Path instead](https://stackoverflow.com/a/60990170/8740349).

VBA Deleting file from One Drive

1 Answers1