Can't read own Excel reports

[MooSki]

Hi guys

Cognos: v 7
O/S : Windows 2000
Excel: 2000

I'm missing something really obvious here and it's doing my head in!

I used to use Cognos v6 and had a few scheduled daily reports that exported into Excel 97 and they worked a treat. Then after the company decided to get me a new PC with Win 2000 on it and also upgrade me to Cognos v7, I started to have a niggly problem.

I can create the Excel files using the macro below, but I can't actually read them myself using Excel 2000, an error pops up saying "filename.xls can not be accessed. The file may be read only, or you may be trying to access a read only location."

Normally I would worry, but the niggly bit is that the admin guy I have scheduled these reports for can read these files on his NT4, Excel 97 machine.

My question is, can I specify which version of Excel to export to? Or is that the wrong question to ask?

Thanks in advance,
MooSki

MacroCode:

dim objImpApp as object
dim objImpRep as object
dim strRepDate as string
dim strRepname as string

sub Main()

strrepdate=format(date,"ddmmyy&quot
strrepname="[pathname]\u k\uk_" & strrepdate & "_adm.xls"

set objimpapp = createobject("CognosImpromptu.Application&quot
objimpapp.opencatalog "[pathname]\bi_prod\impromptu\catalogues\[catname].cat","[username]","[password]"

set objimprep = objimpapp.openreport("[pathname]\u k\rptFulfillment_UK_adm.imr&quot
objimprep.retrieveall
objimprep.exportexcel(strrepname)
objimprep.closereport
objimpapp.quit

set objimprep = nothing
set objimpapp = nothing

end sub

 

[drlex]

MooSki,
Might be something to do with permissions you have under Windows 2000; I tried to make my PowerPlay reports read only by removing write permissions under windows settings from everyone other than the group I was in but ended up blocking myself. Check the permissions you have to the folders in the pathname.
As a PS, I was disappointed that even in ver 2 (7.1) or Imp, the excel version is still only 5.0 which won't work on our company's iPaqs without conversion.
lex

 

[MooSki]

I think it must be that, Lex. Thanks for your reply.

Incidentally, I ran the above report from Impromptu and saved as "Excel with Format" instead of just "Excel" and I can get into it. How do you impliment this instead of the code below?

objimprep.exportexcel(strrepname)

Any advice would be much appreciated.

 

[drlex]

MooSki,
Depends on your version of Impromptu - before 7.1 (a.k.a 7 ver 2), you can only use a nasty kludge involving sendkeys - there's a couple of threads in this forum regarding it. From 7.1, there's a precise command to save with format, but I can't recall it (I had an eval copy, but am staying with 7 for now). Your reseller should be able to provide a copy of it, or you can download it from the cognos site depending on your support status
lex