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.