Make a ‘load xlam’ or ‘load xlsm’ file without any code and in a .xlsx file

Posted on by

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.

What I tried first was

  • Load the .xlsx file
  • R – Mouse click the button and choose ‘Assign macro…’
    • if you don’t have a button – enable the Developer Ribbon
    • Choose Developer->Insert->[button icon] with the tooltip (Button – Form Control)
    • Draw It – then the Assign Macro window appears
  • normally you have ‘Z:\my_network\path-to\some-macro-file.xlam‘!MyMacroName
  • So I replaced (and hoped) that replacing the bold text with http://mycompany.sharepoint.com/my-new-path/some-macro-file.xlam with simply work. Crap it doesn’t.

Trick:

  • open up the toolbox from SharePoint (explained in my previous post)
  • open up your the sheet with the large button
  • erase all but the Sub’s name so in our example above this would be ‘MyMacroName’ the only thing in the assign macro
  • DO NOT TEST IT – why? Because my macro closes the sheet with the button :-O
    • but if yours doesn’t do that – go ahead and test it
    • Since it
  • It will run MyMacroName in the OTHER .xlam workbook. In my case all it did was show a MsgBox(“Loaded”) then closed the current sheet.
  • Now save the sheet with the button and close it.
  • Close all of Excel and test the loader.
  • Again it can be saved as an .xlsx because it has no code – only a pointer to the SharePoint copy.
  • I THINK you can also load and set the data source to the new SharePoint version and save the copy with the button
    • So to do that you switch the old location of the .xlam file to the new SharePoint location and save
    • Click the Data->Edit Link Ribbon item
    • Change Source
    • Test it /Update values
    • Save the .xlsx file with the button
  • Now you can put this file online and worst case email it out and tell people to put in on their desktops
  • CORPORATE SYSTEMS WILL NOT STRIP THIS .xlsx attachment. SWEET.