SHELL out to excel

[Buy2easy.com]

ok im am using access (vba) and i am opening excel.exe something like this:

shell("address to find excel.exe then the file name to open",1)

or something like that...but here is the problem when i pass it a file in a directory that contains a space in the name excell tries to open the wrong thing.

example: if i opened excel in vb like:

shell("C:\excellfolder\excel.exe C:\example folder\example.xls",1)

then itsays this opon entering excel

canot find "C:\example.xls"

then cannot find "folder\example.xls"

it splits it at the space but i do not want it to what can i do to keep it from splitting it at the space?

p.s. i know the code example probably sucks but it is something like that.

thanks ahead of time for the help!

[Armbruster]

Shell can't handle spaces.

You could convert everything to 8.3 naming, but shell still sucks!

Use shellexecute instead.

Option Explicit
 
Private 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
 
Private Const SW_SHOWNORMAL = 1
Private Const SW_SHOWDEFAULT = 10
Private Const SW_SHOWMAXIMIZED = 3
Private Const SW_SHOWMINIMIZED = 2
Private Const SW_SHOWMINNOACTIVE = 7
Private Const SW_SHOWNA = 8
Private Const SW_SHOWNOACTIVATE = 4
 
Private Sub Command1_Click()
    Dim lngHandle As Long
    Dim strFile As String
    Dim strDir As String
    
    strFile = "C:\Documents and Settings\Carl\Desktop\Example.xls"
    strDir = "C:\Documents and Settings\Carl\Desktop"
    
    lngHandle = ShellExecute(Me.hwnd, "Open", strFile, vbNullString, strDir, SW_SHOWNORMAL)
    
    If lngHandle <= 32 Then
        MsgBox "Opening excel failed"
    Else
        MsgBox "Successful" & vbCr & vbCr & _
               "The handle to the new excel window is " & lngHandle
    End If
 
End Sub

[Buy2easy.com]

thanks for your help! Will this still work if i am in VBA? Just out of cureosity why do you check to see if it is <=32 ?

thanks agian!

[WorkHorse]

The Shell function parameter "pathname" takes an argument list separated by spaces (who knows why, but it does). The first argument is the application, the following arguments are the files to be opened by the application (separated by spaces, remember). So if the path name includes spaces, you must enclose the path name with double quotes. You can add double quotes inside a quotation by using two consecutive double quotes (""). So the code would be:

Shell """C:\excellfolder\excel.exe"" ""C:\example folder\example.xls""", 1

Note that it is the same code with "" around each path name. (It isn't necessary for the application file path because it doesn't have spaces, but might as wel be consistenet.)

Of course, that doesn't mean that it wouldn't be better to use ShellExecute, it is just what you need to do if you want to use the Shell function.

[Buy2easy.com]

what is the advantage of shellexecute? what can you do with the handle that it returns?

and example would be awesome! Thanks for the great responses!

[WorkHorse]

Originally posted by Buy2easy.com
thanks for your help! Will this still work if i am in VBA? Just out of cureosity why do you check to see if it is <=32 ?

thanks agian!

To answer you question on ShellExecute: the function returns a long (as indicated in the declare) that is consistent with the return values of the FindExecutable API. Any value over 32 means success. Lesser values indicate an error (ERROR_FILE_NOT_FOUND, ERROR_PATH_NOT_FOUND, ERROR_BAD_FORMAT).

[WorkHorse]

Originally posted by Buy2easy.com
what is the advantage of shellexecute? what can you do with the handle that it returns?

and example would be awesome! Thanks for the great responses!

As you can see from the code, there are more parameters that can be passed with ShellExecute. The function returns a long indicating success/errors. With ShellExecute you can more closely identify the error that occured.

Most programmers seem to prefer ShellExecute, but they don't seem to really use the additional parameters available in ShellExecute (as you can see in the above example). I think it is just more popular because using the double quotes in Shell is a hassle. You just have to get used to using the double quotes in Shell and it should work fine.

[Armbruster]

Originally posted by Buy2easy.com
what is the advantage of shellexecute? what can you do with the handle that it returns?

and example would be awesome! Thanks for the great responses!

Once you have the handle, you can use other api calls to minimize, maximize, close the window, send keys to the window . . .

I have had shell fail too many times. ShellExecute seems to work consistently, especially with unc paths.