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 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")

Set CPApp = CreateObject ("CognosImpromptu.Application")
CPApp.Visible 1

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

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")
objExcel.Visible = 1
objExcel.Application.Workbooks.Open ("P:\Standard\pay.xls")

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

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

End Sub