Populating multiple records to Excel template

[vbaInet]

You really don't want to loop through a recordset to populate cells in Excel. Even the Cells() method alone is slow and on top of that looping through a recordset is also slow.

If you want to export the report (with the layout and formatting) then you need to use the methods explained in my last post. However, if you want to export just the raw values then use Excel's CopyFromRecordset method or Access' TransferSpreadsheet method.

If you need to export calculated fields in the report, perform the calculation in a query and export the query using one of the methods described previously.

 

[aatrv]

Sorry for the late reply. I just wanted to say that I used the code that delikedi provided me and it works perfectly! Thank you all for your help

 

[jasmeetsingh89]

Hi Delikedi ... I have a very similar situation ... and I have used the code that u posted on 07-09-2012 09:57 AM after modifyin it a lil ....

I have multiple records for a single student ID and want to assign them to an Excel template .... I want this to happen on a button click .... But on the button click i m gettin an error whose screenshot i have attached .... also below is my code ... please help me ... i have been crackin my brains on this since a while now ... ...

Dim rsRec As ADO.Recordset
Dim rsRecUnfiltered As ADO.Recordset
Dim recCount As Long
Dim i As Long

Set rsRecUnfiltered = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset, dbReadOnly)
rsRecUnfiltered.Filter = "[mskcc_ID] = " & Chr(34) & InputBox("Please provide the MSKCC ID of the Student:") & Chr(34)
Set rsRec = rsRecUnfiltered.OpenRecordset
If Not rsRec.EOF Then
rsRec.MoveLast
rsRec.MoveFirst
recCount = rsRec.RecordCount
Else
MsgBox "There are no records of payment by this student."
Exit Sub
End If

Dim objXL As Object
Dim objXLBook As Object
Dim objXLSheet As Object
Dim objRange As Object


Set objXL = CreateObject("Excel.Application")
Set objXLBook = objXL.Workbooks.Open("E:\BTR.xlsx")

objXLBook.Application.Visible = True

'Set objXLSheet = objXLBook.Worksheets(1)

objXLSheet.Cells.Range("K8") = rsRec![mskcc_ID]

For i = 0 To (recCount - 1)
objXLSheet.Cells.Range("A" & (14 + i)) = rsRec![_Date_]
objXLSheet.Cells.Range("B" & (14 + i)) = rsRec![Semester]
objXLSheet.Cells.Range("c" & (14 + i)) = rsRec![Description]
objXLSheet.Cells.Range("E" & (14 + i)) = rsRec![Check#]
objXLSheet.Cells.Range("F" & (14 + i)) = rsRec![Payment_Type]
objXLSheet.Cells.Range("G" & (14 + i)) = rsRec![Financial_Aid]
objXLSheet.Cells.Range("H" & (14 + i)) = rsRec![Tuitio_Payment]
objXLSheet.Cells.Range("J" & (14 + i)) = rsRec![Disbursement]
objXLSheet.Cells.Range("K" & (14 + i)) = rsRec![Tuition_Due]
rstBooks.MoveNext
Next i

Set objXL = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set objRange = Nothing
Set rstBooks = Nothing

 

[delikedi]

Hi jasmeetsingh89,

I guess you weren't able to attach the screen shot, as you are a new user. It really helps though, if you provide the error number and the offending line of code.

I can't test any code at the moment but I see these problems that might be producing the error:

Dim rsRec As ADO[COLOR=Red]DB[/COLOR].Recordset
Dim rsRecUnfiltered As ADO[COLOR=Red]DB[/COLOR].Recordset

I haven't worked with ADO objects for a while but I believe you should declare them with "ADODB" keyword.

rsRecUnfiltered.Filter = "[mskcc_ID] = " [COLOR=Red]& Chr(34) [/COLOR]&  InputBox("Please provide the MSKCC ID of the Student:") [COLOR=Red]& Chr(34)[/COLOR]

if mskcc_ID is a number, you don't need the expressions in red. They would be needed is mskcc_ID were of the string datatype. Trying to pass a number as a string might produce the error.

'Set objXLSheet = objXLBook.Worksheets(1)

you should remove the apostrophe at the start of this line. Otherwise the following line will produce the "object required" error.