Issues with .xlsm Files and Macros After Uploading to OneDrive

Question

Hello everyone!

There have been persistent issues with .xlsm documents that contain macros/VBA after uploading them to OneDrive.

A function was added in VBA to update the last modified date of the workbook:

Function LastModifiedDate() As String 
    LastModifiedDate = CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).DateLastModified 
End Function 

After implementing this, the following message appears:

After saving a copy as an Excel Macro-Enabled Workbook, everything works fine locally. However, once the workbook is uploaded to OneDrive, the function ceases to work after clicking on Enable Macros. This issue also occurs with other workbooks containing macros when shared with coworkers.

What could be causing this issue, and how might it be resolved? Additionally, is there a way to disable Auto Save in OneDrive to see if that resolves the problem? Also, when uploaded, the cell that previously worked with the function now shows: !VALUE.

Ps. I tried switching off auto save before clicking on Enable macros but it still damaged the file

Answer 1

Hi @Dneesal

is there a way to disable Auto Save in OneDrive to see if that resolves the problem?

Which version of Office are you and other file coworkers using?

We can turn off autosave in Excel for Microsoft 365 Apps.

Are you using OneDrive for Business?

I test your code in Excel for Microsoft 365 Apps, and I upload the workbook into OneDrive for Business, if I use the functon "=LastModifiedDate()", it returns the error "#VALUE!".

But if I use the code below, the function "=LastModifiedDate1()" returns the date.

Function LastModifiedDate1() As String
    ' Returns the last saved time of the workbook
    LastModifiedDate1 = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

This issue also occurs with other workbooks containing macros when shared with coworkers.

Do you mean all .xlsm files that upload to OneDrive cannot run VBA code correctly on Excel desktop?

If so, I suggest you follow the steps in "Files on OneDrive or SharePoint",

• Set the Windows internet security zone assignment for OneDrive domains to Trusted Sites. Add https://{your-domain-name}-my.sharepoint.com (for OneDrive for Business) into the Trusted Sites zone.
• Add the path into Office apps as a Trusted Location. Go to File > Options > Trust Center, Trust Center Settings > Trusted Locations. Check the "Allow Trusted Locations on my network (not recommended)" box first. Then add https://{your-domain-name}-my.sharepoint.com, make sure that you checked the "Subfolders of this location are also trusted" box.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---