Macro help!!!

[Swathi37]

Hi All,

I am new to macros in Impromptu 7.1. I have a question.
How do I export the Impromptu report to Excel with format. In
Excel the first line should have a text. Ex: "Report By Date" in
bold. The 2nd line should have the dates from the prompts I use
for the .imr report and the third line should have the report data
from .imr. And also the sheet name should be given using a macro.
Is this possible? I mean to say how can I insert the report data
from .imr to excel exactly on the 3rd line ( cell A3)? Since the
contents of the list frame does not get exported to Excel. I would
like to insert the text exactly on the first line of Excel through
macro.
Please help. Your help is greatly appreciated.

Swathi

 

[doodvat]

I have exactly the sam issue. It seems as if the Macro exports the report to version 3 (!!) of Excel. I am using the following command:

'Save as excel
strExcelFileName = Left$(ImpRep.FullName, Len(ImpRep.FullName) - 4)
ImpRep.ExportExcel strExcelFileName & ".xls"

Anyone out there with any ideas?

 

[Mailpack]

This is only possible using Series 7 Version 2 upwards.

I got this working yesterday.

Set ImpRepObject = ImpAppObject.ActiveDocument
ImpRepObject.ExportExcelWithFormat ("\\Gossips\cognos$\Cognos\Live\User Downloads\SD0002 Estimates Raised Yesterday " & Date$ & ".xls&quot

I use this to automatically produce a report for yesterdays information and give the date as part of the file name.

Regards

Roger Pearson

 

[Swathi37]

Hello All,

Thank you for your response. In fact this problem seems to be very serious. I really didnt find the solution to this macro problem. I had contacted the cognos support team. All they say is there are no Excel functions to export the data exactly to a particular line.

The problem is exporting the data from .imr to Excel works fine but I have to add the heading(Report Heading) manually. Sometimes I need to export 10 reports in one excel workbook and manually add 10 different headings for each worksheet. Any ideas to solve this problem? Can we do this through macro?

 

[griffindm]

Swathi,

See thread401-292274. There is a way to automate the Excel with Format in versions prior to 7.1 with SendKeys. Not pretty, but it works. 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.

Hope this helps,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[Swathi37]

Thank you Dave,

Can you please explain how can I embed the report titles in group headers and footers. There is one more problem I face while creating reports in Impromptu 7.1. I dont find any option to insert a new page as a result each report has to be created as a new .imr. This process is bit cumbersome when sending it out as .pdf. Any help greatly appreciated.

Regards
Swathi.

 

[griffindm]

Swathi,

When you take the approach of inserting your page title information into a group footer, it may render the report less useful when viewed directly in Impromptu (warning). To do this make the highest level of group headers visible in Impromptu (if it is not already) by selecting the primary list frame and right clicking and selecting 'Properties | Layout'. Check the highest group header is not already checked. Also set this header to repeat on every page. Then in your report resize this header to the size of the page header and drag your report title and similar text frames into the group header. If you are using column titles you may be able to insert them into the header ('Insert | Column Headers). I'm not certain this will work in all recent versions of Impromptu or not, but it's worth a try. If not, create text frames to do the same thing.

This should do the trick for you. It will NOT create Excel pagination for you, as Excel and Impromptu use completely different methods to make page headers and page breaks.

Regards,

Dave Griffin


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

http://www.decisionsupportgroup.com