launch Excel (VBA) macro from cognosscript

[steever]

Hi,

Any ideas on how i can launch my vba macro from cognosscript?? It needs to be seamless. Basically the user
selects options via a cognos macro, cognos macro runs and exports report contents to excel. I have a vba macro that does some additional formatting work that i need to have kick off with no user intervention between the finish of the cs mac and the beginning of exels.

your ideas are appreciated, thx.

Steever

 

[drlex]

Steever,
Can you not just incorporate the contents of the vba macro into the cognosscript?
lex

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

 

[DeWegenwachter]

Lex said:
Can you not just incorporate the contents of the vba macro into the cognosscript?

You can also incorporate the contents of your cognosscript into the vba macro !!! With a little adjustment you can copy-paste your Cognosscript into VBA !!!

DeWegenwachter

 

[drlex]

Also true!

Just to test the ability to call an excel macro within cognosscript:
If the macro is saved in Personal.xls and objExcel is defined as an object in the script, then code like this will work:

Set objExcel = CreateObject("Excel.Application&quot
objExcel.Visible = 1
objExcel.Application.Workbooks.Open "C:\Documents and Settings\lex\Application Data\Microsoft\Excel\XLSTART\Personal.xls"
' the path to the workbook that holds the macro
objExcel.Application.Workbooks.Open "C:\test.xls"
objExcel.Application.Run "PERSONAL.XLS!Macro1"
' macro1 = name of macro required
objExcel.Application.DisplayAlerts = False
objExcel.Application.Workbooks(2).SaveAs "C:\test.xls", Fileformat:=-4143
objExcel.Application.Workbooks(2).Close
objExcel.Application.Quit

(The save as fileformat is to get Excel 7.0/2000 format from Cognos's 5.0 rendering)

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

 

[steever]

hey guys, thx for the ideas. Drlex, I shall try your latter
suggestion tomorrow morning. I've tried something similar
but could not get around the excel prompt.

Cognos's, official response to my question, was that the only way to get around the prompt was to set the security in Excel to low ?!! (not sure about that?). Doesnt seem right?

I cant move one macro to the other for a few reasons. We need the flexibility of excel to enable us to manipulate columns in a report that have been layered. I'm summarizing layers for some columns but not for others (across these layers) and it goes against PPlay's functionality to have one column in a layer not nesting according to the rest. As
a result, i've had to ship the columns out to excel (summarized and not) and fudge the output accordingly.

The CognosScript macro I've worked on is part of a much bigger front-end macro so I cant really get rid of the cognosscript piece either.

Anyways, thx, will keep you posted.

steever

 

[steever]

drlex,

i tried your code and it appears to work. Great, thx!

The new problem i get now is the old 'object error 440'. I know your code is doing its job as when i put a msgox "hello" at the beginnning of my excel code it came up fine and with no prior Excel prompts to enable macros. Its
getting to the right place, but not going through my code that works fine when run from the vba editor.

Any ideas on this one?

cheers

Steever

 

[drlex]

the new problem i get now is the old 'object error 440'
Steever - glad you're getting somewhere, but what's the full message and what's the line of code that generates it?
Are you sure that you can't copy the contents of the excel macro into the cognos script - you 'll be able to debug better!

lex


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

 

[steever]

drlex,

thx a lot for your help - I've managed to get things worked. I had set the object.visible property to 0 earlier on in the code to get it running faster during testing and forgot to set it to 1. Cant quite get wrapped around why this is the fix (if its not visible, it cant act on it?), but she runs like a dream!

Many thx.

Steever

 

[drlex]

You're welcome - thanks for reporting back, as it is good to know a solution has been reached, especially if this thread is searched for later.
lex

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