Saving Impromptu Report in Excel with format 1

[mukesh]

Can anybody pls help, its urgent.

I want to automate my reports by saving it in excel with formats.

But this is possible only with File>Save As.

It takes lot of time to do my work.

I want to wite cognos script which will save the report in Excel with format.

I am using Impromptu Ver. 6
Pls Help????????????

 

[netprw]

the only way to currently do this is to write a macro that issues sendkeys to impromptu to perform this function. It can not be called directly as with all the other "SAVE" methods.

 

[cognos]

Sub Main()
Dim objImpApp as Object
Dim objImpRep as Object
Dim strExcelFileName As String
Set objImpApp = CreateObject("CognosImpromptu.Application&quot
objImpApp.Visible True
Set objImpRep = objImpApp.OpenReport("C:\macro.imr&quot
strExcelFileName = Left$(objImpRep.FullName, Len(objImpRep.FullName) - 4)
objImpRep.ExportExcel strExcelFileName & ".xls"
'objImpRep.CloseReport
'objImpApp.Quit
Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub
Thanks, Prasad
reachprasad@yahoo.com

 

[griffindm]

Nice code, but this only saves the normal, query level Excel output. NETPRW is correct in the only way to do Formatted Excel is via SendKeys (this per Cognos support online).

Regards,

Dave Griffin

 

[jrrala]

Hi -
I have tried using sendkeys in a script to save Impromptu reports as excel with format.
I have found that they are not 100% successful, sometimes they will overwrite an existing file fine, other times it will not.
Is there any alternative to sendkeys - something a bit more up to date and reliable?

 

[griffindm]

Impromptu Series 7 still does not support Formatted Excel via the API. However, If you are having trouble with overwritting existing files, I suggest you test for the file's existance using Dir() and if found, delete it with Kill macro command before you invoke the SendKeys series.

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[dalex17]

Dave,

Could you please give me an example of code that will search and kill the existence of a file??

Thank you,

dalex

 

[griffindm]

dalex,

Try:

ChkFile = Dir (LocCopy)
If ChkFile <> &quot;&quot; then
Kill LocCopy
End if


Hope this helps.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com