Reading records into Crystal Report for export as Excel?

Robin Cushman

Hi all,

I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel spreadsheet. I'm having trouble
with my code at the point at which it hits ".ReadRecords" -- the
module just runs and runs without generating anything. I've gotten
this code to correctly save .rpt files without any data, but not with
data, nor have I been able to export to Excel. Can anyone see what
I'm not doing right? Below is my code with extraneous stuff taken
out.

Thanks so much for any help.
Robin

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Compare Database
Option Explicit
Public appl As New CRAXDRT.Application
Public db As DAO.Database
Public rst1 As DAO.Recordset
Public rst2 As DAO.Recordset
Public rst3 As DAO.Recordset
Public rpt As New CRAXDRT.Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub AutoGenerateReports()
Dim strSelectionFormula As String
Dim strParameter As String
Dim strNames As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strReportToRun As String
Dim strReportDestination As String
Dim strThisWeeksReport As String
Dim rst As DAO.Recordset
Dim strAllRptOwners As String
Dim LastSwipe As Date
Dim strReportData As String

On Error GoTo ErrorHandler

Set appl = CreateObject("CrystalRuntime.Application")
Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblautogenreports", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qry_autogenrptsnames", dbOpenDynaset)

'set recordset for report
strReportData = "SELECT EvnLog.Loc, EvnLog.Dev, EvnLog.TimeDate,
EvnLog.Code, EvnLog.LName, EvnLog.FName, DEV.TNA, EvnLog.Event,
EvnLog.IOName, Evn.Event " & _
"FROM (Evn INNER JOIN EvnLog ON Evn.Event =
EvnLog.Event) INNER JOIN DEV ON EvnLog.Dev = DEV.Device WHERE " &
strSelectionFormula & ";"
strReportData = Replace(strReportData, "{", "")
strReportData = Replace(strReportData, "}", "")

Set rst3 = db.OpenRecordset(strReportData)

' open report and supply parameters, data, and then export as Excel to
file
Set rpt = appl.OpenReport("c:\TestingAutoGenRpts\TimeAttenda nceRpt_Qry
- 05-13-04.rpt")
With rpt
.DiscardSavedData
.FormulaFields(11).Text = "'" & strReportToRun & "'"
.FormulaFields(9).Text = "'" & strNames & "'"
.FormulaFields(13).Text = "#" & datBeginDate & "#"
.RecordSelectionFormula = strSelectionFormula
.Database.SetDataSource rst3
.Database.Verify
.ReadRecords 'THIS IS WHERE IT HANGS
.ExportOptions.DestinationType = crEDTDiskFile
.ExportOptions.FormatType = crEFTExcel80
.ExportOptions.DiskFileName = strReportDestination & "\" &
strThisWeeksReport & ".xls"
.Export True
End With

Nov 12 '05 #1

Terry Kreft

It's a lot easier to just push an ADO recordset at your crystal report than
to do this.

Essentially you
create an ADO recordset which contains the records you need

then
With rpt
.DiscardSavedData
.Database.SetDataSource rst3, 3
'... do the excel bit here

and that's it

--
Terry Kreft
MVP Microsoft Access
"Robin Cushman" <ro******@earthlink.net> wrote in message
news:6a**************************@posting.google.c om...

Hi all,

I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel spreadsheet. I'm having trouble
with my code at the point at which it hits ".ReadRecords" -- the
module just runs and runs without generating anything. I've gotten
this code to correctly save .rpt files without any data, but not with
data, nor have I been able to export to Excel. Can anyone see what
I'm not doing right? Below is my code with extraneous stuff taken
out.

Thanks so much for any help.
Robin

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Compare Database
Option Explicit
Public appl As New CRAXDRT.Application
Public db As DAO.Database
Public rst1 As DAO.Recordset
Public rst2 As DAO.Recordset
Public rst3 As DAO.Recordset
Public rpt As New CRAXDRT.Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub AutoGenerateReports()
Dim strSelectionFormula As String
Dim strParameter As String
Dim strNames As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strReportToRun As String
Dim strReportDestination As String
Dim strThisWeeksReport As String
Dim rst As DAO.Recordset
Dim strAllRptOwners As String
Dim LastSwipe As Date
Dim strReportData As String

On Error GoTo ErrorHandler

Set appl = CreateObject("CrystalRuntime.Application")
Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblautogenreports", dbOpenDynaset)
Set rst2 = db.OpenRecordset("qry_autogenrptsnames", dbOpenDynaset)

'set recordset for report
strReportData = "SELECT EvnLog.Loc, EvnLog.Dev, EvnLog.TimeDate,
EvnLog.Code, EvnLog.LName, EvnLog.FName, DEV.TNA, EvnLog.Event,
EvnLog.IOName, Evn.Event " & _
"FROM (Evn INNER JOIN EvnLog ON Evn.Event =
EvnLog.Event) INNER JOIN DEV ON EvnLog.Dev = DEV.Device WHERE " &
strSelectionFormula & ";"
strReportData = Replace(strReportData, "{", "")
strReportData = Replace(strReportData, "}", "")

