Originally Posted by
madpiet
use PowerQuery. then when you get everything consolidated, import that into Access.
PowerQuery absolutely blows the doors off VBA. Go on YouTube and look up maybe Rick de Groot (BIGorilla) or Chandeep Chhabra (goodly) or Purna Duggirala (Chandoo) and watch one of their videos on even simple PowerQuery. (Pick one, there are tons). Or one of Mynda Treacy's.
Seriously. don't use Access for the cleaning and transforming. It's terrible. And PowerQuery is in Excel. so you can do it all inside. And folder sources (LOTS of sources are built in, so you can process an entire folder at once if you want. (The problem is that VBA uses strong typing, and so does Access. The only way you could do it 100% in Access is to declare every column text and then use a variant data type in your code to clean it up.