Raspberry PI must-have’s

So I set up 2 Raspberry PI’s in the past week as a friend of mine needed something to do DDNS. I have not completed an Arduino DDNS but I have been comfortable with the PI. I wondered why the price went up in til I realized that the new RPI 3 B+ has wireless and bluetooth now. Nice – and not sure why the name didn’t change :-). Some must have’s I have found are:

  • enable SSH and/or VNC – can even do config via ssh in a console like way!
  • An off switch via a paperclip – this made me laugh and I reviewed the code. Slick! So put a screw in the official package I gave back to my friend as the off switch (short last 2 pins on GPIO)
  • A reboot every week (not necessary but the place I am putting this is 2 hours away – so if any memory leaks etc. occur – I want it to work – can you tell I use Windows for a living?) It is super stable but it needs a reboot still every 3 weeks or so
  • turn the time-sync on – there is no battery so it can go off time

I didn’t want to lose these links so I made an article. Also how to setup no-ip ddns which is super easy – plus I found Snotty Ron’s Cupcake recipe and the Snotty Ron commercial which I must share.

Making a raspberry PI 3 B+ into an Apple Time Machine node

Spare parts, but no spare time but i puttered away at this anyways. So there are a couple kinks to work out, but I wanted something remote for my Time Machine so it could work on the hour every hour in the background. My wireless on my Apple Extreme went but the main router is still in action. So what now? I need somewhere to plug an external USB drive into. So due to Covid19 – like many people I snapped and cleaned my office drawers and dusted off an old Rasperry PI 3 B+. Hey – I could try that cause my time machine perpetually says preparing backup and all the online help doesn’t work.

I had a spare drive (not sure why) so I cobbled the whole thing together and after > 24 hours of it backing up – I think it works. Here are links and quick tips to keep it going

Continue reading

Find out your sharepoint WebID, SiteID and ListID

You can do all of this without being an admin

List Id is easy: Go to a list, switch to classic mode, Click Library->Edit Library – your id is in the URL between %7B abd %7D (%7D is at the end of the url most likely). Those % items represent { and } respectively

Site ID: If your site is https//me.sharepoint.com/sites/MYSITE then add _api/site/id into a new tab in your browser and press enter. Your answer should be there. (Your browser will do a GET to the REST import will do a simple GET to the sharepoint get the site ID using REST)

WebID: Kinda lame once you know the site ID trick. just add _api/web/id.

This has become less exciting now that I know how. The reason I wrote this post is tehre are TONNES of resources on how to get these if your an admin or if you sharepoint powershell works – but adding the words to google “not as an administrator” got me these answers quick.

References: this guy, this chrome extension and this guy are my heros for this.

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

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.

Continue reading

Using Excel xlam Addons from online SharePoint

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.
Learn more

Enable Excel’s ‘Trust access to the VBA project object model’

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

  • 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

CustomUI Tips and Links we can’t live without

This article is simply a super great place to find links to the most used articles

Pre-Setting ‘Trust access to the VBA project object model’ for users via registry

There are some pretty cool “on the fly” programmatic things we can do with the VBA references like deleting “MISSING” references (which break code that is referenced UNDER these missing references- that is why they need to be fixed). But to do so requires that the user checks the box ‘Trust access to the VBA project object model’ which is buried deep (in typical Microsoft fashion). The Ribbon to check this manually is in the popup window accessed by File->Options->Trust Center->Macro Settings. Instead of writing code to tell the user what to do – why not get a registry change on all the target computers first. This article gives the *hint* on how to do that. I have 32bit Office (on purpose – so I don’t forget about those users) but everyone should have 64bit. So – there are a few registry keys that should get set.

Continue reading

Custom Ribbon Via VBA

Here’s a couple links that explain how to unzip and modify the OpenXML file hierarchy of .xlsx and .xlsm files and how to create custom ribbonUI. This should make it possible to access and modify the customUI ribbon via customXMLParts at runtime.

Opening and modifying excel files as zips: https://www.jkp-ads.com/articles/excel2007fileformat02.asp

VBA customUI editor: https://yoursumbuddy.com/ribbon-customui-xml-editor/