It sounds like you are looking for Excel's PowerQuery functionality? You should not need to bring the data into Access, or write Macro's to do what you are doing anymore (Macro's were required in older versions of Excel, but not newer ones that have the data modelling capabilities).

This effectively is a data pipeline built within Excel that can either be edited visually or in 'M-Code'.