Can Cognos Script Editor run a .bat file 1

[dalex17]

Hello,

Does anyone know the question as to whether the Cognos script editor can run a batch file?

If not, then can you tell me if there is Cognos Script Editor syntax that can open an excel file?

Dalex

 

[flex13]

Not sure about the batch file but to launch an XLS from script editor try this little snippet of code ...

DIM XLApp as Object

shell ("C:\Program files\Microsoft Office\Office\excel.exe /e e:\LongShort.xls&quot

This will call the xls file from your macro.

flex

 

[dalex17]

Flex,

Thanks for the snippet. It almost works for me. I mean, excel opens, but then I get an error message saying that the file cannot be found.

this is what I have and I can verify that the file names and location are accurate. Any thoughts?

DIM XLApp as Object

shell ("C:\Program files\Microsoft Office\Office10\excel.exe /Y Y:Temp\New folder\testa1.xls&quot

Thanks again for your help!

Dalex

 

[dalex17]

Flex,

I figured it out. The reason why it didn't work properly was because the macro didn't like the file name 'New Folder'. In particular, it did not like the space between 'New' and 'Folder' for some reason. I know this because when I changed the name of that folder to just 'folder' 9one word with no spaces) it worked.

Thank you so much your assistance!


dalex

 

[drlex]

dalex,
you can also call Excel as OLE, and enjoy added functionality.

This snippet opens strfilename, removes the top 7 rows, autofits the width of column A and adds a title in bold on the top row, prior to saving in Excel 2000 format.

Dim objExcel as Object
....
Set objExcel = CreateObject("Excel.Application&quot
objExcel.Visible = 1
objExcel.Application.Workbooks.Open strfilename
objExcel.Application.DisplayAlerts = False
objExcel.Rows("1:7&quot.Select
objExcel.Selection.Delete shift:=xlup
objExcel.Columns("A:AA&quot.Select
objExcel.Columns("A:AA&quot.EntireColumn.AutoFit
objExcel.Range("A1&quot.Select
objExcel.ActiveCell.FormulaR1C1 = "Target Customer results to period just ended"
objExcel.Range("A1&quot.Select
objExcel.Selection.Font.Bold = True
objExcel.Application.Workbooks(1).SaveAs strfilename, Fileformat:=-4143
objExcel.Application.Workbooks(1).Close
objExcel.Application.Quit

HTH
lex

 

[Draoued]

I suppose that if you add some " it should be able to open the file .
shell ("C:\Program files\Microsoft Office\Office10\excel.exe" /Y "Y:Temp\New folder\testa1.xls&quot

 

[dalex17]

You guys are terrific! Thanks for your assistance.

P.S. Is there anything "cool" that can be done with Cognos script editor? I mean like a progress bar or something? Really anything that might add a cherry on top.

It's no big deal because I have everything working; just thought I'd ask.

Thanks again!

dalex

 

[navshiv]

I know that you don't need your original question answering now that you have a solution for the Excel problem but to open up a batch file you use the following command

Dim x

x=Shell("path to .bat file here&quot

Think that should work!

 

[dalex17]

Navshiv,

Thank you. This is very helpful!

Dalex