Impromptu Macro - HELP!!!!

[fishax]

Hi All,
I want to do something really simple. I want to Open a Report in Impromptu, then RUN A MACRO that will save the report as an excel file to a SPECIFIED PATH. The excel file should have the SAME NAME as the original report except with extension .xls. I am able to save the file
to the original path that I opened report from, but to a specified path...haven't been successful. For example below, I want to save the Report to the C:\ location with the same filename as the report e.g. c:\report.xls

Sub Main()
'declare variables
Dim objImpApp As Object
Dim objImpRep As Object
Dim strASCIIFileName As String
Set objImpApp = CreateObject("Impromptu.Application&quot
'retrieve the active report
Set objImpRep = objImpApp.ActiveDocument
'get the report name and path
strASCIIFileName = Left$(objImpRep.FullName,
Len(objImpRep.FullName) - 4)
'export as an ASCII file

objImpRep.ExportASCII ("c:\" & strASCIIFileName & ".xls" ) --> {*****HELP!!!THIS IS WHERE THE PROBLEM LIES********)

'release variables
Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub

Can anyone Help Point me in the right direction. Am using version 5 of impromptu

 

[griffindm]

What is the macro error message you receive? The basic code looks okay, but I'll try to compile and run it here. I no longer have version 5, so I may not be able to replicate the error if it's at the API level.

Remember that using this method, you will only get a raw data dump of the results of the report query, not an Excel representation of the formatted report. That is not possible until version 6, and you have to use a SendKeys workaround to do it in a macro. Do a keyword search on "Excel" in this forum and you should find the previous help on how to do this.

Hope this helps,

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

http://www.decisionsupportgroup.com

 

[fishax]

Hi,
I don't get a compile error, but when running the Macro from within Impromptu, I get an error msg (sorry, I cannot remember it...something Expression related).

Don't quite understand. You are saying that if I ran the same code with version 6 (provided ofcourse I get the saving path and file name to work), this would give me a true representation of the formated report?

 

[griffindm]

Nope,

What I am saying is that version 6 has the ability to create a true formatted Excel version of a report, but it is only available from the menu, not from a macro API call. You can simulate menu keyboard entry from within a macro using the SendKeys command. An example is available here in the forum. Just use the Keyword search on "Excel format". That should get you to the thread that discusses it.

It would help to diagnose your runtime error if you could provide the exact error message.

Regards,

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

http://www.decisionsupportgroup.com

 

[fishax]

Hi Guys,
Here's the solution that worked.....

> Sub Main()
> 'declare variables
> Dim objImpApp As Object
> Dim objImpRep As Object
> Dim strASCIIFileName As String
> Set objImpApp = CreateObject("Impromptu.Application&quot
> 'retrieve the active report
> Set objImpRep = objImpApp.ActiveDocument
> 'get the report name and path
> strASCIIFileName = Left$(objImpRep.Name,
> Len(objImpRep.Name) - 4)
> 'export as an ASCII file
>> objImpRep.ExportASCII ("c:\" & strASCIIFileName & ".xls&quot
> 'release variables
> Set objImpRep = Nothing
> Set objImpApp = Nothing
> End Sub


One more question....It was stated that SendKey would preserve the formatting. I don't really care about formatting, but DO care about FILTERS getting captured in the saved file i.e. the file produced does take into account any filters at the report level. I am assuming that the above method will correctly provide the SAME information as the Impromptu report that is run. Is this correct?

 

[griffindm]

fishax,

You're making this cryptic. Nothing changed in your ExportASCII line except the omission of a space before the last close parenthesis. Is this supposed to be the fix? Please explain.

Yes, any method for exporting the data (HTML, PDF, Excel, ASCII, formatted Excel) exports only the results returned after the filter is considered.

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

http://www.decisionsupportgroup.com

 

[fishax]

Hey Dave,
Ok...here's the difference

strASCIIFileName = Left$(objImpRep.Name,
> Len(objImpRep.Name) - 4)

What I had before was
strASCIIFileName = Left$(objImpRep.FullName,
Len(objImpRep.FullName) - 4)

The Fullname property not only captures the FullName, but the complete path of where the report is at. Changing to NAME made the difference.

Hope that helps. Thankyou for your assistance and input.

 

[griffindm]

fishax,

Thanks for clearing that up. I was looking at the wrong line of the code (with the '>>') thinking you had added the '>>' yourself for emphasis on the changed code line.

Regards,

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

http://www.decisionsupportgroup.com

 

[fishax]

Here we go again....more Impromptu issues.

Using the working code above, I had to generate csv file. This works great but there is an issue. It seems like the csv file is not created correctly in version 5 of Impromptu if there are BLANK fields. The data is getting shifted.

For Example

Column1 Column2 Column3
A 1 A
B 2 B
C C

the csv or txt file that is created has

Column1 Column2 Column3
A 1 A
B 2 B
C C

(the data gets shifted). Is there a fix for this problem?

 

[fishax]

Hi,
Just want to add (from further investigation)....it is when one of the column data has a comma in it, that things are shifting to the right.

e.g.

Column1 Column2 Column3
A,A 1 A

The shift for csv file is as follows
Column1 Column2 Column3
A A 1 A

This is the dilemma. Any way to get around this mess from within the Macro?

 

[fishax]

Hi,
Just want to add (from further investigation)....it is when one of the column data has a comma in it, that things are shifting to the right.

e.g.

Column1 Column2 Column3
A,A 1 A

The shift for csv file is as follows
Column1 Column2 Column3
A A 1 A

This is the dilemma. Any way to get around this mess from within the Macro?

 

[fishax]

Sorry......

Just to correct myself...the shift is to the RIGHT, and it is happening when there is a COMMA in the data.

E.g.
Column1 Column2
A,A 1

In the csv file, I get
Column1 Column2
A A 1

Anyway to get around this comma issue in the data itself in the macro so that the data/column info is always accurate?

 

[fishax]

Seem to have some posting issues...

The shift is to the RIGHT, and occurs when there is COMMA in the data.

So for e.g. C,C is the data for Column1

then on the csv file, I am getting it separated as
C for data in Column1, and C for data in Column2 (i.e. it is inaccurate csv file).

Any fix in Macro to resolve this kind of an issue?

 

[griffindm]

If your use of the CSV file can handle, try calculating the text column with embedded double-quotes:

'"'+Column1+'"' ...

CSV uploads that can handle quoted strings ignore embedded commas within.

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

http://www.decisionsupportgroup.com

 

[fishax]

Hi Dave, forgive my ignorance, but not quite sure what you mean. Are you saying that the data where the comma is present should be in quotes (in the database itself i.e. "A,A"?????)

Please note that I am simply performing an equivalent to a "SAVE AS" and generating a csv file in Impromptu. Since the data itself in the database has commas, this is shifting the correct values per column. Ofcourse, the length of the information in the Column will vary according to the data inputed by the user e.g. A,A or A,A,A,A.

Again, can you please clarify what you mean so I eagerly try what else can work.

 

[griffindm]

fishax,

Many applications that use CSV files can handle text strings bracketed by double quotes ["]. If your application for the CSV file can handle these, by changing the text column definitions (within "Data Definition" in Impromptu) you can force the column to appear with double quotes. Thus:

Column1 Column2 Column3
AB,CD 5 EF,GH

Becomes

Column1 Column2 Column3
"AB,CD" 5 "EF,GH"

And the CSV export goes from:

AB,CD,5,EF,GH

To:

"AB,CD",5,"EF,GH"

In most applications that import CSV files, the first export would try to populate five columns of data. The second with the double quotes will only populate three.

HTH,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[fishax]

Thankyou, I will give this a try.

I am able to generate an xls (Excel) file through Impromptu using SendKey, but the application I am exporting the Excel file into is not taking the xls file since for some reason, since the Impromptu 5.0 saves xls in version 3 of excel.

Can one not, using the Cognos Macro, generate the xls file, then somehow, get Excel to open it and Save it again in Excel97 format or even a csv? Just wondering if this can be done right from within the Macro. If so, any pointers would help.

 

[griffindm]

fishax,

If you are using version 5, you should not have to use SendKeys to save to Excel, as this is only required for the "Formatted Excel" option, which does not appear until version 6. The normal:
objImpRep.ExportExcel strExcelFileName & ".xls"
method should work fine. This will not preserve formatting, but it's all that's available in version 5.

Another option on the embedded commas is provided by the Cognos Knowledgebase. It recommends changing the default delimiter from a comma to a period. This is set via an option in the SaveAs screen of Impromptu. I would prefer to change it to something more obscure, like a pipe "|" which would be very unusual in a text column. Then just change the delimiter option in the importing application to match. This assumes you have that flexibility.

You may be able to overcome the Excel version limitation (which is version 3 of Excel with version 5 of Impromptu) by using a macro call to open Excel, load the version 3 file and then resave it as a later version. This is possible, but you would have to work out the details, as I have no relevant example.

Regards,

Dave Griffin

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

http://www.decisionsupportgroup.com

 

[fishax]

Hi....thanks for the advice. I will certainly try and look into the option of changing the default delimitor.

One question though, do you know if there is a way to change the default delimitor through the Macro itself i.e

'export as an ASCII file
objImpRep.ExportASCII ("c:\" & strASCIIFileName & ".csv&quot - MAYBE A SWITCH OR SOMETHING HERE TO TELL IT WHAT DELIMITOR?

 

[griffindm]

fishax,


Check the Macro help file on the ExportASCII method. There are options to automatically embed the text strings in quotes and to change the delimiter within the macro.


HTH,

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

http://www.decisionsupportgroup.com