delete (Kill) statement

[dalex17]

Hello,

I am using Cognos script Editor to save an excel report with format. I am accomplishing this by using SendKeys. However, I find that sometimes the use of Sendkeys does not copy over any existing occurences of the excel file I'd like to copy over. So I read somewhere in this forum that a "Dir()" statement and a 'Kill' statement should be used to first detect and then delete any occurences of a selected excel file; at which the the cognos code (below) should then recreate it.

This is where I need asisstance. The below code does not seem to be functioning for me and I'm wondering if it's the order of the code or the code itself:

Any helpful tips to put an old project to rest would be much appreciated!

The objective of the code is to:

1) First kill any occurrences of pay.xls
2) Open Impromptu and save Pay.imr to Pay.xls (with format)
3) Open Pay.xls & open Load.xls (since pay.xls overwrites each time this is run, I use loadfile.xls to read from pay,xls and perform misc. vb functions).
4) Close everything

Dim CPApp as Object
Dim CPHot1 as Object
Dim CPRep as Object
DIM XLApp as Object
DIM objExcel as object

CPHot1 Kill ("P:\Standard\pay.xls&quot

Set CPApp = CreateObject ("CognosImpromptu.Application&quot
CPApp.Visible 1

CPApp.OpenCatalog "P:\Standard\Payable.cat"
CPApp.Visible True
Set CpHot1=CPApp.OpenReport("P:\Standard\Pay.imr&quot

SendKeys "%F",1
SendKeys "A",100
SendKeys "{TAB}",100
SendKeys "{DOWN 8}",100
SendKeys "{ENTER}",100
SendKeys "+{TAB}",100
SendKeys "P:\Standard\Pay.xls",100
SendKeys "{ENTER}",100
SendKeys "{TAB}",100
SendKeys "{ENTER}",100

CPHot1.CloseReport
CPAPP.closecatalog
CPApp.Quit
Set objExcel = CreateObject("Excel.Application&quot
objExcel.Visible = 1
objExcel.Application.Workbooks.Open ("P:\Standard\pay.xls&quot

objExcel.Application.Workbooks.Open ("P:\Standard\loadfile.xls&quot

Set CPRep = Nothing
Set CPApp = Nothing
Set CPHot1 = Nothing
Set XLApp = Nothing

End Sub

 

[griffindm]

Hi datalex,

Looks like you are a Deltek Costpoint user. The "CP" prefix to the objects is indicative of that. The normal sample files for macro use "Imp" instead.

First glance at the code: You need to modify the line:

CPHot1 Kill ("P:\Standard\pay.xls&quot

to be

Kill "P:\Standard\pay.xls"

You should not have the CPHot1 object reference before the Kill command. The parantheses are also not needed.

Hope this helps,

Dave Griffin



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

http://www.decisionsupportgroup.com