Populating multiple records to Excel template

[aatrv]

Hello. I'm a beginner at Access and i'm currently using access 2010. I have a report in access that contains records of authors and the books they have written. I want to be able to filter the report by author name and then export the filtered results to an excel template I have created. I have already figured out how to search, filter and populate the template thanks to "delikedi". However, if some author has more than one book, it only populates the first book and not the rest. I have attached a word document with screenshots and the code I used so it's easier to understand. Please help me out, i'm almost there !

 

[delikedi]

ok try this then

Dim rstBooks As Recordset
Dim lngBookCount As Long
Dim i As Long

Set rstBooks = Me.RecordsetClone
If Not rstBooks.EOF Then
    rstBooks.MoveLast
    rstBooks.MoveFirst
    lngBookCount = rstBooks.RecordCount
Else
    MsgBox "There are no records of books by this author."
    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("C:\sampletemplate.xltx.xlsx") 
'I suggest you clean the filename up btw
objXLBook.Application.Visible = True

Set objXLSheet = objXLBook.Worksheets(1)
'u dont need this line anymore Set objRange = objXLSheet.Cells.Range("B3")
 
 objXLSheet.Cells.Range("B1") = Author_Name

While rstBooks.EOF = False
     For i = 0 To (lngBookCount - 1)
        objXLSheet.Cells.Range("B" & (5 + i)) = [FONT=&quot]rstBooks![Online_Book_Name[/FONT]]
        objXLSheet.Cells.Range("A" & (5 + i)) = rstBooks![Original[FONT=&quot]_Book_Name[/FONT]]
    'I believe you should turn genre into a column, since every book can have different genre:
        objXLSheet.Cells.Range("C" & (5 + i)) = rstBooks![Genre]
     Next i
    rstBooks.MoveNext
Wend

'these lines are dysfunctional also strData = ComputerName
'these lines are dysfunctional also objRange.Value = strData

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

 

[aatrv]

When I tried that, it gives me a compile error: "method or data member not found." And the following is highlighted:

Dim rstBooks As Recordset
Dim lngBookCount As Long
Dim i As Long

Set rstBooks = Me.RecordsetClone
If Not rstBooks.EOF Then
rstBooks.MoveLast
rstBooks.MoveFirst
lngBookCount = rstBooks.RecordCount
Else
MsgBox "There are no records of books by this author."
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("C:\sampletemplate.xltx.xlsx")
'I suggest you clean the filename up btw
objXLBook.Application.Visible = True

Set objXLSheet = objXLBook.Worksheets(1)
'u dont need this line anymore Set objRange = objXLSheet.Cells.Range("B3")

objXLSheet.Cells.Range("B1") = Author_Name

While rstBooks.EOF = False
For i = 0 To (lngBookCount - 1)
objXLSheet.Cells.Range("B" & (5 + i)) = [FONT=&quot]rstBooks![Online_Book_Name[/FONT]]
objXLSheet.Cells.Range("A" & (5 + i)) = rstBooks![Original[FONT=&quot]_Book_Name[/FONT]]
'I believe you should turn genre into a column, since every book can have different genre:
objXLSheet.Cells.Range("C" & (5 + i)) = rstBooks![Genre]
Next i
rstBooks.MoveNext
Wend

'these lines are dysfunctional also strData = ComputerName
'these lines are dysfunctional also objRange.Value = strData

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

 

[MikeLeBen]

Are you referencing either the ADO or DAO libraries in your project?

read more about that here:
http://www.accessmvp.com/twickerath/articles/adodao.htm

 

[aatrv]

Do you know how I would do that in Access 2010? This link shows only for Access 2000 or 2002

 

[vbaInet]

Where did you place the code? Looks like you placed in a Standard Module. You should place the code in the module of your form.

 

[delikedi]

I've never worked with ADO recordsets, so no comment on that. In order to avoid confusion, you may change the line

Dim rstBooks As Recordset

into

Dim rstBooks As DAO.Recordset

Aatrv, I don't get any errors with the code. You are placing this code behind a button on the form, right?

On a different subject, I've noticed a logic error further down:

Next i
    rstBooks.MoveNext

should be

rstBooks.MoveNext
Next i

 

[aatrv]

hmmm that's strange. I've changed both things you said and i still get that same compile error. I did not see any logic errors. And yes, i am using this code as the Onclick event of a button.

 

[MikeLeBen]

Unfortunately I only know how to do that in 2007 as that's what I use. I'm pretty sure almighty Google can help you with that though.

Lookup "Library reference Office 2010".

Still, I smell a missing reference error, but I don't know for sure.

 

[vbaInet]

You said you are using the code in the Click event of a button but where exactly did you place the entire code? If the code is in a Standard Module it will throw that error. Or MikeLeBen might be on point. References are in the VBA editor under Tools > References.

 

