Seems simple enough – well I fought a bit – and rather than re-write an article, I will put a link to a sub-section of another article. Here is the link to help jump you there.

I have a file that is an .xlsx file that has NO code that loads a .xlam file with ease. BUT in the last article, as you can see the .xlam file is on sharepoint. Oh no – what now? It seems .xlam files do load properly whereas sometimes (security/trust issues) the .xlam file needs a helper file to get it kick started/loaded.

So I placed a massively large button on the whole spreadsheet (my sense of humour) but it was clear what needed to be done. PRESS THE BUTTON. Then the macro it loaded closed the workbook. Worked perfect. Oh oh – there was a problem – crap it can’t be changed to open a share point file. oh – wait – if you do this little trick – it works.

Ummm. Oh – here is the sweetest part – CORPORATE SYSTEMS WILL NOT STRIP THIS .xlsx attachment cause ‘ain’t no macros here boss!’. Boo-ya.

It seems you CAN run .xlam files from Excel as a central library whereas a year ago (might be more – time flies) this was not allowed. If you have a company that has SharePoint – it is finally ready. Using techniques you ALREADY know – here is how I did it and how credentials etc. fit into things. What a ride – and it works.

• on-prem on on-premises means the file’s url is like https://sharepoint.mycompany.com
• whereas hosted by microsoft would look like https://mycompany.sharepoint.com or https://mycompany-my.sharepoint.com is what I have noticed.

It is easy to enable the checkbox in the options screen. The option can also be set via a registry entry (or two). To register a change in the registry, Excel must be restarted, but ALSO it is important to note that when Excel exits, it resets this flag. Excel cannot be running therefore. This means we need a few ingredients when we are programming a solution.

First check if Excel is running

• to do this we have to know the latest Office version,

• is it 64 or 32bit as it affects what registry entries could exist.

Next set the registry entries

If the user does not need ton know if a setting will be successful , then use the “set it and hope for the best” strategy.

has been reset and it will fail anyways – then just set the 2 registry entries. Set the DWORD ‘AccessVBOM’ = 1 to trust.

Here are the two registry entries. In short set this one to 1 and the 2nd reg entry item if it exists.

• HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Security\AccessVBOM
• HKEY_LOCAL_MACHINE\Software\Microsoft\Office\16.0\Excel\Security\AccessVBOM