HELP Macro Fails using ExportExcel

[ccoughlan]

Hello. We are currently migrating our cognos impromptu reports to a new server. We have a bunch of macros that run the reports and export them to Excel. However when installing cognos on the new server, the ExportExcel method no longer works in the macros. I've heard that this has been removed from Cognos 7.3, but we are still on 7.2 so I don't know why this method does not work. When I step through the macro, I get "R438 No such property or method". Any ideas?

 

[drlex]

ccoughlan,
Can you confirm that Microsoft Excel has been installed on the new server? I understand that the Impromptu rendering engine requires some MS component to work.
lex

soi la, soi carré

 

[ccoughlan]

Yes. That was my first swing at this. I installed Excel on the new server, as well as access, word and the rest of the office suite.

 

[drlex]

Hmmm. Other than the suggestion that there may be an incompatibility between the latest version of Office (Excel 2003) and the old version of Impromptu (7.1 - I don't recall Cognos issuing 7.2) - I'm a little stumped.

Trying to use 'ExportExcel' on 7.3 gives a '440 Export to older Excel format is no longer available. (Deprecated functionality.)' error.

Is it worth you posting your machine specs and the script concerned (or relevant section(s) of)?

soi la, soi carré

 

[ccoughlan]

I'm stumped too. The other thing I noticed in testing this is that it actually does do the export to excel and the file is in the directory where I would expect it to be. The problem is that there is still the error in the code and it still locks up the macro when compiled. I read a post somewhere on the 7.3 error message but was a little stumped when I was getting R438 and not 440. Here's the script in question. I apologize in advance for the formatting:

'declare variables
Dim objImpApp as Object
Dim imprep As Object
Dim objImpRep As Object
Dim strASCIIFileName As String
Dim CatalogFileName As String
Dim ReportName As String
Dim ExcelReport As String

'Set the report and catalog name
Catalogfilename = "c:\Cognos\DSS_Report_Source\Labor_Efficiency\Labor_Efficiency.cat"
ReportName = "c:\Cognos\DSS_Report_Source\Labor_Efficiency\Labor_Efficiency.imr"
ExcelReport = "c:\Cognos\DSS_Report_Output\Labor_Efficiency\Labor_Efficiency.xls"

'set the Application Object to Impromptu
Set objImpApp = CreateObject("CognosImpromptu.Application.cer3")

'This line must be added for the impromptu instance to close
objImpApp.Visible 1

'1)Creator=catalog user class, 2)catalog pw, 3)user id for database, 4) database pw
objImpApp.OpenCatalog CatalogFilename,"Creator","Prod","cognos","powerplay4",1
set imprep = objImpApp.OpenReport(ReportName)

REM This creates the Excel Spreadsheet
imprep.ExportExcel ExcelReport


'retrieve the active report
Set objImpRep = objImpApp.ActiveDocument

'Force the object to quit and then destroy the application object
objImpApp.Quit
Set objImpApp = Nothing
Set objImpRep = Nothing

End Sub

 

[drlex]

Which of the lines you've posted is throwing the error?

(It may be the way that you've set out the script, but I would have standardized the report object rather than having imprep and objimprep.

set imprep = objImpApp.OpenReport(ReportName)

REM This creates the Excel Spreadsheet
imprep.ExportExcel ExcelReport


'retrieve the active report 
Set objImpRep = objImpApp.ActiveDocument

as

set Objimprep = objImpApp.OpenReport(ReportName)

'retrieve the active report 
Set objImpRep = objImpApp.ActiveDocument

REM This creates the Excel Spreadsheet
Objimprep.ExportExcel ExcelReport

Having said that, if the script worked before, then it doesn't explain the problem.)

soi la, soi carré

 

[ccoughlan]

The line that errors is:

imprep.ExportExcel ExcelReport

I also tried your code and still get the R438 error.

 

[drlex]

Another tack: can Cognos access Excel OK? Would the following short macro work? (It merely opens a report and changes the font to arial narrow before saving).

Is the configuration of the new server (OS, Office, Cognos) the same as your old box?

Sub Main()
   Dim objExcel As Object
   Dim strfilename as Object
   strfilename = "D:\xl\my_report_here.xls"
   Set objExcel = CreateObject("Excel.Application")
      objExcel.Visible = 1
      objExcel.Application.Workbooks.Open strfilename
        objExcel.Application.DisplayAlerts = False
        objExcel.Cells.Select
        objExcel.Selection.Font.Name = "Arial Narrow"
        objExcel.Application.Workbooks(1).SaveAs strfilename, Fileformat:=-4143
      objExcel.Application.Workbooks(1).Close
    objExcel.Application.Quit
End Sub

soi la, soi carré

 

[ccoughlan]

That macro works. The two boxes are running the same version of Office and Both are running Excel 2002 (though the old box has SP3 versus SP1 for the new box). They are both running Impromptu 7.1.339.0. They are on different operating systems with the old being on XP and the new being on 2003 server.

 

[drlex]

I found a server here with 2003 on, so just checked with a version of 7.1 (7.1.116, a bit older). Error occured just as you describe.

Ignoring the route of asking Cognos to 'fix' 7.1 to play nicely with S2003, it seems the choices might be:
1) Install XP/Server 2000 on server.
Notwithstanding the licencing hurdles MS put up, I can see that being a non-starter, but, hey, it's an option!
2) Use On Error Resume prior to exportexcel line
3) Use ExportExcelwithFormat function instead (which doesn't throw an error).

In your shoes, 3 looks to be the best. After all, going to Imp7.3 will require 3) anyway, so it might be a good time to prepare your upgrade path. 7.3 allows specification of Excel version in exporting, so it might prove to be a useful upgrade.

