Results 1 to 2 of 2
  1. #1
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019

    VBA routine to rename fieldnames based on a lookup table


    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.

    Attached Files Attached Files

  2. #2
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    I overlooked something... I may already have a solution. Wasn't sure how to delete this post.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-20-2021, 04:27 PM
  2. Lookup column based on table value
    By greenkevin86 in forum Access
    Replies: 9
    Last Post: 04-30-2012, 06:03 PM
  3. Rename cell based on duplicate compound key
    By luckycharms in forum Access
    Replies: 1
    Last Post: 04-23-2012, 06:18 PM
  4. Replies: 17
    Last Post: 12-20-2011, 04:36 PM
  5. Replies: 3
    Last Post: 02-09-2011, 07:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums