I've done a bit of excel object programming but am looking for a good way to
iterate through a MS Access recordset pasting the group header field to a
column then move to the next row before pasting the disired fields to a row
in the Excel sheet and continue pasting the rows until I reach the next
group and then paste that group header and so on. Any ideas? Thanks!
My initial code looks like this:
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim mCatCode As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set rstRs = dbPM.OpenRecordset(sqlRS, dbOpenSnapshot)
rstRs.MoveFirst
mCatCode = rstRs!CatCode
Do While rstRs.EOF < True

See below - I will append some code to what you have done. Is CatCode the
group header you are referring to? I will assume so. I will also assume
your recordset is sorted by CatCode; you could sort it with the Sort method
if not. Hope the brief outline below helps get you started:

"Geoff" wrote:

I've done a bit of excel object programming but am looking for a good way to
iterate through a MS Access recordset pasting the group header field to a
column then move to the next row before pasting the disired fields to a row
in the Excel sheet and continue pasting the rows until I reach the next
group and then paste that group header and so on. Any ideas? Thanks!
My initial code looks like this:
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim mCatCode As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set rstRs = dbPM.OpenRecordset(sqlRS, dbOpenSnapshot)
rstRs.MoveFirst

' Add to your declarations at the top:
' Dim xlCell as Excel.Range
' Dim OldCatCode as String

' continue your code with:
Set xlCell = xlBook.Worksheets("Sheet1").Range("A1")
OldCatCode = ""
Do While rstRs.EOF < True
mCatCode = rstRs!CatCode
' next line checks for new group header:
If Not(mCatCode = OldCatCode) Then
' if it is a new group, put the group header onto the current row and skip
down:
xlCell.Value = mCatCode
Set xlCell = xlCell.Offset(1,0)
OldCatCode = mCatCode
End If
' Now dump the fields into the row where xlCell is.
' The details depend on your recordset contents; you could do this field by
field, i.e.:
xlCell.Offset(0,1).Range("A1").Value = rstRs.Fields("FieldName").Value
' Or you could use a For Each loop to loop through the fields in the
recordset if there are a lot of them

' Go on to the next record:
rstRx.MoveNext
Loop