[aatrv]

You said you are using the code in the Click event of a button but where exactly did you place the entire code? If the code is in a Standard Module it will throw that error. Or MikeLeBen might be on point. References are in the VBA editor under Tools > References.

I placed my whole code under Option Compare Database. I clicked on the button in design view, then clicked property sheet, onclick, event procedure, and then I pasted the code there. Here's exactly how it looks


Option Compare Database

Private Sub Command28_Click()
Dim rstBooks As DAO.Recordset
Dim lngBookCount As Long
Dim i As Long
Set rstBooks = Me.RecordsetClone
If Not rstBooks.EOF Then
rstBooks.MoveLast
rstBooks.MoveFirst
lngBookCount = rstBooks.RecordCount
Else
MsgBox "There are no records of books by this author."
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("C:\sampletemplate.xltx.xlsx")
'clean the filename up btw
objXLBook.Application.Visible = True
Set objXLSheet = objXLBook.Worksheets(1)
'dont need this line anymore Set objRange = objXLSheet.Cells.Range("B3")

objXLSheet.Cells.Range("B1") = Author_Name
While rstBooks.EOF = False
For i = 0 To (lngBookCount - 1)
objXLSheet.Cells.Range("B" & (5 + i)) = rstBooks![Online_Book_Name]
objXLSheet.Cells.Range("A" & (5 + i)) = rstBooks![Original_Book_Name]
'turn genre into a column, since every book can have different genre:
objXLSheet.Cells.Range("C" & (5 + i)) = rstBooks![Genre]
rstBooks.MoveNext
Next i
Wend
'these lines are dysfunctional also strData = ComputerName
'these lines are dysfunctional also objRange.Value = strData
Set objXL = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set objRange = Nothing
Set rstBooks = Nothing
End Sub

 

[vbaInet]

References then! And here's a site that should help give you some pointers. It's from one of the forum's VIP and an Access MVP:

http://www.btabdevelopment.com/ts/default.aspx?PageId=10

 

[aatrv]

Unfortunately I only know how to do that in 2007 as that's what I use. I'm pretty sure almighty Google can help you with that though.

Lookup "Library reference Office 2010".

Still, I smell a missing reference error, but I don't know for sure.

Ok so i added that from tools > reference. When I did that, it says "Name conflicts with existing module, project, or object library."

 

[MikeLeBen]

has the debug mode highlighted the same line?

 

[aatrv]

has the debug mode highlighted the same line?

Yes it still highlights : "Set rstBooks = Me.RecordsetClone"

 

[MikeLeBen]

I assume the error might be caused by the fact that reports don't have such a property. It is infact a property belonging to the recordsource object in forms.

 

[vbaInet]

Oh is this for a report? Mike's right there.

 

[MikeLeBen]

Oh is this for a report? Mike's right there.

I eventually read the original post

 

[vbaInet]

I eventually read the original post

I skimmed through it but somehow missed it.

So the report needs to be filtered using its Filter and FilterOn properties and you export it using the OutputTo command.

 

[delikedi]

I missed the fact that this was a report too. Anyways, I believe the following code should work now (reds are new or modified code).

Dim rstBooks As DAO.Recordset
[COLOR=Red]Dim rstBooksUnfiltered As DAO.Recordset[/COLOR]
Dim lngBookCount As Long
Dim i As Long

[COLOR=Red]Set rstBooksUnfiltered = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset, dbReadOnly)[/COLOR]
[COLOR=Red]rstBooksUnfiltered.Filter = "[Author_Name] = " & Chr(34) & InputBox("Please provide the name of the author:") & Chr(34)[/COLOR]
[COLOR=Red]Set rstBooks = rstBooksUnfiltered.OpenRecordset[/COLOR]
If Not rstBooks.EOF Then
rstBooks.MoveLast
rstBooks.MoveFirst
lngBookCount = rstBooks.RecordCount
Else
MsgBox "There are no records of books by this author."
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("C:\sampletemplate.xltx.xlsx")

objXLBook.Application.Visible = True

Set objXLSheet = objXLBook.Worksheets(1)

objXLSheet.Cells.Range("B1") = [COLOR=Red]rstBooks![Author_Name][/COLOR]

For i = 0 To (lngBookCount - 1)
objXLSheet.Cells.Range("B" & (5 + i)) = rstBooks![Online_Book_Name]
objXLSheet.Cells.Range("A" & (5 + i)) = rstBooks![Original_Book_Name]
'I believe you should turn genre into a column, since every book can have different genre:
objXLSheet.Cells.Range("C" & (5 + i)) = rstBooks![Genre]
rstBooks.MoveNext
Next i

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