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

When Excel workbooks are stored in OneDrive or SharePoint folders, VBA macros that handle file paths, check directory contents, or save files often fail with Runtime Error 53: File not found, Runtime Error 70: Permission denied, or Runtime Error 1004. These failures happen because OneDrive replaces traditional local drive paths (C:\Users\...) with cloud HTTPS URLs (https://d.docs.live.net/... or SharePoint company URLs) while AutoSave creates background locks and temporary upload sync states.

Quick Fix:

To prevent path-related crashes, convert the cloud URL returned by ThisWorkbook.Path back into your local synchronized file path using Windows environment variables (Environ("OneDrive") or Environ("UserProfile")). For saving or overwriting operations, temporarily disable AutoSave in code using ActiveWorkbook.AutoSaveOn = False before saving, or use native URL-compatible Workbook methods instead of local file system commands.

Mechanics: Why OneDrive Breaks Standard VBA Code

Traditional VBA file routines rely on standard Windows file system conventions:

• Local physical drive paths formatted with backslashes (C:\Folder\File.xlsx).
• Direct, synchronous read/write access managed entirely by the local operating system.

When a workbook is synced to OneDrive or SharePoint, Excel prioritizes cloud co-authoring:

[ Traditional Local Path ] ──► C:\Users\Username\OneDrive\Reports\Data.xlsx
                                      │ (OneDrive Cloud Sync Active)
                                      ▼
[ Excel Application Path ] ──► <https://company.sharepoint.com/sites/Team/Shared> Documents/Data.xlsx

Because ThisWorkbook.Path or ActiveWorkbook.FullName returns an HTTPS web URL instead of a local disk path, legacy VBA functions fail:

1. Dir(), GetAttr(), and File I/O (Open ... For Input): These native functions cannot parse https:// protocol strings and immediately throw Runtime Error 53: File not found or Bad file name or number. For more on general Dir failures, see Runtime Error 53: File not found (Dir function failures).
2. Scripting.FileSystemObject: The Windows Scripting Host does not support web endpoints, returning Runtime Error 76: Path not found.
3. AutoSave & Temp Sync Files: When AutoSave is active, OneDrive continuously syncs document micro-updates. If your macro attempts to rename, copy, or overwrite the active file using FileCopy or Kill, OneDrive’s background process holds a sync lock, triggering Runtime Error 70: Permission denied.

Diagnostic Flowchart: Distinguishing the OneDrive Error

What is your VBA macro trying to do when it fails?
 │
 ├── Checking if a file/folder exists (`Dir`, `GetAttr`, `FSO.FileExists`)
 │     └── Symptom: `Runtime Error 53` or `Runtime Error 76` on `ThisWorkbook.Path`
 │     └── Root Cause: `ThisWorkbook.Path` returned an `https://` URL.
 │     └── Solution: Convert the HTTPS URL to a local disk path.
 │
 ├── Opening a secondary workbook (`Workbooks.Open`)
 │     ├── Target path is an `https://` URL
 │     │     └── Solution: `Workbooks.Open` supports URLs natively, but requires URL encoding for spaces (`%20`).
 │     └── Target path uses `Dir()` validation first
 │           └── Solution: Remove the `Dir()` check or test the resolved local path.
 │
 ├── Saving, exporting, or overwriting (`SaveAs`, `FileCopy`, `Kill`)
 │     ├── Error: `Runtime Error 1004` on SaveAs
 │     │     └── Solution: Turn off `AutoSaveOn` or resolve cloud upload sync conflicts.
 │     └── Error: `Runtime Error 70` on FileCopy/Kill
 │           └── Solution: Target local temp directory (`Environ("Temp")`) instead of the synced OneDrive folder.
 │
 └── Opening an automated temporary backup file (~$filename.xlsx)
       └── Root Cause: OneDrive synced a temporary owner lock file.
       └── Solution: Filter out files starting with `~$`.

Practical Solutions & Code Fixes

1. Converting OneDrive HTTPS URLs to Local Disk Paths

If your macro needs to use Dir(), FileSystemObject, or shell commands with files located in the same folder as your workbook, convert the web path back to the actual local folder path on your computer.

Use this drop-in conversion function:

VBA

Function GetLocalWorkbookPath(wb As Workbook) As String
    Dim rawPath As String
    Dim oneDrivePath As String
    Dim relativePath As String

    rawPath = wb.Path

    ' Check if the path is an HTTPS URL (OneDrive / SharePoint)
    If InStr(1, rawPath, "https://", vbTextCompare) = 1 Then
        ' Retrieve local OneDrive sync root from environment
        oneDrivePath = Environ("OneDriveCommercial")
        If oneDrivePath = "" Then oneDrivePath = Environ("OneDriveConsumer")
        If oneDrivePath = "" Then oneDrivePath = Environ("OneDrive")

        ' Parse out the relative folder path following the site/library structure
        ' Example extracts everything after the 4th forward slash
        Dim slashPos As Long, i As Long
        slashPos = 0
        For i = 1 To 4
            slashPos = InStr(slashPos + 1, rawPath, "/")
            If slashPos = 0 Then Exit For
        Next i

        If slashPos > 0 Then
            relativePath = Mid(rawPath, slashPos)
            relativePath = Replace(relativePath, "/", Application.PathSeparator)
            relativePath = Replace(relativePath, "%20", " ")
            GetLocalWorkbookPath = oneDrivePath & relativePath
        Else
            GetLocalWorkbookPath = oneDrivePath
        End If
    Else
        ' Already a standard local path (C:\...)
        GetLocalWorkbookPath = rawPath
    End If
End Function

Now, instead of calling Dir(ThisWorkbook.Path & "\data.csv"), call:

VBA

Dim localFolder As String
localFolder = GetLocalWorkbookPath(ThisWorkbook)
If Dir(localFolder & Application.PathSeparator & "data.csv") <> "" Then
    ' Process local file safely
End If

2. Managing AutoSave During Macro Saves

When macros programmatically save an active workbook, the background AutoSave engine can clash with Workbook.Save or Workbook.SaveAs, resulting in Runtime Error 1004: Method 'SaveAs' of object '_Workbook' failed.

To bypass this conflict, inspect and toggle the AutoSaveOn property:

VBA

Sub SafeCloudSave()
    Dim wasAutoSaveOn As Boolean

    ' Check if AutoSave is supported and enabled
    On Error Resume Next
    wasAutoSaveOn = ActiveWorkbook.AutoSaveOn
    If wasAutoSaveOn Then
        ActiveWorkbook.AutoSaveOn = False
    End If
    On Error GoTo 0

    ' Perform macro modifications and save
    ActiveWorkbook.Save

    ' Restore AutoSave state if desired
    On Error Resume Next
    If wasAutoSaveOn Then
        ActiveWorkbook.AutoSaveOn = True
    End If
    On Error GoTo 0
End Sub

If your macro encounters generic save failures outside of AutoSave conflicts, see Runtime Error 1004: “SaveAs method of Workbook class failed.” If cloud sync conflicts cause broader saving locks, review Fixing “File was not uploaded because changes cannot be merged” in OneDrive.

3. Handling Temporary Files in the Local %TEMP% Folder

Writing temporary CSVs, PDF exports, or scratch files directly into a synchronized OneDrive folder forces the OneDrive sync client to continuously index and upload temporary files. This leads to file-locking conflicts (Runtime Error 70: Permission denied).

The Rule: Never store temporary macro artifacts in synced folders. Always write scratch data to the user’s local Windows temp directory:

VBA

Sub WriteTempFile()
    Dim tempFolder As String
    Dim tempFilePath As String
    Dim fileNum As Integer

    ' Target the unsynced local Windows Temp directory
    tempFolder = Environ("Temp")
    tempFilePath = tempFolder & Application.PathSeparator & "scratch_data.txt"

    fileNum = FreeFile
    Open tempFilePath For Output As #fileNum
    Print #fileNum, "Temporary data payload"
    Close #fileNum

    ' Safely delete after processing
    If Dir(tempFilePath) <> "" Then
        Kill tempFilePath
    End If
End Sub

4. Filtering Out Ghost Owner Files (~$)

When Excel opens a cloud-synced file, it generates a hidden lock file prefixed with ~$ (e.g., ~$Report.xlsx). If your macro iterates through a folder using Dir("*.xlsx") to process multiple files, it will inevitably attempt to open these locked temporary strings, causing file corruption or open errors.

The Fix: Always skip temporary lock files in your iteration loops:

VBA

Sub ProcessAllWorkbooksInFolder(folderPath As String)
    Dim fileName As String
    fileName = Dir(folderPath & Application.PathSeparator & "*.xlsx")

    Do While fileName <> ""
        ' Skip hidden owner lock files created by OneDrive/Excel
        If Left(fileName, 2) <> "~$" Then
            Dim targetWb As Workbook
            Set targetWb = Workbooks.Open(folderPath & Application.PathSeparator & fileName)

            ' Process workbook...

            targetWb.Close SaveChanges:=False
            Set targetWb = Nothing
        End If
        fileName = Dir()
    Loop
End Sub

If background tasks or aborted loops leave these files permanently locked on your drive, refer to Fixing errors in Folder.Files when a temp file (~$) is open in the folder.

Common Confusion: OneDrive URL vs. SharePoint Permissions

It is easy to mistake a path error for a security permissions issue:

• OneDrive Path Errors (Code 53 / 76): Occur because VBA built-in functions do not understand the https:// prefix. The user typically has full read/write rights, but the command cannot resolve the address.
• Permission Denied / HTTP 403 (Authentication): Occurs when the user’s Microsoft 365 token has expired or SharePoint permissions are set to view-only. If Workbooks.Open("https://...") fails even when the URL is valid, verify corporate cloud permissions as detailed in How to fix “Upload Blocked” and “Sign in required” for OneDrive/SharePoint files.

Hard Stop & Data Safety

When writing macros that delete (Kill) or overwrite files located inside OneDrive folders:

• Never issue Kill on a path variable until you verify that the string is non-empty and points to a specific file (e.g., If Len(filePath) > 5 And Dir(filePath) <> "" Then Kill filePath).
• Avoid using automated file-clearing routines on root cloud libraries. If a script executes an unanchored delete command, OneDrive may sync the deletion across all team members before the action can be cancelled. Verify local path strings using Debug.Print in the Immediate Window prior to deploying batch-deletion macros.