Import File with Multiple header Variations

  • Background:
    Our dept at work receives excel files in various formats from other depts. All have the same TYPES of datapoints, but they come in different column orders with slightly different headers(Ex: Service Code v ServiceCode v ServCode v SCOD). While it would be great to force a standard format, assume that is off the table. We currently have a process where we manually past the data from those different spreadsheets into one standard import file, import that template into an Access application we've built that formats the data and columns to a standard file we use to give our reps.

    I would like to add functionality to that database that builds that import file for us so we can cut out that step. I already know how to create a form that allows a user to select a file using the Open/Save dialog and I also already know how to manipulate the excel sheet using Access vba code.

    My question is regarding the multiple header variations. I need to know the best way to have the application search for a required header, and if it doesn't find it, move on to the next potential variation of the header in the list. Ultimately I would also like to add a piece that says, if no known iterations of a header exist the user enters the one that is there and it will add the user entry to the list of potential headers.

    Can someone help put me on the right path?