MS Access 2010 VBA Find Column number of Excel heading by name

also change this line
 .worksheets(3).Select

to

 .worksheets("NameofSheet").Select

Why are you needing to know the column the "Plan ID" field is in on the imported spreadsheets?

Thank You! That is exactly what I needed. I modified it a little and made it a function and it works like a dream.

Thanks for the quick response!

-Chuck

I need to know because the cell it is in can change. It will be a key field on an access table.

On import, if the field is identified in the Access table imported to, I don't think what order the fields come in matters.  But others may disagree....

the only important thing is to find out that the column exists in the excel file.
the location is irrelevant if you are just doing a direct import of the worksheet.

btw, how are you importing the excel file?

Rey,
 This does not seem to work when the cell is to characters.

Like AA,BB, etc

well, you should have specified that you have more than 26 columns..., so what is the maximum column that you can have?

this code can find up to BZ column

Sub FindColumnname()
 Dim excelFile As String, iRow As Integer
 excelFile = CurrentProject.Path & "\book1.xlsx"
 Dim xlObj As Object, j As Integer, colCount As Integer
 iRow = 1
 Set xlObj = CreateObject("excel.application")
       xlObj.Workbooks.Open excelFile
       With xlObj
       .Visible = True
             .worksheets(1).Select
             colCount = .ActiveSheet.UsedRange.Columns.Count
             For j = 1 To colCount
             
                Select Case colCount
                    Case Is <= 26
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print Chr(64 + j) & iRow
                         Exit For
                        End If
                     
                    Case Is <= 52
                    
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print "A" & Chr(64 + j - 26) & iRow
                         Exit For
                        End If
                    
                    Case Is <= 78
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print "B" & Chr(64 + j - 52) & iRow
                         Exit For
                        End If
                   Case Else
                    
                End Select
             Next
             xlObj.Quit
       End With
 End Sub

Select allOpen in new window

Unfortunately they vary up to 160 cells,

then just follow the coding in the Select Case part, by adding 26 for each case.

Sub FindColumnname()
 Dim excelFile As String, iRow As Integer
 excelFile = CurrentProject.Path & "\book1.xlsx"
 Dim xlObj As Object, j As Integer, colCount As Integer
 iRow = 1
 Set xlObj = CreateObject("excel.application")
       xlObj.Workbooks.Open excelFile
       With xlObj
       .Visible = True
             .worksheets(1).Select
             colCount = .ActiveSheet.UsedRange.Columns.Count
             For j = 1 To colCount
             
                Select Case colCount
                    Case Is <= 26
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print Chr(64 + j) & iRow
                         Exit For
                        End If
                     
                    Case Is <= 52
                    
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print "A" & Chr(64 + j - 26) & iRow
                         Exit For
                        End If
                    
                    Case Is <= 78
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print "B" & Chr(64 + j - 52) & iRow
                         Exit For
                        End If
                   
                   
                    Case Is <= 104
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print "C" & Chr(64 + j - 78) & iRow
                         Exit For
                        End If
                   
                    Case Is <= 130
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print "D" & Chr(64 + j - 104) & iRow
                         Exit For
                        End If
                   
                    Case Is <= 156
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print "E" & Chr(64 + j - 130) & iRow
                         Exit For
                        End If
                   
                   
                    Case Is <= 182
                        If .ActiveSheet.cells(iRow, j).Value = "Plan ID" Then
                         Debug.Print "F" & Chr(64 + j - 156) & iRow
                         Exit For
                        End If
                   
                   Case Else
                    
                End Select
             Next
             xlObj.Quit
       End With
 End Sub

Select allOpen in new window

correction,

line 14 should be Select Case j  not  Select Case colCount

Thanks Rey,
 I've been coding on a MainFrame for the last 10 years. It's taking a while to get back into Access.

Would this be an easier way to get the column name? I'm not sure if it's what I'm looking for.

https://www.experts-exchange.com/questions/28159929/Excel-VBA-Find-Range-for-dbl-character-columns.html

I found anothger way that allows for any number of columns and is a lot shorter.

             colCount = .ActiveSheet.UsedRange.Columns.Count
             
             For j = 1 To colCount
             
                  If .ActiveSheet.Cells(iRow, j).Value = "Plan ID" Then
                    FindColumnName2 = Split(Cells(1, j).Address, "$")(1)
                  End If
             
            Next j