Using Excel xlam Addons from online SharePoint

Posted on by

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.

Quick Table of Contents

First – I suggest that you make an area for yourself on your corporate online site to put everything. Then set permissions like Read Only access for all users and Full Control to yourself and one other group (so you wont lock yourself out)

Folder, Structure Setup & Security

  • to make an area NOT part of Shared Documents is important because there is STILL a 256 character limit so the recommendations is do NOT use spaces or long names. I made my area called myAddin and under that my toolbox that houses the .xlam file called tb.
    • why? I put a subfolder tb so I could disinherit permissions and set
      • (to get here – on the folder IN SHAREPOINT in web browser click … and Manage Access – then on the bottom right – click Advanced)
      • myself and my admin = Full Control
      • remove everyone else
      • click + , then type “everyone” and choose “Everyone Except External Users”. That should be pretty secure
      • You might want to NOT choose “Notify People” while you are mucking about with this
  • For heaven’s sake turn versioning on – this is a toolbox and you can revert to a previous save.
    • I personally use GIT for all my VBA code and have a super cool export mechanism which I might blog about at a later date.
    • Gone are the days of making changes on one person’s computer and merging two toolboxes together – why? Cause now I have one super-shared toolbox. SUPER AWESOME for me and my clients.
  • To my document library I added 3 fields called “category” (mandatory), “description” and “quick install steps’
    • REMEMBER – SharePoint does NOT encourage making folders. Folders are for HUMANS. SharePoint was designed to have METADATA be used instead of folders and Views be made to separate out this and that file. It DOES work but it NOT liked by humans UNLESS you make the shortcuts at the top to filter these files.
    • This way you can have ONE folder full on files with a short URL. I am running with it and I will see how it goes
    • Review:
      • The Top Level folder “myAddin” is read/write for everyone.
      • I use the “mandatory” category field as my sub-folder concept WITH short links the top of my Doc Library. I am controlling most of the content so it should be OK.
      • The tb sub folder is read only for all except me and admins and its permissions are “disinherited” from the top read.write level
  • Drag your .xlam file into the ‘tb’ folder. For our exercise, lets call it “toolbox.xlam”

Other local client setup

  • Start Excel. Now in the upper right portion of the main window – you will see “Sign In” or a circle with a your initials or someone else’s initials who signed in long ago. The point is – it is THIS USER who will need access to your “myAddin” and “tb folders.
  • Get some options in Excel set.
    • Click File->Options
    • Click Trust Center tab (on left)
    • Click Trust center Settings … button
    • In my case Click “Macro Settings’ tab (on left)
    • Click “Trust access to the VBA Project Model”
    • Click Trusted Locations tab (on left)
    • Check off “Allow Trusted Locations on my network (not recommended)”
    • Click Add new Location button
    • paste in https://mycompany.sharepoint.com/
    • Check off “Sub folders of this location are also trusted”
    • OK and then OK again
    • (feels good so far right!? Right)
  • Copy the PROPER URL (yes I am trying to get your attention – do NOT do the wrong link) for your addin. In our case what we are hunting for is https://mycompany.sharepoint.com/xlAddin/tb/toolbox.xlam
    • **IMPORTANT** – in lower left of your browser click “Return to classic SharePoint”
    • goto your tb folder so you can see toolbox.xlam
    • Now in the URL copy up to this point “https://mycompany.sharepoint.com/xlAddin/tb”
    • Now open Excel and click “File->Open” and paste this in. You SHOULD be able to browse like it is local or network folder. Click “toolbox.xlam”.
    • Click open and you should be able to go to the developer tools and see your macros loaded.
    • WHY – JUST get the Copy-Link – NO NO NO NO NO. In classic Microsoft fashion – they are ensuring the LEAST SECURITY possible. The “Copy Link” in every instance I have found default to “Anyone with this link can edit”. So then why do they allow folder permissions to be then overridden by this link’s permission. This is NOT on-prem SharePoint. BEWARE. That is mental! That is TOTALLY WHAT WE DO NOT WANT (plus it doesn’t work) Why does MS do this.. they drive me super insane in MANY instances. I will stop my rant now as I could make a whole article about why I love Microsoft ($$$ for designers and IT people) vs. Apple (the phone never rings cause it never goes down and a monkey can administer it)
  • At this point you should be able to see your Macros Loaded and be able to edit them and if you are super geeky and added a Ribbon to the mix you should be able to see those buttons.

Allow Editing and Saving changes to your .xlam for YOURSELF (the tool designer) – Permissions and Logistics

