Fixing VBA errors caused by “OneDrive” autosave and temp file paths.

When a macro fails with Runtime Error 52: Bad file name or number, Runtime Error 76: Path not found, or crashes during automated saves, the failure is frequently triggered by Microsoft OneDrive integration. When an Excel workbook syncs to OneDrive or SharePoint, ThisWorkbook.Path stops returning a local disk path (such as C:\Users\...) and instead returns a web URL (such as https://company.sharepoint.com/...). Native VBA file operations and Scripting libraries cannot process HTTP addresses as local file paths.

Quick Answer

Convert the cloud URL returned by ThisWorkbook.Path back into a local system path before passing it to native file functions. Alternatively, retrieve the local sync root directly using the OneDrive or OneDriveCommercial environment variables (Environ("OneDrive")), and construct your local file paths using standard folder structures.

---

The Underlying Conflict: URLs vs. Local File Systems

Legacy VBA file commands—including Dir(), Kill, FileCopy, Name, and native I/O statements (Open ... For Input)—were built for local DOS/Windows file structures. When cloud sync is active, Excel shifts the file context to the cloud endpoint to enable real-time co-authoring and AutoSave.

Expected Local Path:  C:\Users\Analyst\OneDrive - Company\Reports\Data.xlsx
Actual Path Returned: <https://company.sharepoint.com/personal/analyst/Documents/Reports/Data.xlsx>

When code passes an https:// address to a function like Dir(ThisWorkbook.Path & "\data.csv"), the operating system rejects the call, resulting in Runtime Error 53: File not found (Dir function failures). Similarly, if your script attempts to use the file system object on that web address, it fails because FSO requires local drive semantics.

---

First-Line Fix: Resolving the Local OneDrive Path

The most direct way to bypass URL errors is to resolve the local synchronization path using Windows environment variables instead of relying on ThisWorkbook.Path.

Solution 1: Use Environment Variables for Local Folder Access

If you are generating, reading, or saving external files in a synchronized OneDrive folder, build the target path using the local user profile:

Function GetLocalOneDrivePath() As String
    Dim oneDrivePath As String

    ' Check for corporate/business OneDrive first, then consumer
    oneDrivePath = Environ("OneDriveCommercial")
    If Len(oneDrivePath) = 0 Then
        oneDrivePath = Environ("OneDriveConsumer")
    End If
    If Len(oneDrivePath) = 0 Then
        oneDrivePath = Environ("OneDrive")
    End If

    If Len(oneDrivePath) > 0 Then
        GetLocalOneDrivePath = oneDrivePath & "\"
    Else
        ' Fallback to UserProfile if OneDrive variables are unmapped
        GetLocalOneDrivePath = Environ("USERPROFILE") & "\OneDrive\"
    End If
End Function

You can then reliably access local files:

Sub SaveLocalCSV()
    Dim localFilePath As String
    localFilePath = GetLocalOneDrivePath() & "Exports\Summary.csv"

    ' Works without triggering URL runtime errors
    Open localFilePath For Output As #1
    Print #1, "ID,Status,Timestamp"
    Close #1
End Sub

Solution 2: Translate Cloud URLs to Local Paths Dynamically

If your code must dynamically reference the exact folder where the active workbook lives, you must detect whether ThisWorkbook.Path starts with http and strip the web root:

Function ConvertCloudPathToLocal(ByVal cloudPath As String) As String
    If InStr(1, cloudPath, "https://", vbTextCompare) = 1 Then
        Dim localRoot As String
        Dim relativePath As String
        Dim slashPos As Long

        localRoot = Environ("OneDriveCommercial")
        If Len(localRoot) = 0 Then localRoot = Environ("OneDrive")

        ' Locate the start of the relative folder path after the tenant/library URL
        ' Adjust the slice based on your tenant's library naming (typically after /Documents/)
        slashPos = InStr(1, cloudPath, "/Documents/", vbTextCompare)
        If slashPos > 0 Then
            relativePath = Mid(cloudPath, slashPos + 11)
            relativePath = Replace(relativePath, "/", "\")
            ConvertCloudPathToLocal = localRoot & "\" & relativePath
            Exit Function
        End If
    End If

    ' Return original path if not a cloud URL
    ConvertCloudPathToLocal = cloudPath
End Function

---

Handling AutoSave Collisions and Temporary Files

OneDrive’s AutoSave feature creates two specific operational barriers during macro execution: background file locks and temp file collisions.

1. Background Sync Locks during .SaveAs

When AutoSave is active, Excel continuously syncs deltas to the cloud. If a macro attempts to execute Workbook.Save or Workbook.SaveAs while a background sync thread is writing metadata, execution halts with Runtime Error 1004: “Cannot access the file ‘filename.xlsx'” (Pathing issues).

• The Diagnostic Fix: Turn off AutoSave programmatically on the specific workbook object during script execution:

Sub BatchUpdate()
    ' Temporarily suspend AutoSave for this file
    If ThisWorkbook.AutoSaveOn Then
        ThisWorkbook.AutoSaveOn = False
    End If

    ' Perform data edits and local save
    ThisWorkbook.Save

    ' Re-enable AutoSave
    ThisWorkbook.AutoSaveOn = True
End Sub

If AutoSave turns off permanently or shows a persistent error icon, consult Troubleshooting “AutoSave” being disabled automatically.

2. Temp File Collisions (~$ Ghost Files)

When scanning a synced directory with Dir(), OneDrive’s background syncing engine frequently generates hidden temporary owner files prefixed with ~$. If your macro loops through a folder opening every .xlsx file it encounters, opening a lock file causes immediate corruption warnings.

• The Preventative Filter: Explicitly filter out temporary lock files in all search loops:

Sub ProcessFolderFiles(folderPath As String)
    Dim currentFile As String
    currentFile = Dir(folderPath & "\*.xlsx")

    Do While Len(currentFile) > 0
        ' Ignore hidden lock and temp files
        If Left(currentFile, 2) <> "~$" Then
            Dim targetWb As Workbook
            Set targetWb = Workbooks.Open(folderPath & "\" & currentFile)
            ' Process workbook...
            targetWb.Close SaveChanges:=False
        End If
        currentFile = Dir()
    Loop
End Sub

If your automated process fails with sync conflict dialogs or identity authorization requests, address the connection credentials using Fixing “Upload Blocked” and “Sign in required” for OneDrive/SharePoint files.