Happy Friday,
lex


soi la, soi carré

 

[ccoughlan]

Are you sure the path that you were trying to export the excel file to exists? At least that is what Cognos came up with after looking at this for 3 days. Sheesh. I'm glad the money we pay for support is well spent.

We are currently using ExportExcelWithFormat as a workaround for now, but the problem is that it does not allow for auto-filtering and it has that 16,000 and change limitation for sheets where if there are more records than that returned, it splits them up on multiple sheets. Yes the users could paste all the data into a new sheet, but they're not always the most patient people.

After getting your code for opening excel and closing it, I created a macro that will open and retrieve the report, copy the data to the clipboard, open excel, paste the data into a new sheet, save the spreadsheet, and close both Excel and Impromptu. It has worked on the two or three reports that I've tried it with. My only concern is that the clipboard will fill up using it on 50 reports or so and it will open Excel and get prompted when pasting. I'm trying to figure out a way to clear out the clipboard in VB to handle this, but I'm not even sure it is going to be a problem.

Thanks for all your help on this. I'll post the macro so others don't have to go through this if they run into the smae situation.

 

[drlex]

ccoughlan,
I'm sure that the path existed, as I watched the excel file being created just moments before the error was generated. Therefore, it looks like there is a slight incompatibility between Imp 7.1, Server 2003 and Excel 2002. Your experience suggests either Server 2003 or the earlier SP on Excel to be the culprit. Perhaps worth trying SP3 on Excel?

In Imp 7.3 you can get 65,536 rows (the Excel limitation) per worksheet rather than 16K, so could save you some effort in coding a solution.

(I guess if you ever need more than 65K, export as csv.)

As for combining sheets, I do likewise to provide some reports that have a page or two of Impromptu reports and a page or two of Powerplay reports. Hideous, but then the non-Cognos users are happier 'when a plan comes together'.

soi la, soi carré

 

[ccoughlan]

My first question was kind of tongue and cheek. My sarcasm didn't quite come across in my post.

I think for now I'm going with the clipboard solution. It was pretty easy to clear out the clipboard and it will basically cause the least amount of impact to the users. I'll save off all the current .Mac files so if we ever do go to 7.3, I've got them and can swap them in. We'd do it now, but then we have to update every catalog and report , so that is twice as many objects to get into, versus just updating the .MAC files.

 

[drlex]

Sorry to miss the sarcasm - tone is such a major component of communication. I always try and use an emoticon: 'you can always rely on Cognos support '.
Good luck with the changes. 7.1 > 7.3 wasn't too much of an issue when you control all the catalogues and write all the reports .


soi la, soi carré

 

[ccoughlan]

It turns out that after a week on this, cognos acknowledged that there is an issue with Impromptu 7.1.339 and Windows Server 2003. They had me upgrade to 7.1.814 and the ExportExcel Method now works.

 

[drlex]

Hurray!

Good to know that the support licence eventually pays off...[happy]

soi la, soi carré