VBA - Collecting all the heading names into an array

[poduk]

Hi

Please can you help.

I am new to programming and maybe mixing up different languages.
The goal of my code is to collect the names of all the columns currently in my excel sheet.
The excel sheet can change in width (more or less columns) and content (heading names)

Dim i As Integer
i = 0
Range("A1").Activate  ' row 1 contains column headings
Do While Not IsEmpty(ActiveCell)  ' stop if the column heading is empty 
Heading(i) = ActiveCell.Value ' copy the heading name into array positon 0 
ActiveCell.Offset(, 1).Activate  ' move across to the next heading.
    i = i + 1 ' increase array position by 1
Loop ' loop until the end of the headings

I am hoping get an array with all column headings in it
Heading (A1, B1, C1)

Then use heading(0) to call A1 into my code.

Is it possible to do this?
If so, any idea where I am going wrong?

Thanks

Phil

 

[poduk]

More info

My code to the point it fails.

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=C:\Devicies.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Live_Data", cn, adOpenKeyset, adLockOptimistic, adCmdTable

' select all heading and adds to array heading

Dim heading As Variant
heading = Application.Index(Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Value, 1, 0)
' end of heading grab

Range("A2").Select ' row 2 contains first record
Do While Not IsEmpty(ActiveCell) 'while there is something in column A

Dim vRecord As Variant
vRecord = Application.Index("A2", Cells(1, UBound(heading)).Value, 1, 0)

MsgBox vRecord(2)

I can see the following
Ubound(heading) picks up the value "51"
vRecord picks up "Error 2015"
MsgBox vRecord(2) display a message "Run-time error '13': Type mismatch"

I believe this is the part of my coding with the error but struggling to work out why.

vRecord = Application.Index("A2", Cells(1, UBound(heading)).Value, 1, 0)

Maybe I have not understood how Application.Index works.
Am I right in think this calls the excel command Index?

 

[Rick Rothstein]

I believe this is the part of my coding with the error but struggling to work out why.

vRecord = Application.Index([COLOR=#FF0000][B]Range([/B][/COLOR]"A2", Cells([COLOR=#FF0000][B]2[/B][/COLOR], UBound(heading))[COLOR=#FF0000][B])[/B][/COLOR].Value, 1, 0)

See the red changes above

 

[poduk]

Yes it works.

Thanks again.

 

[poduk]

Hi Rick

Thank you again for all your help. I now have a fully working VBA code that will automatically update my Access database.

Here is a copy of the code, if it will help anyone else.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=C:\Devicies.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Live_Data", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Dim heading As Variant
heading = Application.Index(Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Value, 1, 0)

Dim i As Integer
i = 2

Range("A2").Select ' row 2 contains first record
Do While Not IsEmpty(ActiveCell) 'while there is something in column A

' Grab cell value of the complete row
vRecord = Application.Index(Range(ActiveCell, Cells(i, UBound(heading))).Value, 1, 0)
ActiveCell.Offset(1, 0).Activate
i = i + 1
' end - Grab cell value of the complete row
'working filter start
SerialNumber = vRecord(2) ' serial number data is in column 2
rs.Filter = "[Serial number ]='" & SerialNumber & "'"
' Working filter finish
'Updating access records
Dim v As Integer
v = 1
Do While v < UBound(heading) + 1
rs(heading(v)).Value = vRecord(v)
v = v + 1
Loop
rs.Update
' finished updating first record\row
Loop
' loop remaining rows until column a has an empty cell
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

Phil