Copy specfic Layer with macro

[bebbo1986]

Hallo,
I use VBA to control cognos and then copy the report in Excel. Everything is working very well so far.
But now I have different layers and want to copy the values from a specific layer.
When I write:
PowerPlayReport.Copy
always the first layer is copied.

This won't work:
PowerPlayReport.Layers.Item(3).Activate
PowerPlayReport.Layers.Item(3).Copy
The third layer is here indeed activated and will be shown in cogonos but the command copy-command is not allowed.

This way:
PowerPlayReport.Layers.Item(3).Activate
PowerPlayReport.Copy
won't work either. Here still the first layer is copied althogh the third is shown.

Could someone help me, please? Thanks.

 

[drlex]

try using .Select to shift the focus.

soi la, soi carré

 

[bebbo1986]

With .Select nothing is happening at all.

 

[drlex]

Well, since .copy copies to clipboard, unless you do a .paste into a reporter report or paste from clipboard into another application, I wouldn't expect anything else to 'happen'. I know that "Works for me" isn't hugely helpful, but at least it does work.

soi la, soi carré

 

[drlex]

If you create a simple layered report (with at least 4 layers), save it on the root of C:, what happens when you run this:

Sub Main()
   Dim objPPRep AS Object
   Set objPPRep = GetObject("C:\Sample.ppr" )
   objPPRep.Visible = 1
   objPPRep.ExplorerMode = 0
   objPPRep.Layers.Item(4).Activate
   objPPRep.Layers.Item(4).Select
   objPPRep.Copy
   objPPRep.Paste
   Set objPPRep = Nothing
End Sub

?
(You'll have to close PP client manually)

soi la, soi carré

 

[bebbo1986]

When I run this the same thing happens. Only the first layer is copied.

 

[bebbo1986]

And if I use
objPPRep.Layers.Item(4).Activate
objPPRep.Layers.Item(4).Select
Selection.Copy

Only the name or headline of the layer is copied.

 

[drlex]

Other than inserting a .Unselect statement prior to the .Activate, I can only think of suggesting an upgrade to 7.4.
FWIW, there is a document on the Cognos support page, # 73364.4 entitled "Macro to copy all layers into an excel spreadsheet", which for a single layer, uses the .Columns.Select, followed by .Rows.Select, but then the focus is already on the required layer, as the macro looks to be run from inside the report.

Out of ideas, sorry!
lex

soi la, soi carré

 

[bebbo1986]

The .unselect worked.