Shortcut button reference changed to workbook on OneDrive

cpu97

Board Regular
Joined
Jan 8, 2010
Messages
69
Thanks in advance for reading. About six weeks ago, my Windows 10 machine underwent an update. Prior to that, on my Excel 2019 I had several shortcut buttons on the Quick Access Toolbar, each linked to macros in various workbooks stored locally on my machine. My workflow was designed to have the workbooks which hold the macros open, and I typically use each button at least once a day, often more. Each macro opens, manipulates, saves and closes several workbooks, each stored locally.

Ever since the aforementioned update, however, the computer has also been saving to OneDrive in addition to my hard drive. I am not sure why, I do not recall electing this configuration. The codes is the same, instructing the files to save locally, but they also seem to save to OneDrive at the same time (or perhaps saving locally triggers a save to the remote location). This saving to the internet slows down the processes, but not by so much that it’s unbearable. The real problem for me is that the macro buttons were modified in that the workbooks they reference were changed from the local versions to the OneDrive versions. So, when I click on one of the buttons, I get an error message saying that “Excel cannot open two workbooks with the same name at the same time.”

Before the update, the workbook was open because I was working on it, I’d click the button and it would call the macro and run as expected. Now, clicking on the button appears to try to open the workbook from OneDrive, even though it’s already open, causing the error.

I removed the new buttons, opened the local workbooks, and attempted to recreate the buttons, but the only options for macros to assign to the button were the OneDrive versions, with long http addresses rather than the local C: drive addresses. I realize that I can probably change the codes in the OneDrive files to close themselves after running such that they can be opened the next time I click the button, but If I need to work on the files then I have to open them back up again, just to close them to run the macros, defeating the purpose of the shortcut button.

Does anyone know how I can assign the local versions of the macro to the buttons? If I need to disable OneDrive entirely, that’s fine with me!

Thanks for your consideration.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I was able to revert to my old state by following a YT video with instructions on "how to stop Windows 10 from saving files to OneDrive," then copying the files back from one drive to my local drive, then deleting the shortcut buttons. Next I opened the files from the local drive, and created new macro buttons in the Quick Access Tool Bar to replace the old ones. The file names did not have the http addresses in this step, so I figured that I was in the clear. My last step was to clear all of the pinned files from my quick open lists and replace with the local versions. The macros run from the quick access toolbar, so the issue is resolved, at least until the next update automatically replaces everything! Thanks to all who viewed the post.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top