Experts:
I would like some assistance with creating, e.g., a VBA routine which would alter field names upon a data import routine (from Excel into Access).
General background:
1. Attached are 4 spreadsheets (source files) *mimicking* legacy data.
2. For testing purposes only, each XLSX includes 5 records and 2-3 columns (fields).
3. Now, the header rows in the XLSX are named differently for each of the 4 sources.
4. Based on 3., the header rows (fields names) require renaming (standardization) once the data has been imported into a staging database (see attached "Data_Import").
Current Process:
a. The attached DB contains a table [00_tbl_Master] which includes the field naming convention of "Before" ([FN_LEGACY]) and "After" ([FN_STANDARDIZED]).
b. Once I receive a new data dump (i.e., spreadsheets), I first rename the header rows in Excel from values shown in [FN_LEGACY] to values shown in [FN_STANDARDIZED].
c. Then I import the spreadsheets via command button "Import Data" (see form "F01_ImportData" and simply browse to the folder).
Again, presently, presently I have to rename the Excel header rows prior to the data import. However, as part of process streamlining, I'd like to remove that sub-step and automate it in Access (via VBA).
Envisioned Process:
- I open the DB and execute the file import routine (via form "F01_ImportData") as-is.
- As part of the import routine (and maybe a 2nd routine), VBA kicks in and renames all matching field names in [tbl_Source_1] through [tbl_Source_4] based on values in [00_tbl_Master].
- That is, the routine needs to use [00_tbl_Master] as a lookup for [FN_LEGACY] field names and alter them with [FN_STANDARDIZED] field names.
My question:
What VBA code would allow me to automatically rename the field names in [tbl_Source_1] through [tbl_Source_4] based on the matrix/lookup table [00_tbl_Master]?
Thank you for any assistance w/ this scenario.
Best,
Tom