Apr 06 2022 09:22 AM - edited Apr 18 2022 09:46 AM
Apr 06 2022 09:22 AM - edited Apr 18 2022 09:46 AM
I have a Macro-Enabled Workbook that I've been using for over a year with no issues in using the macros inside. Yesterday I copied this workbook into my OneDrive folder.
Now, when I open the file from the local copy on my hard drive, I cannot run, edit, or delete any macros. I can record a new macro, but that macro has the same limitations as previously created macros, it cannot be run, edited, or deleted.
Trying to run a macro produces this error message:
That sounds like a lead, except that when I open the workbook I do not get a security banner at all, though the banner settings are enabled.
I have tried these settings in the Trust Center and they have not altered the behavior of macros.
I added the local hard drive location for OneDrive to Trusted Locations, including checking the box to include all subfolders, but this has not fixed the issue.
I should not have to use the un-secure macro security settings I posted above, but I would make do if they actually worked. As it is, I am stumped on how to fix this issue.
I would really love to be able to use OneDrive for my important documents, but most of my important Excel Workbooks have at least one macro in them, since I use them to save time on tasks that are repeated often.
I've done some more testing and it is definitely an issue related to the Trusted Locations in Office. I created a new folder and added that folder to Trusted Locations. I was able to use macros from files in that folder without any issues. Then I uninstalled and re-installed OneDrive and pointed it to that folder for its base location. After OneDrive took over the folder, macros no longer worked from files in that folder.
Basically, Office is not recognizing a OneDrive folder as a Trusted Location despite the folder being in the list.
If it helps, here are my Office and OneDrive versions:
Office - Version 2203 (Build 15028.20160 Click-to-Run) Current Channel
OneDrive - Version 2022 (Build 22.033.0213.0002)
I ran a test with Dropbox, since it's a similar service. I am able to get the local Dropbox folder recognized properly as a Trusted Location in Office and macros work fine once that's done. Which means it's not automatic just because an app is monitoring the folder for synchronization.
So, this seems to be purely a question of how to fix OneDrive to make it work correctly with Trusted Locations.
Sep 06 2022 06:36 PM
@ChristopherLandry Hi Chris, did you end up getting any good solutions? We are having (maybe) similar issues, sadly don't know how to make Excel to trust our personal onedrive folders
Sep 07 2022 04:24 AM
@zfeng1380 Hello. Unfortunately Microsoft has been completely silent about this issue as far as I'm aware.
Here's a more complete image than the one I put in my original article showing how to get to the Trusted Locations settings in Excel Options for adding a local folder to the Trusted Locations list.
If you get to this point and your Excel workbook's macros continue to work after the workbook is added to a OneDrive folder, please let me know. Maybe Microsoft has fixed this in the background with an update somewhere along the way.
Sep 07 2022 05:04 AM
@ChristopherLandry thanks for the detailed reply. Unfortunately no luck still. Life is so tough using 2 office products....
Sep 08 2022 01:21 AM - edited Sep 08 2022 01:23 AM
Having the exact same issue.
Our current workaround is to copy a file to your desktop, run the required macro there, then copy it back onto our shared Onedrive office folder.
But this is only a temporary solution, the best will be to allow macros to be run on Onedrive directly. Would love to see if the smart people can come up with a solution.
Sep 08 2022 01:25 AM
Sep 08 2022 01:31 AM
@Peet_Luckhoff hi, we had the same issue, we could't get it work, even the very expensive external IT consultancy firm we hired (we paid over USD 100 per hour!!) couldn't get it work. So the solution we used at the very end is to switch everything to OneDrive Business accounts (as opposed to OneDrive Personal), it solves the problem at the expense of extra cost,
Sep 08 2022 03:20 AM - edited Sep 08 2022 03:25 AM
Thanks for the reply.
It is just weird that it has been working perfectly for a good 4 months now, and suddenly 2 days ago, it doesn't anymore.
When I say works perfectly, the different users could open the files on a shared onedrive folder, and run the macros in the files without issues.
Some added info, the macros are still enabled when I open the files on my pc, and only on my pc. I am the one who set up the Shared Onedrive Folder in the first place. However, it doesn't work on the other users pc's anymore. They have "Can Edit" Access to the folder.
Sep 08 2022 04:38 AM
@Peet_Luckhoff the cause is because of Microsoft's security upgrade related to macro. please see below for the official documentation (https://docs.microsoft.com/en-us/deployoffice/security/internet-macros-blocked)
if that official documenttation cannot solve your issue, there is a community discussion on this, some found it helpful, please google "Microsoft has blocked Macros from running because the source is untrusted", the very first search result should be that one.
and again if you still cannot fix it, and i will assume you use OneDrive - Personal (not business) account, then what worked for us at the very end is bite the bullet and upgrade to business acount
Sep 22 2022 02:23 PM
I contacted Microsoft support on this and they could not resolve it. They did have me create feedback in the the MS Feedback Hub. https://aka.ms/AAi4hrx . Please upvote for my feedback to give it higher priority. I am using Windows 11, OneDrive Personal. Strangely, one workbook in the shared folder does still work with macros.
Sep 22 2022 03:56 PM
Oct 17 2022 12:01 AMSolution
I assume you added your local Onedrive location.
now you should add the online Onedrive location name "https://d.docs.live.net/" make sure that you checked the "Subfolders of this location are also trusted"box.
Noted that you have to check the "Allow Trusted Locations on my network (not recommended)" box first.
Jan 22 2023 07:16 AM - edited Jan 22 2023 07:21 AM
Thanks, exeg. That suggestion worked for me.
Others keep in mind that if you have assigned macros to objects, you may need to reassign them if you have moved the file.
Jan 27 2023 04:46 AM - edited Jan 27 2023 04:46 AM
Thank you, exeg. I think that OneDrive online location made all the difference. I don't think I would have guessed that specific site location was the home of OneDrive.
Now that I can add my most important spreadsheets to OneDrive, which all have important macros, the software is actually useful to me!
Feb 14 2023 08:12 AM
I understand you were able to solve this problem.
I added a local one-drive location and checked the "Subfolders of this location
I added also the online one-drive location and checked the "Subfolders of this location
Can you send a picture or something similar so I can understand better?
I thank you for the help
Feb 14 2023 08:50 AM
Feb 15 2023 04:41 AM
Mar 18 2023 07:37 AM
@exeg Thank you so much for posting this. It was driving me crazy!