Open Crystal Reports file from Excel

**computermike** · 05-12-2010, 10:46 AM

Hi,

I wanted to to create a small macro that would open a crystal reports file similar to clicking on the file in windows explorer. The report's path and name would be in a cell for example "C:\Reports\sales.rpt". You click a button and that file open's the report in Crystal, ready for me to work on.

**Leith Ross** · 05-12-2010, 11:38 AM

Hello computermike,

Welcome to the Forum!

Provided Crystal Reports is installed on the computer, this macro will work. Change Range("A1") to the cell on the active sheet you will be using.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Add the Macro to the Command Button
1. Right Click the Button.
2. Click View Code...
3. A new window will open in the Visual Basic Editor like below

Please Login or Register  to view this content.

4. Type in the macro's name. You code you should like this...

Please Login or Register  to view this content.

5. Press Ctrl+S to save the changes.
6. Press Alt+Q to return to Excel.

**computermike** · 05-12-2010, 01:23 PM

Tried code, but got error

Run-time error '5':
Invalid procedure call or argument

Also tried hard coding name and path, but got same error

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Tried Shell "Calc" brings up calculator ok????

**Leith Ross** · 05-12-2010, 01:27 PM

Hello computermike,

If you can Shell another program then Crystal Reports is not loaded on your computer. Shell will only run the program if it is installed and has a registered file extension.

**computermike** · 05-12-2010, 03:37 PM

I have Crystal on my machine. I use it everyday. Where would I look to determine if file extension is registered? Also, when I double click a report in Windows Explorer it opens fine, so that tells me file extension is registered. Any ideas???

**Leith Ross** · 05-12-2010, 03:50 PM

Hello computermike,

Well, we'll go to plan "B" - the API! Copy this code into a VBA module and then run the macro RunReport. If this doesn't work then I have no idea why it won't run.

Please Login or Register  to view this content.

**computermike** · 05-12-2010, 03:55 PM

The last code pasted works, Thanks

Not sure how to mark Solved????

**Leith Ross** · 05-12-2010, 04:04 PM

Hello computermike,

If your questions have been answered to your satisfaction, please mark this thread as solved.

How to Mark a thread Solved
1. Go to the first post #1
2. Click Edit
3. Click Go Advanced
Just below the word Title you will see a drop down with the words "(no prefix)".
4. Click the drop down and select "[Solved]"
5. Click Save

**Jlatimer** · 09-28-2012, 02:45 PM

Originally Posted by Leith Ross

Hello computermike,

Well, we'll go to plan "B" - the API! Copy this code into a VBA module and then run the macro RunReport. If this doesn't work then I have no idea why it won't run.

Please Login or Register  to view this content.

Hello,

I apologize for resurrecting an older solved thread but I have a question about this VBA code. When the macro runs, it opens the actual Crystal program outside of Excel. Is there a way to streamline the export of the data into the workbook? Ideally I would like one sheet to have the raw data so I can simply have a pivot table on another sheet based on this.

Thank you,