I want to save reports in excel with format using script, how? 3

[JBo]

I can write script to run and save a report in excel, but how can I get the script to save the same report in excel with format? Does anyone know the solution?

 

[griffindm]

Use Sendkeys in your macro. Search here on that topic and see the previous posts about this. No API that allows this without Sendkeys (through version 6. I'll check in Series 7 docs late next week. Anyone else know if this changes in Series 7?).

HTH,

Dave Griffin

 

[JBo]

Thanks Dave

I've searched for Sendkeys but I can only find a post about saving excel without format and some rather basic problems. Can you be more specific as to a solution? I currently handle over a hundred reports and automation with Cognos Script has become my 'raison d'etre'.

Many Thanks
James

 

[griffindm]

Here is the sample code off the Cognos Knowledgebase:

Sub Main

Dim objimp As Object
Dim objrep As Object

Set objimp = CreateObject("Impromptu.Application&quot
objimp.OpenCatalog "C:\Test\aaa.cat"
Set ImpRep = ObjImp.OpenReport("C:\Test\aaa.imr&quot
objimp.Visible True

AppActivate "Impromptu - [aaa.imr]"
SendKeys "%F",1
SendKeys "A",1
SendKeys "{TAB}",1
SendKeys "{DOWN 8}",1
SendKeys "{ENTER}",1
SendKeys "+{TAB}",1
SendKeys "D:\test.xls",1
SendKeys "{ENTER}",1

Set objrep = Nothing
Set objimp = Nothing

End Sub

Hope this helps,

Dave Griffin

 

[JBo]

Thanks Dave

I cracked it in the end. This helps no end. I envisage problems with crashes whilst I run these macros. Do you have any suggestions regarding avoiding this and what to do when it does crash?

J

 

[griffindm]

James,

It helps if nothing else if running on the computer. This avoids another app taking focus and receiving the sendkeys input.

Also it helps Win resource utilization if you have Impromptu in single-instance mode (new in version 6). Do this by running the Impromtpu executable ONCE (either ImpAdmin.exe or ImpUser.exe) from the command line with a '/si' on the end (no quotes). From then on Impromptu will open in single-instance mode. Otherwise the default for version 6 is to open another session if one already exists. If you want to set it back to multi-instance, run the command line executable with '/mi' (no quotes) on the end.

I'll let you know next week if the new Series 7 of Impromptu allows API control of this 'SaveAs' option.

Regards,

Dave Griffin

 

[JBo]

Cheers Dave

Here's one for you. I'm creating subreports from quite a complex original. Copying the calculated data elements from the parent report is no problem (as long as impromptu doesn't crash), but how can I copy the report structure from the parent. Quite a quandary, I think you'll agree.

J

 

[griffindm]

James,

I sense from your 'if Impromptu doesn't crash' comment that you too have had the wonderful experience of working hard on a complex report only to have Impromptu bomb on you, usually before you've saved your latest changes.

I'm not sure about the requirement to copy the main query report structure into a sub-report. Do you mean the calculated columns or the physical report layout in page mode? Not sure I have any suggestions that would help.

I have found in my years of working with Impromptu that if you have rights to create data structures in the database, that you can create complex report much more reliably (and usually with less processing time) if you handle the tough parts in SQL on the database, often creating reporting repositories that reduce the 'strain' on the report writer. I know this is more than most report authors want to do, but if you take the time to learn how to do it, there's little you cannot do in terms of report creation.

HTH,

Dave Griffin

 

[JBo]

Dave

It is the physical report layout to which I refer. I have only recently begun working full time with Impromptu and thus I am still on a learning curve. The majority of our company reports were set up some time ago by an external agent and, whilst fulfilling there purpose, they restrict the possibilities of sharing the information company wide to non-Impromptu users. I am currently working on integrating the reports with Microsoft applications to aid the universal distribution of data throughout the company.

My predecessor, whilst knowledgable about HR, was somewhat unambitious when it came to IT solutions. I have a basic programming knowledge and are learning how cognos script and the use of hotfiles can make report production more efficient. I am grateful for your support so far. Thankyou.

My ambition in the short term is the total integration between cognos applications and Microsoft OLE reports. I hope at some point I may of help to yourself, but it is unlikely in the near future.

J

 

[griffindm]

James,

I see from your profile that you are very interested in Cognos Scripts. I do a great deal of work with them, and owe a lot of what I have learned from the inspiration of others. I'll look for your posts in the future to see if I can be of assistance. I did a little work in Yoevil (in the southwest of England) back in the late 1990's. Beautiful country you have there.

On the topic of sub-reports: are you aware of the limitations in placement of a sub-report physically into the page frames? (i.e. that you cannot place it into the list frame area of the main query). This results in most report writers writing sub-reports with small result sets that can easy be placed into report page headers or footers. You can also place multiple list frames into a larger form frame, but things get ugly fast if you are writing for paper (versus on-line) distribution. That said, you may want to re-think what you are trying to do with sub-reports. Another alternative for on-line access is to use drill-thru reports. This way the user can go from one report (that uses the entire page) to another large full-page report that is based on filter criteria parsed from the selected row of the main query.

I hope some of this makes sense. Let me know if I can provide additional information.

Regards,

Dave Griffin

 

[RVLF]

You have to create a macro.

And use the ExportXls Method like :

ObjImpRep.ExportXLS Namefile.xls

Where ObjImpRep is the activedocument of Impromptu application.

The same for pdf with PublishPdf

Easy ! no ?

 

[griffindm]

RVLF,

The ExportXLS method only returns the rows from the query, not the formatting of the report itself. You need to use the SendKeys method above to save as Excel with Format. I have also checked in Series 7 to see if Cognos had added this option to the API, but no luck. It's SendKeys until the next release I'm afraid.

James,

One other thing I didn't mention, but you've prebably seen. In order to use column headers and report titles, you need to embed them into GROUP headers and footer. PAGE headers and footers are not exported to Excel using the 'with format' option.

HTH,

Dave Griffin

 

[JBo]

Thanks again Dave

Yes, I already now this. I'm getting pretty clued up when it comes to Impromptu forum, but there's always something you don't know. So I appreciate your support.

J

 

[donafran]

Dave G,
Thank you so much for posting your script code in your response of June 13, 2002. (thank you JBo for posting the question!).

This script has proved very valuable to me, but I am new at the Cognos Script language and have a few additional questions:
We run the same report every day. When you make the Impromptu app Visible, after your SENDKEYS "{ENTER}",1 command, our app was sitting and waiting for a response to the message "File Already exists. Do you want to replace it?" So I have added:
Sendkeys "Y",1
to our script. This works fine.

PROBLEM: The Impromptu App stays Open, Visible with this report open until someone (me) manually goes to the server and closes it. We run about 6-12 reports every day in this manner. If no one (me) looks at the server and closes them, the open instances of Impromptu stack up and eventually crash the server (like when I am out of the office).
I ahve tried adding the following:
SendKeys "%F",1
SendKeys "x",1
at the end of the script. I get no errors, but it also has no effect. The app stays open and visible. Please help!

Also, on the Sendkeys command where you have the filename of the output file, is there a way to have the system date as a variable part of the file name (so we are able to save previous days reports)? so that your D:\test.xls file might be named D:\test 112002.xls or whatever format the date is used in. And tomorrow, the output would automatically be name D:\test 112102.xls ??

Thanks !
-Dona

 

[griffindm]

Hi Dona,

The closing of Impromptu should be occurring via the lines

Set objrep = Nothing
Set objimp = Nothing

You can also add the command objimp.Quit just before these to make extra sure.

The issue about multiple instances of Impromptu up at the same time (assuming you never want this) can be handled by running the Imp executable (either User or Admin) from a command window with a /si flag at the end. This will make the app run in single instance mode from that point on, until or unless reset using the /mi flag the same way. In single instance mode, a macro will re-use an open instance of Impromptu rather than spawning a new one.

For setting the filename using the date: It's fairly easy to do this. You can specify a dynamic file name using a concatenation of text (such as "C:\ExportFile\&quot and a variable, such as:

x$ = format(date,"ddmmyy&quot

as in: fname = "C:\ExportFile\"+x$

which will give you your date stamp in the format you desire.

Hope this is helpful.

Dave Griffin

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

http://www.decisionsupportgroup.com

 

[donafran]

Dave,
Here is my script. It Functions just fine, except for the fact Impromptu App does not close down. It does not even close the report...(excuse the line-wrapping)

Dim ImpApp As Object
Dim ImpRep As Object

Sub Main()

Set ImpApp = CreateObject("Impromptu.Application&quot
ImpApp.OpenCatalog "F:\Program Files\Cognos\e-Applications 1.0\BI\Catalogs\mssql\BIC_SA_CUSTOM.CAT","Creator"
Set ImpRep = ImpApp.OpenReport("F:\Program Files\Cognos\e-Applications 1.0\BI\Reports\Roller Reports\Roller Shipments2.imr&quot

ImpApp.Visible True

AppActivate "Impromptu - [Roller Shipments2.imr]"
SendKeys "%F",1
SendKeys "A",1
SendKeys "{TAB}",1
SendKeys "{DOWN 8}",1
SendKeys "{ENTER}",1
SendKeys "+{TAB}",1
SendKeys "\\MLFFSS1\IT\OneWorldQueryDoc\Roller Ships2.xls",1
SendKeys "{ENTER}",1

Set ImpRep = Nothing
Set ImpApp = Nothing

End Sub

When I put in the ImpApp.Quit command just prior to the SEt ImpRep = Nothing command, it runs fine up to that point, actually creates the output file. But then it either just leaves Impromptu open anyway, Or gives me one of two error messages : Either the Memory cannot be Read application error or it gives me Error Code 1003 - Exception not handled by program.

The only time it actually closes down the app is when I generated the Memory Error.

Also - I tried using your dynamic file name in the SendKeys statement -- it doesn't gets stuck where it should be replacing the old filename with the new file.

 

[griffindm]

Dona,

Looking over the script code, I think I see where the root cause of the error may be. The SendKeys command may be 'overlapping' with the SetImpRep = Nothing statement. I would try to insert another ImpApp or ImpRep command between the two, even something innocent like objimp.Visible True. If this didn't solve the problem I would put in a delay loop of about 5 seconds to slow it down between the last SendKeys and the next statement.

Try this and let me know how it goes.

Regards,

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!