VBA: Saving Excel file does not work on Mac, if the destination is in local OneDrive folder

Question

Summary: writing Excel workbook fails, if the destination is under OneDrive, only on Mac, and only if the destination file does not already exist.

I have a test routine that reads and writes Excel files (see the end of the post).

The source and destination can be

**1)**Local files, like

C:\localTest\1.xlsx - for Windows, or

/Users/username/Desktop/1.xlsx - for Mac

**2)**It could be a network path for files on OneDrive, like

https://companyname.sharepoint.com/sites/ap/Shared%20Documents/Some%20Report/tstFo/1.xlsx

**3)**and it could be local paths for OneDrive files, like

C:\Users\username\OneDrive - companyname\1\tstFo\1.xlsx - for Windows, or

/Users/username/Library/CloudStorage/OneDrive-companyname/1/tstFo/1.xlsx - for Mac

---

They all work, except when the destination is 3: local path for OneDrive. Only on Mac.

And before you say my path is slightly wrong: it does not work if the destination file does not exist,

but the exact same routine with exact same paths works - if the destination file does exist.

Excel asks if I want to replace it - and when I say yes - it does so without issues.

---

Here is the routine (written for Word VBA macro, that's why Excel is an object):

Private Function readAndWriteExcel() As Boolean

Set XL = CreateObject("Excel.Application")

On Error Resume Next

Set WBK = XL.Application.Workbooks.Open(fileName:=TextBox1.Text, ReadOnly:=True)

If (Err.Number <> 0) Or (WBK Is Nothing) Then

  MsgBox "Error reading Excel file" & vbCrLf & TextBox1.Text

  WBK.Close

  XL.Application.Quit

  Set WBK = Nothing

  Set XL = Nothing

  readAndWriteExcel = False

  Exit Function

End If



WBK.SaveAs fileName:=TextBox2.Text

If Err.Number <> 0 Then

  MsgBox "Could not save " & vbCrLf & TextBox2.Text

  WBK.Close

  XL.Application.Quit

  Set WBK = Nothing

  Set XL = Nothing

  readAndWriteExcel = False

  Exit Function

End If



readAndWriteExcel = True

WBK.Close

XL.Application.Quit

Set WBK = Nothing

Set XL = Nothing

End Function

As you probably understood, it is getting the source and destination file names from TextBox1 and TextBox2.

Summary: writing Excel workbook fails, if the destination is under OneDrive, only on Mac, and only if the destination file does not already exist.

I would appreciate any insight.

Answer 1

Thanks for reaching out.
Cause (macOS only):\ On macOS 12.1+ OneDrive uses Apple’s File Provider platform and moves the sync root to ~/Library/CloudStorage/OneDrive‑<tenant>. Folders under this path can be online‑only until they’re hydrated. Some apps (including Excel when automated from Word VBA) fail to create a brand‑new file in an online‑only OneDrive folder, while replacing an existing file succeeds—exactly what you’re seeing. [techcommun…rosoft.com], [support.mi…rosoft.com]

Fixes / Workarounds:

Pin (hydrate) the destination folder\ In Finder, right‑click the target OneDrive folder (and its parent) → Always Keep on This Device. This forces a local copy so Excel can create files there. [support.mi…rosoft.com], [support.mi…rosoft.com]

Save to a local temp file, then move into OneDrive\ Save to Environ("TMPDIR") first (a real local path on macOS), then Name (move/rename) the file to the OneDrive path. This avoids first‑write into File Provider until the final move. (Code below.) [support.mi…rosoft.com]

Pre‑create a placeholder, then SaveAs (replace)\ If the destination file doesn’t exist, create a zero‑byte file at the target path, then call SaveAs to the same path—replacement works even in online‑only folders. [support.mi…rosoft.com]

Use the SharePoint/OneDrive HTTPS URL instead of the local sync path\ Saving to https://tenant.sharepoint.com/.../file.xlsx bypasses File Provider path quirks. Your test using the HTTPS URL already works. [learn.microsoft.com]

Also ensure Excel for Mac and the OneDrive client are up‑to‑date; recent updates specifically target File Provider behavior on macOS. [support.mi…rosoft.com]

Safer drop‑in VBA (implements #2 and #3)

Private Function readAndWriteExcel() As Boolean

    Dim XL As Object, WBK As Object

    Dim src As String, dest As String, tmp As String

    src = TextBox1.Text

    dest = TextBox2.Text

    Set XL = CreateObject("Excel.Application")

    On Error GoTo OpenErr

    Set WBK = XL.Workbooks.Open(Filename:=src, ReadOnly:=True)

    ' --- #3: Pre-create a placeholder if the destination file is missing ---

    If Dir$(dest) = "" Then

        EnsureFolderExists GetParentFolder(dest)

        Dim f As Integer: f = FreeFile

        Open dest For Output As #f: Close #f

    End If

    On Error GoTo SaveErr

    WBK.SaveAs Filename:=dest, FileFormat:=xlOpenXMLWorkbook  ' .xlsx

    readAndWriteExcel = True

    GoTo Cleanup

SaveErr:

    ' --- #2: Save to local temp, then move into OneDrive ---

    Err.Clear

    tmp = Environ$("TMPDIR") & "tmp*" & Format(Now, "yyyymmdd*hhnnss") & ".xlsx"

    WBK.SaveAs Filename:=tmp, FileFormat:=xlOpenXMLWorkbook

    EnsureFolderExists GetParentFolder(dest)

    Name tmp As dest

    readAndWriteExcel = True

    Resume Cleanup

OpenErr:

    MsgBox "Error reading Excel file: " & Err.Number & " - " & Err.Description & vbCrLf & src

    readAndWriteExcel = False

Cleanup:

    On Error Resume Next

    WBK.Close SaveChanges:=False

    XL.Quit

    Set WBK = Nothing

    Set XL = Nothing

End Function

' -------- Helpers --------

Private Function GetParentFolder(ByVal f As String) As String

    Dim i As Long

    For i = Len(f) To 1 Step -1

        If Mid$(f, i, 1) = "/" Or Mid$(f, i, 1) = "" Then

            GetParentFolder = Left$(f, i - 1)

            Exit Function

        End If

    Next

End Function

Private Sub EnsureFolderExists(ByVal path As String)

    If Len(path) = 0 Then Exit Sub

    If Dir$(path, vbDirectory) = "" Then MkDirRecursive path

End Sub

Private Sub MkDirRecursive(ByVal path As String)

    Dim parts() As String, p As String, i As Long

    parts = Split(Replace(path, "", "/"), "/")

    p = IIf(Left$(path, 1) = "/", "/", "")

    For i = 0 To UBound(parts)

        If Len(parts(i)) > 0 Then

            p = p & parts(i)

            If Dir$(p, vbDirectory) = "" Then On Error Resume Next: MkDir p: On Error GoTo 0

            p = p & "/"

        End If

    Next i

Alternative (HTTPS path):

WBK.SaveAs Filename:="https://companyname.sharepoint.com/sites/ap/Shared%20Documents/Some%20Report/tstFo/1.xlsx", _

            FileFormat:=xlOpenXMLWorkbook

(This uses the SharePoint URL rather than the local sync path.) [learn.microsoft.com]

References:

• OneDrive on macOS moved to ~/Library/CloudStorage and uses File Provider (behavior changes & AutoSave notes). Microsoft Support
• Background on the new Files On‑Demand experience and File Provider integration on macOS 12.1+. OneDrive Tech Community blog
• How Always Keep on This Device works and why pinning hydrates content locally. Microsoft Support
• General OneDrive troubleshooting for macOS (keep client updated, re‑link if needed). Microsoft Support

Let us know if the issue persists after following these steps. I’ll be happy to assist further if needed. If the issue has been resolved, Kindly mark the provided solution as "Accept Answer", so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.