Opening and running reports from VBA 2

[rainbowrabbit]

I am new to the objects in Crystal reports and I am having trouble even getting started opening and running from VBA.

I want to run a report that imports directly to an Excel spreadsheet via VBA.

When I use

Dim cryrep2 As CRAXDRT.Report

Set cryrep2 = cryapp.OpenReport("H:\WeeklyUpdates\EDdiversity\Test_ED_Div.rpt")

and then;

cryrep2.Export

I get the error after specifying how I want to export;
"Server has not yet been opened"

Crystal is looking at an Oracle database but when I try to open that using connection object of ADODB I get an error about tnsnames?

Is their something fundamental I am doing wrong (probably?!), does anyone know about any good books/resources on the subject?

Thanks in advance...

 

[glalsop]

At the very least, you will need to declare an Application object and use that object's LogOnServer method to log on to the server where the Oracle database resides.

Try something like this:

Dim crxApp As New CRAXDRT.Application
Dim crxReport As CRAXDRT.Report

Set crxReport = crxApp.OpenReport("H:\WeeklyUpdates\EDdiversity\Test_ED_Div.rpt")

crxApp.LogOnServer "Your server LogOn Info"

With crxReport
    With .ExportOptions
        .DiskFileName = "YourFullExportPathIncludingFileName"
        .DestinationType = crEDTDiskFile
        'You may need to adjust the Excel settings here
        .FormatType = crEFTExcel80
    End With
    'Don't promt for export options
    .Export False
End With
    
crxApp.LogOffServer "Your server LogOn Info"

Set crxReport = Nothing
Set crxApp = Nothing

Also, you may want to look at Microsoft Query, which comes with most distributions of Excel. By using Crystal Reports to populate an Excel worksheet with data, you are using a middle man that you probably don't need.

-Gary

 

[rainbowrabbit]

thanks Gary I tried this out but I get a different error message,

when I get to the line;

crxApp.LogOnServer "<my server logon string>"

I get the message;

"Server has not yet been opened".

I have put in all the details to my Oracle server inside the speech marks but it seems to need something else initialised before I can go past this step?

 

[glalsop]

And you passed everything as a string value, including the password?

Perhaps you could post that line of code, and replace passwords, etc, with "abcd", or something like that.

I believe that the method states that userName and password are optional, but of course if the database requires it they really aren't.

-Gary

 

[glalsop]

Here are a couple of Business Objects whitepapers discussing this error, and possible resolutions.

http://support.businessobjects.com/communityCS/TechnicalPapers/cr_server_not_yet_been_opened.pdf.asp

http://support.businessobjects.com/library/kbase/articles/c2006666.asp

-Gary

 

[rainbowrabbit]

Thanks for your help on this Gary.

cryapp.LogOnServer "pdsora7.dll", "abc", "abc", "abcdefg", "abcdefg"

'cryapp.LogOnServer "provider = msdora;data source = abc;user id = abcdedfg;password = abcdefg"

I have to use the top line in my code as when I put it into one string I get the message that I have left out a required parameter (server name). "abc" is the server name "abcdefg" is the username and password (high security system this!).

 

[glalsop]

And this reports runs fine from the Crystal design view, if you run it stand-alone?

-Gary

 

[rainbowrabbit]

yeah the reports run ok. The first time you logon to the server you get the server logon and the report refreshes.

I have even tried to link directly to Oracle from Access using an ADO connection, this is when I got error messages about tnsnames.

 

[rainbowrabbit]

Gary the solution to my problem was on the business objects support page, instead of using the logonserver method of the database I used the setlogoninfo method for the tables, the code you sent works a treat!!

Thanks very much!!