Use CurrentRegion To Find Table Headers

Question

I have the following query, I have a program which has a range of a sheet that we call and treat as a Database. This range is empty until the information to be processed is imported. This information comes from 7 different files whose structure differs from each other in the order of the fields. In order not to have to program a code for each file, I have thought about creating a table that allows me to add the fields for each file, identifying them, so that the program at some point goes through the table, filters and locates the "headers", and then with a currentregion to verify these...but here is the catch, I don't know how to use currentregion in this context of what I propose.

I imagine the configuration table as follows

ID - ORIGIN - FIELD
1 - FILE1 - IDENTIFICATION
2 - FILE1 - FULL NAME
3 - FILE1 - DATE OF BIRTH
4 - FILE1 - SALARY
5 - FILE2 - VEHICLE PLATE
6 - FILE2 - VEHICLE TYPE
7 - FILE2 - ASSIGNED DRIVER
8 - FILE2 - MILEAGE
9 - FILE3 - START DATE
10 - FILE3 - END DATE
11 - FILE3 - BUDGET
12 - FILE3 - PREVIEW

And so on, almost in some cases up to 180 headings. For this reason I would like help on how with CurrentRegion and with this configuration table it could be done that it does not matter the order in which the fields are imported (if they arrived in a different order than the configuration table) to the Database, but that headers can be addressed or found and processed in the code later, and thus not have fixed cell or column addresses assigned. Thank you very much for your collaborations.

Answer 1

• The focus of the question is not on CurrentRegion.
• Utilize a Dictionary object to retain the desired header names.
• Import data in the same order as the config table.
• Note: Adjustments are required for importing multiple data files.

Microsoft documentation:

ReDim statement

Split function

Range.CurrentRegion property (Excel)

Option Explicit
Sub Demo()
    Dim objDic As Object, arrRes(), ColCnt As Long, RowCnt As Long
    Dim i As Long, j As Long, sKey As String, sFile As String
    Dim arrData, oWK As Workbook
    Dim aCol, vCol, iCol As Long
    Const SDEL = "|"
    Set objDic = CreateObject("scripting.dictionary")
    ' Load config data
    arrData = Sheets("Config").Range("A1").CurrentRegion.Value
    For i = LBound(arrData) + 1 To UBound(arrData)
        sKey = UCase(arrData(i, 2))
        ' Get the desired header name
        If objDic.exists(sKey) Then
            objDic(sKey) = objDic(sKey) & "|" & arrData(i, 3)
        Else
            objDic(sKey) = arrData(i, 3)
        End If
    Next i
    sFile = "File1.xlsx" ' for testing
    sKey = UCase(Split(sFile, ".")(0))
    If objDic.exists(sKey) Then ' file name (w/o ext) in Dict
        ' Open file to load data
        Set oWK = Workbooks.Open(ThisWorkbook.path & "\" & sFile)
        arrData = oWK.Sheets(1).Range("A1").CurrentRegion.Value
        oWK.Close False
        RowCnt = UBound(arrData)
        For Each vCol In Split(objDic(sKey), "|")
            iCol = 0
            ' Check if header in Config table
            For j = LBound(arrData, 2) To UBound(arrData, 2)
                If UCase(arrData(1, j)) = vCol Then
                    iCol = j
                    Exit For
                End If
            Next
            ' Populated the output array
            If iCol > 0 Then
                ColCnt = ColCnt + 1
                ReDim Preserve arrRes(1 To RowCnt, 1 To ColCnt)
                For i = LBound(arrData) To RowCnt
                    arrRes(i, ColCnt) = arrData(i, iCol)
                Next
            End If
        Next
    End If
    ' Write output to new sheet
    Sheets.Add
    ActiveSheet.Range("A1").Resize(RowCnt, ColCnt) = arrRes
'    ActiveSheet.Name = "DB"
End Sub