Blog

New Code Projects: Backblaze B2 Version Cleaner & VBA SharePoint List Library

Itā€™s been a while since Iā€™ve posted code of any description, but Iā€™ve been working on a couple of things recently that Iā€™m going to make publicly available on my GitLab page (and my mirror repository at code.jnf.me)

Backblaze B2 Version Cleaner

I wrote last week about transitioning my cloud backup to Backblazeā€™s B2 service, and I also mentioned a feature of it thatā€™s nice but also slightly problematic to me: it keeps an unlimited version history of all files.

Thatā€™s good, because it gives me the ability to go back in time should I ever need to, but over time the size of this version history will add up – and Iā€™m paying for that storage.

So, Iā€™ve written a script that will remove old versions once a newer version of the same file has reached a certain (configurable)Ā ā€œsafe age.ā€

For my purposes I use 30 days, so a month after Iā€™ve overwritten or deleted a file the old version is discarded. If I havenā€™t seen fit to roll back the clock before then my chance is gone.

Get the code here!

VBA SharePoint List Library

This one I created for work. Getting data from a SharePoint list into Excel is easy, but I needed to write Excel data to a list. I assumed thereā€™d be a VBA function that did this for me, but as it turns out I was mistaken – so I wrote one!

At the time of writing this is inĀ ā€œproof of conceptā€ stage. It works, but itā€™s too limited for primetime (it can only create new list items, not update existing ones, and each new item can only have a single field).

Out of necessity Iā€™ll be developing this one pretty quickly though, so check back regularly! Once itā€™s more complete Iā€™ll be opening it up to community contributions.

I have no plans to add functions that read from SharePoint to this library, but once I have the basic framework down that wouldnā€™t be too hard to add if youā€™re so inclined. Just make sure you contribute back!

Get the code here!

Blog

Extract a List of Values Filtered by Criteria with Sub-Arrays in Excel

If you’re not careful then array functions in Excel can quite easily melt your brain and cause it to leak out through your various face-holes, so I’m finding this step-by-step guide especially useful this morning.

Extract a List of Values Filtered by Criteria with Sub-Arrays in Excel