shell command to open an .xls file does not work

[dalex17]

Hello,

At the very end of my cognos script, I have a command which should open an excel file:

Shell ("C:\Program files\Microsoft Office\Office10\excel.exe /Y Y:\Standard Reports\Acc dept\test.xls&quot

The problem I am having is that it cannot read the file pathname 'Standard Reports' because it has a space in the name. I know this because if I rename the folders by removing the spaces between the folder names (i.e. StandardReports\Accdept), the test.xls file opens correctly.

Please let me know if I should further clarify.

Dalex

 

[drlex]

Dalex,
Could you not put the path to the file in sub-quotes?
Alternatively, why not call excel as an Object and then you can do some further manipulation as required?
works wonders for some of my PP reports...
lex

 

[spicysudhi]

there is a better way to open files, user ShellExecute
this is VB script, should work in Cognos also.


Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Then in your form:

Dim X As Long
X = ShellExecute(Me.hwnd, "Open", FileName, 0&, 0&, 3)



regards,
Sudhí

 

[dalex17]

drlex,

I appreciate your response.

I would like to try to call excel as an object. Could you show me pointeres on how to get this started?


Thank you

dalex

 

[drlex]

dalex,
No problem. I think I posted some code in a previous thread, but being Friday, I'm happy to repeat myself.

lex

This code-snippet removes lines 2-7 from a powerplay report that has been saved in excel format, adjusts the width of the first column to 20 (use EntireColumn.AutoFit in place of ColumnWidth = 20 if preferred) and saves it in Excel2000 format over the original report.

Record macros in Excel to see the format of commands in VBA, but note that some will need work to get functioning in CognosScript; the FileFormat is an example of this.

The DisplayAlerts = False turns off any warning windows, such as the OK to overwrite file.

Dim objExcel as Object

...<rest of macro>...

Set objExcel = CreateObject(&quot;Excel.Application&quot
objExcel.Visible = 1
objExcel.Application.Workbooks.Open strfileprompt
objExcel.Application.DisplayAlerts = False
objExcel.Rows(&quot;2:7&quot.Select
objExcel.Selection.Delete shift:=xlup
objExcel.Columns(&quot;A:A&quot.ColumnWidth = 20
objExcel.Application.Workbooks(1).SaveAs strfileprompt, Fileformat:=-4143
objExcel.Application.Workbooks(1).Close
objExcel.Application.Quit