Launch Cognos files in Excel

[Lagoon1]

Hi
I need help launching files from Cognos to Excel via a macro button.

I've tried running a macro from excel to extract data but with the following problems:

1. Seems to work once and not the other. (Is there a problem with the IP address?)

2. Data extracted comes up with the sort ascending / descending arrows which is a bit of a pest.

Anyway, any ideas?

Cheers,

Lagonn1.

 

[drlex]

Lagoon,
are you trying to bring data from Impromptu, PowerPlay (Client or Web) or another Cognos application into Excel from a macro launched within Excel?
Do you get any error messages, and if so, what are they and what is the code that results in them being given? Or is it that you are getting null files?
lex

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

 

[Lagoon1]

Hi Drlex
Thanks very much for your reply.

I am actually trying to bring in data from Powerplay.
Below is the vba code that i used in the vb editor in microsoft excel.

Sub URL_Test()
With ActiveSheet.QueryTables.Add(Connection:="URL;

http://This/Is/a/test/",

Destination:=Range("a1"))
'write web-page to sheet
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub


1. Is it possible to exclude all the hyperlinks and also when the data is copied into excel, it seems those drop-down arrows turned into text with hyperlinks (i.e. sort descending or ascending).

2. Does anyone know of a method to extract the data via the url address from Cognos Cubes? I've tried successfully, but it seems that the url address is not static and keeps changing dynamically. Is there any way of overcoming this problem? I've asked the IT people in the company that i worked in and was told that the url address changes due to the fact that the data is updated / refresh every now and then and thus, the url address would change as well. I am not sure if this is the fact because if I am able to bookmark the desired outcome in my favourites, I'm sure that there is a way to extract this info.

Any help will be greatly appreciated.

Thanks.

Rgds,
Lagoon1.

 

[drlex]

Lagoon1,
I agree with your IT people - the URL is created on the fly by PPES for use in upfront, so you've no chance of setting up a static address to read from.

If you need to get out info, perhaps you could consider alternatives:
You can access cubes from excel by use of a PP add-in for excel. Since it is a VBA program, you should be able to automate manipulation (never tried it myself).
If there are static views you wish to 'suck into' excel, can they not be generated as PP reports and saved as excel views automatically to be 'worked over' in excel later?
If you looking to provide a macro for an excel user to grab the data from an open Upfront session, then it would seem likely that you could 'read' the open page and process out the non-factual content.

From the Cognos side, it might be easier to set a script that dumps the contents out in .csv in a named file for excel to pick up after. This script would need to be in JavaScript. (I can't currently recall the steps to activate a scriptlet in PPES, but I know it's been discussed on this forum previously).

Good luck and post back - it's an interesting task.

["] Veni, Vidi, Velcro. ["]

 

[Lagoon1]

Hi Drlex
I will try to see if there is anything that I can do.

Otherwise, I might have to come back and see if you can come up with anything else.

Thanks very much for your help.

Cheers,

Lagoon1