You have to use check-in and out of the documents. Test if you can save to it. Ahhh – you can’t – so one extra (SUPER COOL) step.

  • Go back online and check-out the macro and reload/restart Excel (restart to reload the .xlam – mine have no “sheets” showing
  • now try it again.
  • What I noticed with .xlam files is if you forget to save Excel doesn’t remind you to save. BUT when you exit Excel now – it prompts you to check back in.
  • Also that means that you an muck about with your macros in a non-live fashion, get it all worked out and then checkin. Only at check in with other see the changes.
  • Similarly – you have to remember to checkin of course for other to see your changes. This will be likely the new frustration whereas in the older “network location” it was “oops, a change for one guy messed up someone else cause I had to save before I went home”.

Access by others in Excel

Simply have others login to Excel (in upper right) and give then the PROPER link

  • https://mycompany.sharepoint.com/xlAddin/tb/toolbox.xlam
  • Then have them choose File->Open and if you select the toolbox.xlam in the recents list you will see a pin icon on the right. Click it and your toolbox.xlam will be at the top of the list all the time.
  • Other ideas for them to do is File->Open then “Add a Place” and have them add https://mycompany.sharepoint.com/xlAddin … but upon writing this – I don’t know how I did this anymore. I will edit this once I re-figure that out
  • See next steps on mapping a drive letter to SharePoint so they can access the top directory, where, I am going to put templates etc. that I USED to put into folders but now am going to put as a category

Mapping Drives to SharePoint using Explorer (and Quick Access link)

So I wanted to put another cloud icon like one drive but I am not sure how to do that. What I did figure out is how to map a drive to an online shared folder WITHOUT having to be logged in as a Microsoft User into Windows.

Drive mapping will never cease to amaze me and my hunch was correct. It is WebDav underneath. This very detail is when I started writing this article

First, this article helped me accomplish this task. This CANNOT be done in Chrome but Internet Explorer. Add the domain https://mycompany.sharepoint.com as a trusted site using internet explorer. Keep the security level at the middle seemed to work for me.

Next to get quick access, follow these steps in this article. Note there are 2 methods – the main method and then an alternate way.

Remember to choose “login using different credentials” and to put your corporate username@mycompany.com and password.

To map using WebDav if that doesn’t work is to use a different URL which would be in this format:

  • \\mycompany.sharepoint.com@SSL\DavWWWRoot\xlAddin
  • I mapped it to the x drive
  • Choose different credentials (as I am not logged in as a microsoft user on my windows)

If all that fails – try this – I had to remap a drive on another machine – and this method works for sure.

  • Start internet Explorer
  • Add https://mycompany.sharepoint.com as a trusted site with medium access (one in the middle chosen by detault)
  • Login to sharepoint as ANY USER who has access to the folder you want
  • Navigate to the document library in our case this is https://mycompany.com/myAddin
  • In the new version of sharepoint (not classic) – click All Documents and choose ‘View in File Exploer’. A popup will happen so allow that (I trust that you don’t have popup blockers and antivirus stuff getting in the way).
  • A little window will appear and then disappear then you have Windows Explorer showing you the contents.
  • Copy the file location
  • Click This Computer
  • On upper ribbon called Computer – click “Map Drive”
  • Copy/Paste https://mycompany.sharepoint.com/myAddin into the place you want to go,
  • Choose a drive letter (eg G: for Geek)
  • choose “Use Other Credentials” and enter them and click “remember” – it does NOT have to be the same user as the first bullet
  • Then you will have a webDav’ed mapped drive. SWEET

Afterwords

Well that was quite a trip using new and old techniques. I hope it gave you hope that (finally) what we expected could be done can in fact be done.

What I didn’t write about is the fact that when you make changes – how you have to ensure Excel is indeed not loaded. When people use preview in Windows Explorer a copy of Excel can be lurking in the background. Then when you start or re-start Excel now there are 2 copies. And the copy in the background has your macros still loaded!!!! You customers will never see any new changes. So you have to teach them about

  • Start Task Manager
  • Click more details in bottom left if it is a small screen
  • Then go to the Details tab at the top right
  • Then click on ANY ITEM in the list
  • The type exc to quickly make the list go to Excel and to end-task on all instances.
  • Annoying!???? Yes
  • How can you tell there is > 1 version? If you start excel and it takes a little longer than normal to load it is likely the case that there are >1 copies of it running.
  • I will soon be making a right mouse click on the start item’s Excel and “killing all copies”. I will likely use c# to do this and a few registry items