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

In Access 2010,I am importing a spreadsheet. I Need to know what Colum heading "Plan ID" appears in. Depending on the Excel file it could be in K1 or J1 or Z1 etc.

Can anybody send me the exact code to do this?
Chuck LoweAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this codes, change the path to your excel file

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
also change this line
 .worksheets(3).Select

to

 .worksheets("NameofSheet").Select
0
tomfarrarCommented:
Why are you needing to know the column the "Plan ID" field is in on the imported spreadsheets?
1
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Chuck LoweAuthor Commented:
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
0
Chuck LoweAuthor Commented:
I need to know because the cell it is in can change. It will be a key field on an access table.
0
tomfarrarCommented:
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....
0
Rey Obrero (Capricorn1)Commented:
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?
0
Chuck LoweAuthor Commented:
Rey,
 This does not seem to work when the cell is to characters.

Like AA,BB, etc
0
Rey Obrero (Capricorn1)Commented:
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

Open in new window

0
Chuck LoweAuthor Commented:
Unfortunately they vary up to 160 cells,
0
Rey Obrero (Capricorn1)Commented:
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

Open in new window

0
Rey Obrero (Capricorn1)Commented:
correction,

line 14 should be Select Case j  not  Select Case colCount
0
Chuck LoweAuthor Commented:
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.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28159929.html
0
Chuck LoweAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.