Set rst3 = db.OpenRecordset(strReportData)

' open report and supply parameters, data, and then export as Excel to
file
Set rpt = appl.OpenReport("c:\TestingAutoGenRpts\TimeAttenda nceRpt_Qry
- 05-13-04.rpt")
With rpt
.DiscardSavedData
.FormulaFields(11).Text = "'" & strReportToRun & "'"
.FormulaFields(9).Text = "'" & strNames & "'"
.FormulaFields(13).Text = "#" & datBeginDate & "#"
.RecordSelectionFormula = strSelectionFormula
.Database.SetDataSource rst3
.Database.Verify
.ReadRecords 'THIS IS WHERE IT HANGS
.ExportOptions.DestinationType = crEDTDiskFile
.ExportOptions.FormatType = crEFTExcel80
.ExportOptions.DiskFileName = strReportDestination & "\" &
strThisWeeksReport & ".xls"
.Export True
End With

Nov 12 '05 #2

Robin Cushman

Thanks so much for your advice Terry. I went back and re-did things
in ADO and for some reason am still having problems. (I'm using A2K,
VBA, ADO, Crystal 9) Below is (hopefully all of) the code that needs
to be analyzed. Basically I can get the code to export an Excel file,
but the file size is 0 KB, and I get an error message when I open it –
"Unable to open file." Well, I _can_ open it, but there's nothing
inside – no headers or footers much less details, and my code keeps
running and running. My database does consist of about 20,000
records, but the code kind of hangs – I can hear that it's not
processing.

I can get the code to save a report file with the record source set
properly so that when I do bring up the report I get good data, but I
just can't get the report to associate the data with it and then
export the whole thing to Excel.

Between books and newsgroup postings I've been looking at several
possible sources of the problem. If anyone has any suggestions for
places to look for code for working with Crystal (other than here),
I'd appreciate hearing about them…

(1) I guess I should assume that since I dimmed both my recordset and
report as Public that I'm not having scope problems and therefore
don't need ".ReadRecords" – right? What does one use
".Database.Verify" for?
(2) Should I be using ".AddADOCommand" rather than ".SetDataSource"?
(3) I checked and do have what I believe are the right U2F*.dlls and
U2D*.dlls – could I be missing others?
(4) I eventually need to plug in the proper ".ExportOptions" to export
but am just using ".Export True" at this point to eliminate possible
places for things to go wrong.
(5) I've read in places that this might be a timing issue – could that
be the problem here?

Thanks again for any help on this.

~~~~~~~~~~~~~~~~~~~~~~~~~~

Set rpt = appl.OpenReport("c:\TestingAutoGenRpts\TimeAttenda nceRpt_Qry
- 05-13-04.rpt")

With rpt
.DiscardSavedData
.Database.SetDataSource rst3, 3
.Export True
End With

~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks again!!

"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<o4********************@karoo.co.uk>...

It's a lot easier to just push an ADO recordset at your crystal report than
to do this.

Essentially you
create an ADO recordset which contains the records you need

then
With rpt
.DiscardSavedData
.Database.SetDataSource rst3, 3
'... do the excel bit here

and that's it

--
Terry Kreft
MVP Microsoft Access

Nov 12 '05 #3

Terry Kreft

Robin,
Here is the whole of the code which I have used to achieve what you want
(this is using CR9, and A2k running from the Access database).

' ********** Code Start **********
Sub AutoGenerateReports()
Dim appl As New CRAXDRT.Application
Dim rst1 As ADODB.Recordset
Dim rpt As New CRAXDRT.Report

Const REP_DATA = "SELECT * FROM Table3"
Const REP_NAME = "C:\Delme CR\test.rpt"

Set appl = CreateObject("CrystalRuntime.Application")

Set rst1 = CurrentProject.Connection.Execute(REP_DATA)
Set rpt = appl.OpenReport(REP_NAME)

With rpt
.DiscardSavedData
.Database.SetDataSource rst1, 3
.Export True
End With

rst1.Close
Set rst1 = Nothing
Set rpt = Nothing
Set appl = Nothing
End Sub

' ********** Code End **********

Try starting with this code as template and then work up. If the code above
doesn't work then there must be something else going on.

The most obvious thing to look for is that the field names in your recordset
match the ones used when creating the report.

--
Terry Kreft
MVP Microsoft Access
"Robin Cushman" <ro******@earthlink.net> wrote in message
news:6a*************************@posting.google.co m...

Thanks so much for your advice Terry. I went back and re-did things
in ADO and for some reason am still having problems. (I'm using A2K,
VBA, ADO, Crystal 9) Below is (hopefully all of) the code that needs
to be analyzed. Basically I can get the code to export an Excel file,
but the file size is 0 KB, and I get an error message when I open it -
"Unable to open file." Well, I _can_ open it, but there's nothing
inside - no headers or footers much less details, and my code keeps
running and running. My database does consist of about 20,000
records, but the code kind of hangs - I can hear that it's not
processing.

I can get the code to save a report file with the record source set
properly so that when I do bring up the report I get good data, but I
just can't get the report to associate the data with it and then
export the whole thing to Excel.

Between books and newsgroup postings I've been looking at several
possible sources of the problem. If anyone has any suggestions for
places to look for code for working with Crystal (other than here),
I'd appreciate hearing about them.

(1) I guess I should assume that since I dimmed both my recordset and
report as Public that I'm not having scope problems and therefore
don't need ".ReadRecords" - right? What does one use
".Database.Verify" for?
(2) Should I be using ".AddADOCommand" rather than ".SetDataSource"?
(3) I checked and do have what I believe are the right U2F*.dlls and
U2D*.dlls - could I be missing others?
(4) I eventually need to plug in the proper ".ExportOptions" to export
but am just using ".Export True" at this point to eliminate possible
places for things to go wrong.
(5) I've read in places that this might be a timing issue - could that
be the problem here?

Thanks again for any help on this.

~~~~~~~~~~~~~~~~~~~~~~~~~~

Set rpt = appl.OpenReport("c:\TestingAutoGenRpts\TimeAttenda nceRpt_Qry
- 05-13-04.rpt")

With rpt
.DiscardSavedData
.Database.SetDataSource rst3, 3
.Export True
End With

~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks again!!

"Terry Kreft" <te*********@mps.co.uk> wrote in message

news:<o4********************@karoo.co.uk>...

It's a lot easier to just push an ADO recordset at your crystal report than to do this.

Essentially you
create an ADO recordset which contains the records you need

then
With rpt
.DiscardSavedData
.Database.SetDataSource rst3, 3
'... do the excel bit here

and that's it

--
Terry Kreft
MVP Microsoft Access

Nov 12 '05 #4

Robin Cushman

You're a lifesaver! Thanks so much, this was just what I needed. I
created a separate module, copied your code in and substituted my
query and report name, and tried to generate a PDF (for simplicity's
sake). Exactly the same thing happened -- I got essentially a blank
PDF with an error upon opening it, and my code ran for 10 minutes
(which is much too long) before I stopped it. Now I know my code
probably wasn't the problem, which is a big relief. It hangs at the
".Export True" line.

Could it be that I don't have something selected in Tools/References
that I should? Here's what I've got currently:

* Visual Basic for Applications
* MS Access 11.0 Object Library
* OLE Automation
* Crystal Report Export 9
* Crystal Report 9 ActiveX Designer Run-Time Library
* Crystal Report 9 Library
* Crystal Report Common Object Model Library 2.0
* MS ActiveX Data Objects 2.5 Library
* MS ADO Ext. 2.6 for DDL and Security
* MS Jet & Replication Objects 2.6 Library

I'm running Office2003/Access 2003 on Windows 2000 Professional,
creating databases in Access 2000 format. Can you/anyone see here
what, if anything I might be missing? If a missing library isn't the
problem, what else might be?

Thanks a million again.
Robin


"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<r_********************@karoo.co.uk>...

Robin,
Here is the whole of the code which I have used to achieve what you want
(this is using CR9, and A2k running from the Access database).

' ********** Code Start **********
Sub AutoGenerateReports()
Dim appl As New CRAXDRT.Application
Dim rst1 As ADODB.Recordset
Dim rpt As New CRAXDRT.Report

Const REP_DATA = "SELECT * FROM Table3"
Const REP_NAME = "C:\Delme CR\test.rpt"

Set appl = CreateObject("CrystalRuntime.Application")

Set rst1 = CurrentProject.Connection.Execute(REP_DATA)
Set rpt = appl.OpenReport(REP_NAME)

With rpt
.DiscardSavedData
.Database.SetDataSource rst1, 3
.Export True
End With

rst1.Close
Set rst1 = Nothing
Set rpt = Nothing
Set appl = Nothing
End Sub

' ********** Code End **********

Try starting with this code as template and then work up. If the code above
doesn't work then there must be something else going on.

The most obvious thing to look for is that the field names in your recordset
match the ones used when creating the report.

--
Terry Kreft
MVP Microsoft Access

Nov 12 '05 #5

Robin Cushman

Nor does it seem to matter whether I set up the report with an
"Access/Excel (DAO)" connection or a "Database Files" connection to
the data...


"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<r_********************@karoo.co.uk>...

Robin,
Here is the whole of the code which I have used to achieve what you want
(this is using CR9, and A2k running from the Access database).

Nov 12 '05 #6

Terry Kreft

Robin,
I've tested this in Access2000 and Access2003 (A2k format) on Win2000
Professional and it works in both cases. The references I have are as
follows.

Visual Basic For Applications
Microsoft Access 9.0 Object Library (or Microsoft Access 11.0 Object
Library)
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Crystal reports 9 ActiveX Designer Run Time Library

The most likely reason why it doesn't work is that a dll is not properly
registered.

Start with the craxdrt9 dll and run through them registering them using
regsvr32. If you still have a problem try reinstalling Crystal.

--
Terry Kreft
MVP Microsoft Access
"Robin Cushman" <ro******@earthlink.net> wrote in message
news:6a*************************@posting.google.co m...

You're a lifesaver! Thanks so much, this was just what I needed. I
created a separate module, copied your code in and substituted my
query and report name, and tried to generate a PDF (for simplicity's
sake). Exactly the same thing happened -- I got essentially a blank
PDF with an error upon opening it, and my code ran for 10 minutes
(which is much too long) before I stopped it. Now I know my code
probably wasn't the problem, which is a big relief. It hangs at the
".Export True" line.

Could it be that I don't have something selected in Tools/References
that I should? Here's what I've got currently:

* Visual Basic for Applications
* MS Access 11.0 Object Library
* OLE Automation
* Crystal Report Export 9
* Crystal Report 9 ActiveX Designer Run-Time Library
* Crystal Report 9 Library
* Crystal Report Common Object Model Library 2.0
* MS ActiveX Data Objects 2.5 Library
* MS ADO Ext. 2.6 for DDL and Security
* MS Jet & Replication Objects 2.6 Library

I'm running Office2003/Access 2003 on Windows 2000 Professional,
creating databases in Access 2000 format. Can you/anyone see here
what, if anything I might be missing? If a missing library isn't the
problem, what else might be?

Thanks a million again.
Robin


"Terry Kreft" <te*********@mps.co.uk> wrote in message

news:<r_********************@karoo.co.uk>...

Robin,
Here is the whole of the code which I have used to achieve what you want
(this is using CR9, and A2k running from the Access database).

' ********** Code Start **********
Sub AutoGenerateReports()
Dim appl As New CRAXDRT.Application
Dim rst1 As ADODB.Recordset
Dim rpt As New CRAXDRT.Report

Const REP_DATA = "SELECT * FROM Table3"
Const REP_NAME = "C:\Delme CR\test.rpt"

Set appl = CreateObject("CrystalRuntime.Application")

Set rst1 = CurrentProject.Connection.Execute(REP_DATA)
Set rpt = appl.OpenReport(REP_NAME)

With rpt
.DiscardSavedData
.Database.SetDataSource rst1, 3
.Export True
End With

rst1.Close
Set rst1 = Nothing
Set rpt = Nothing
Set appl = Nothing
End Sub

' ********** Code End **********

Try starting with this code as template and then work up. If the code above doesn't work then there must be something else going on.

The most obvious thing to look for is that the field names in your recordset match the ones used when creating the report.

--
Terry Kreft
MVP Microsoft Access

Nov 12 '05 #7