From Crystal to Excel - Data coming from an ODBC using a vb macro

[Ghis123]

Hello,

I'm developping a usefull vb macro for Excel.
I want to open a .rpt file created with Crystal Report 8.5, the data is extract from an ODBC database installed on a AS400.
What I'm able to do:
#1- I can open the .rpt file in Crystal, update the data and save everything as an excel spreadsheet.
#2- I can use my excel-macro on a computer were Crystal is already installed and do the same as in #1.

What I want to do is to do #2 but on a machine were Crystal is not installed. I already looked in runtime.hlp file to find the required dll. I think I would need p2sodbc.dll but I don't know how to register correctly dll's. In Excel I've installed the CRXDTL.dll.
My best wish would be that my macro open the .rpt file and use the database path and info contain in that file.
I give below my code so it can be used by others and also for troubleshouting. Do I need any other librairies or command?

I used the tips ".discardsaveddata" in order to constrain the program to download new data from the database.

Thanks for help!!

Ghis123

Sub from_AS400_to_Excel()

Dim crystal1 As New CRAXDRT.Application
Dim rapport As CRAXDRT.Report
Dim exportoptions As CRAXDRT.exportoptions


Set rapport = crystal1.OpenReport("h:\ghis\SFO.rpt", crOpenReportByTempCopy)
Set exportoptions = rapport.exportoptions

'Tips to constrain the download of new data
rapport.DiscardSavedData  

With exportoptions
  'define destination -> here disk file
    .DestinationType = crEDTDiskFile        
  'path for output file
    .DiskFileName = "h:\ghis\testB.xls" 
  'define output format -> here excel 8.0
    .FormatType = crEFTExcel80Tabular   
End With

'Download data and export to excel -> false mean no 
'parameter windows
rapport.Export False  
End Sub

 

[synapsevampire]

There could be numerous DLL's required.

Here's a whitepaper that might help:

http://support.crystaldecisions.com/library/kbase/articles/c2009907.asp

-k

 

[SurfingGecko]

Are you getting an error? If so post that so we can see it.

You will want to refer to the runtime.hlp file and see what the RDC requires (less the crviewer.dll) for running on a client machine.

You will also need the p2sodbc.dll for data access and the client will need the DSN configured and accessible along with the database client software.

For a shortcut to registering DLL's have a look in the FAQ for this forum. I posted one on this topic today.

The article synapse... mentioned is also something certainly worth a look.

As a side note the p2sodbc.dll does not need to be registered not does the export driver.



Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

Home of Crystal Ease