Problem with Access Report to Excel Code

Paulsburbon

Registered User.
Local time
Today, 13:18
Joined
May 3, 2005
Messages
65
I'm trying hard to learn VB but having a hard time with uncommented code I have found on the net. Here is the Problem:

I need to take a report[invoice] and make it into an excel file to send to customers that prefer that format. I sure I can format the excel file in the way I want it but getting the actual data[items ordered] to the sheet is a bit hard for me. Here is the code I'm using:

Private Sub Toggle110_Click()
Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim qdf As QueryDef
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set qdf = CurrentDb.QueryDefs("qryInvoice")
qdf.Parameters(0) = Forms!frmClaims!idsClaimNumber

Set rs = qdf.OpenRecordset
intMaxCol = rs.Fields.Count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set xlApp = New Excel.Application
With xlApp
.Visible = True
Set xlBook = .Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
With xlSheet
.Range(Cells(1, 1), .Cells(intMaxRow, intMaxCol)).CopyFromRecordset rs
End With
End With
End If
End Sub

So far I understand most of this but the .Range is coming back with a fail error. Any help would be really nice.
 
I just tried your code (using another query) and it worked fine. You should try stepping through the code one line at a time and checking your variables to see if you are getting what you expect.

Peter
 
Well I'm glad someone got it to work! All the variables seem to work. However when it gets to the

.Range(Cells(1, 1), .Cells(intMaxRow, intMaxCol)).CopyFromRecordset rs

It refuses to post the data in Excel and comes up with an error. Maybe something wrong with the query I'm using? Thank you for the reply Bat17!
 
I would try it with a simple select query, so that you know that it will return data and the number of Columns/Rows required.
This will help you trouble shoot where the problem lays

Peter
 
Should I try a SQL Query in the code itself? Or just make a new one? Thank you for your help peter.

Paul
 
I just created a new one and substituted it.

Peter
 
What version of Excel are you using? Excel 97 doesn't support the CopyFromRecordset method using ADO connections, so maybe this could be a problem (this scuppered me from using ADO to connect Excel/Access).
 
I'm using Excel 2003

Yea, Still can't get it to work. Using Excel 2003 and access 2003.
 
I don't have 2003 to play with, so I can't test it there for you.
I would try just poking data into cells to see if that works. Might help pin down whether it is range realy causing the problem or the recordset.

HTH
 
.Range(Cells(1, 1), .Cells(intMaxRow, intMaxCol)).CopyFromRecordset rs


Hi Paul

If this line is exactly as it appears in your module, then you have missed out a full stop before the Cells(1,1) term.

Richard
 

Users who are viewing this thread

Back
Top Bottom