VBA to move into Excel cells

[Pendragon]

Access03/WinXP

Running code to dump data from a recordset into an Excel file. I am trying
to populate the first row with the recordset field names. The problem is in
trying to move from one column to the next - the error is "438 - Object
doesn't support this property or method."

Any help is appreciated.

Set objActiveWkbk = objExcel.workbooks.Add

......

rs.MoveFirst

With objExcel
.Sheets(1).Select
With .ActiveSheet
For i = 1 To rs.RecordCount
.cells(1, i).Select ***Error is here***
.ActiveCell.Value = rs(i).Name
.ActiveCell.Font.Bold = True
Next i
.Range("A2").CopyFromRecordset rs
End With
End With


Thanks!

 

[Pendragon]

One correction to code:

not: With objExcel
instead: With objActiveWkbk

Thanks.

 

[Graham Mandeno]

Hi Uther

I always try to avoid using Select, Selection, and ActiveXXX when automating
Excel, and instead use absolute object references so I'm sure about where I
am.

I suggest you modify your code as follows:

Dim objWkSht as Excel.Worksheet

......
objExcel.SheetsInNewWorkbook = 1 ' avoid empty worksheets
Set objActiveWkbk = objExcel.Workbooks.Add
Set objWkSht = objActiveWkbk.Sheets(1)

......

rs.MoveFirst

With objWhSht
For i = 1 To rs.RecordCount
With .Cells(1, i)
.Value = rs(i - 1).Name
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Next i
.Range("A2").CopyFromRecordset rs
End With