Data from Excel to VB by column name/header

[UBfoolin]

My users create Excel worksheets that have a single header row with the field name for each column's data. The fields are NEVER in the same columns from worksheet to worksheet.

How can I pull the data from all worksheets, using the field name in the first row, putting each column's data into it's corresponding recordset field?

Thanks for any tips!
UB

 

[jsogchr]

If the field names are always named the same then you could do something like this:

for x = 1 to 10
If Worksheets("Sheet1&quot.Cells(1,x) = "Topic1" Then
ColNumTopic1 = x
End If
Next x

for x = 1 to 100
array(x) = Worksheets("Sheet1&quot.Cells(x, ColNumTopic1)
Next x

If the field names are not always the same, but contain similar wording do the same as above but use:

If instr(Worksheets(&quot;Sheet1&quot.Cells(1,x), &quot;Top1&quot <> 0 Then

Hope this helps.

Susan