Excel Interaction with Cognos 1

[xlbo]

Thanks to drlex (via the archives) , I have the following code to open cognos from excel:

Set objPPRep = CreateObject("CognosPowerPlay.Report&quot
objPPRep.Open "K:\PPreports\dailysales.PPR"
objPPRep.SaveAs "K:\xl\dailysales", 4 'Excel
objPPRep.Close

Background - we have just bought another company who use Cognos. We don't. At the moment we are trying to integrate but senior managers still want information. Ergo, I have to find a way of getting cognos powerplay data into excel. I know it can be done manually by doing save as - excel but I'm an automation freak and the thought of having to manually save a bunch of reports sends shivers down my spine ;-)

I am sure that the code above works but unfortunately, it would appear that the laptop I have been given to work on this has cognos but not the DLL files necessary to enable me to make a reference to it from excel (VBA). Does anyone know if
a: I can download the DLL files from somewhere
or
b: they could send me a copy of the DLLs

This particular machine is running XL 97 (SR 1) on win2000

TIA

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

Or - I'm sure the DLL files are there somewhere - if someone could post which files are necessary, I reckon I can make excel reference them...
Ta

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[drlex]

xlbo
Not too sure which is the correct .dll, as I work the other way (referencing excel from cognos). If Cognos and Excel have been installed properly on the laptop, then one should be able to reference each from the other.
A quick scan of installed .dlls shows a possible requirement: do you have an file "ExcelRendererClient.dll" on your lap top - (path will be the \Cognos\cer*\bin, where * is a number between 1 & 3 depending on version of Cognos))?
lex

"Time flies like an arrow, but fruit flies like a banana."

 

[xlbo]

Will look for that .dll
In the meantime - do you know what the reference name would be for cognos in excel VBA ?? eg for referencing Access, you need to tick the "Microsoft Access X Object library. I am assuming itwould start with "Cognos....." but if that isn't the case, it may be that it is there and I just can't find it ;-)

Cheers for the reply

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

Hmmmm - dunno what this other company was playing at but the only Cognos folder I can find has a direct subfolder called Powerplay 6.5 or there's a Cognos Shared one....

Nothing that looks like cer1 .....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[drlex]

Is there a path under Program Files?
Usual (full) path is C:\Program Files\Cognos\Cer*\bin

Oh, I found this as a macro to run in excel to call a PowerPlay report and save it in excel. 'Fraid I haven't tried it yet.

lex
---------------------------------------------------------

The following macro, when run from Excel 5.0 or 7.0 will Open a PowerPlay Report, save it as Excel 7.0/5.0 format, and open it in Excel. To avoid seeing PowerPlay open and close, comment out or remove the indicated line.

Sub ImportPowerPlay()
Dim PowerPlayReport, PowerPlay As Object
Dim ReportName, WorkBookName, OpenDir, SaveDir As String
Dim ReportPath, WorkBookPath As String

OpenDir = "d:\"
SaveDir = "d:\"
ReportName = "PPlay1.ppr"
WorkBookName = "PPlay1.xls"
ReportPath = OpenDir & ReportName
WorkBookPath = SaveDir & WorkBookName

Set PowerPlayReport = GetObject(ReportPath)
Set PowerPlay = PowerPlayReport.Application

'Remove the following line, if desired
'--------------------------------------
PowerPlayReport.Visible = True
PowerPlayReport.SaveAs WorkBookPath, 4, True
PowerPlayReport.Close
PowerPlay.Quit
Set PowerPlayReport = Nothing
Set PowerPlay = Nothing
Workbooks.Open WorkBookPath
End Sub

"Time flies like an arrow, but fruit flies like a banana."

 

[xlbo]

Just done a search and there's no "ExcelRendererClient.dll" anywhere on the machine. Bloody cowboys :-( D'you reckon the Cognos website will have downloads ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

Timing - nope - under C:\ProgramFiles\Cognos, the next level is "Powerplay 6.5"

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[drlex]

Looking at the other .dlls, I suspect that the renderer may well be for another cognos product, Impromptu, and not powerplay.

AFAIK:
If PowerPlay Client and Excel are both installed on the same machine, then one can create a report against a data cube in PPC and save it in XLS format to open in Excel. If PPXLSrv.exe and PPXLServer.xla are present and activated in Excel, then one can open reports and cubes _within_ excel (no calls to PPClient).

I'm happy to email you components, but I'm on a higher version of cognos than you. This probably won't matter for the PPXL components, though.
(Otherwise, if you can't find installation disks, you ought to be able to get them from the company's reseller.)

I'm toddling off now, but happy to pick this up tomorrow. We can exchange details and speak on the 'phone if it's easier.

lex

"Time flies like an arrow, but fruit flies like a banana."

 

[xlbo]

No worries lex - I was just trying to save myself some time. I think I'm just gonna take manual data dumps from Cognos, lob 'em into excel and then (in the words of my boss) "Macroize the cr*p outta them"

It looks like they've done a half baked installation and it'll probably take more time for me to try and figure it all out than to just manually dump the data

I'll have a look for the PPXLSrv.exe and PPXLServer.xla though - that could be quite useful

Cheers for all the help / advice and have a star for the code & syntax - I'm sure I'll be using it at some point as we're planning on keeping Cognos in some form or other

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[drlex]

xlbo
You're welcome and thanks for the star. The offer to help still stands - wake me up on the TTUK forum if required.
lex

"Time flies like an arrow, but fruit flies like a banana."

 

[xlbo]

no worries - if I get stuck with something specific, I may just take you up on that

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244