Register
Log In
Unanswered
Home
October 17, 2014
Offline
Hello,
I want to transfer all my client documents to OneDrive for Business, but I am having a problem with macro-enabled workbooks.
I have made the OneDrive folder containing my documents (and its sub-folders) a trusted location in Excel, but when I open a macro-enabled workbook from the recent files list, macros are disabled. If I open it using File, Open, Browse… macros are enabled. They are also enabled if I open the workbook by double-clicking it in File Explorer.
I am using Office 365 Pro Plus and Windows 10.
Is there a way to fix this?
Thanks,
Tony
November 8, 2013
Offline
Hi Tony,
Make sure there are no Group Policies restricting macros. Also, note that template files .xltx, .xltm are not considered trusted documents.
Another thing you may try is to list the Recent Files file path from your computer, with this code:
Sub ListRecentFiles()
Dim RecentFilesList As Object, RecentFile As Variant
Set RecentFilesList = Application.RecentFiles
For Each RecentFile In RecentFilesList
   Debug.Print RecentFile.Name
Next RecentFile
End Sub
This will print in Immediate window the full path of the recent files.
My guess is that you have files with "https://d.docs.live.net/...." path in the list, and that cannot be a trusted location.
October 17, 2014
Offline
Hi Catalin,
Thanks for your reply. I don't have any group policies restricting macros. I have run the code you gave me and the list looks like this:
https://rutherfordsystems-my.sharepoint.com/personal/tony_rutherfordsystems_onmicrosoft_com/Documents/OneDriveWork/Clients/CtiC/CTiC%20Contacts%20Register.xlsm
https://rutherfordsystems-my.sharepoint.com/personal/tony_rutherfordsystems_onmicrosoft_com/Documents/OneDriveWork/Clients/CtiC/CTiC%20Contacts%20Register%20test.xlsm
\Users\Tony\OneDrive - Rutherford Systems\OneDriveWork\Clients\CtiC\Data Export 2016-12-07-14-13-17.csv
\Users\Tony\OneDrive - Rutherford Systems\OneDriveWork\Clients\CtiC\Data Export 2016-12-07-14-00-03.csv
https://rutherfordsystems-my.sharepoint.com/personal/tony_rutherfordsystems_onmicrosoft_com/Documents/OneDriveWork/RSA/Worklog/WorkLog.xlsm
\Users\Tony\OneDrive - Rutherford Systems\OneDriveWork\Clients\CtiC\CTiC Contacts Register.xlsm
The thing that puzzles me is that there are some circumstances where macros are enabled and other where they are not. I have tried opening the same macro-enabled workbook using four methods, and the results are:
- Select from pinned files list: macros not enabled
- Select from recent files list: macros not enabled
- Select with File, Open, Browse: macros enabled
- Double click in File Explorer: macros enabled
I can manage using the last two options, but it's a pain not being able to use the pinned/recent files list.
Thanks,
Tony
November 8, 2013
Offline
Hi Tony,
I think there is something else you can try:
You already have a tool for creating Self Signed certificates. (search your computer for "Manage file encryption certificates"). Use the wizard to create a self signed certificate stored on your computer, and use this certificate to digitally sign all your vba projects, in all files.
Next step is to change your macro settings, to Allow Digitally signed macros only.
Now, the recent files links should open with macros activated, if they are digitally signed.
I think you have to add that certificate in Trusted Publishers, Trusted Locations, and other locations if necessary.
October 17, 2014
Offline
Hi Catalin,
I have found the answer to my problem. I had never used OneDrive until three months ago, when I posted my query, and didn't fully understand how it worked.
When I open an Excel workbook by double clicking it in File Explorer or using File, Open, Browse..., I am opening a local copy of the file. I had defined the local path as a trusted location, so my macros were automatically enabled. When I open it using File, Open, OneDrive... or select it from the pinned or recent files list, Excel opens the copy on my SharePoint site. I had not defined this as a trusted location, so I got the prompt to enable macros every time.
I have now added my SharePoint site to trusted locations, and everything works.
Thank you for your help with this.
Tony
Most Users Ever Online: 57
Currently Online:
27 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Akshatraj Adig
Liesie Marais
Moet Lewis
Michelle Anderson
Veronica Gallego
krishnaraj r
Dharan Prakash Mishra
Md. Rahman
Lilian Vo
Jane Kumwenda
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
