Or if I want to do this in a really roundabout way, first export the
data (with pre-processing to standardise the columns etc) to Access,
and then bring it back from Access to Excel.

Ok, ta. You've at least diagnosed the first major issue with my code.