Help: Static shell function call works, but dynamic call fails

[tranchemontaigne]

I'm having to recode some old MS Access DBs so they will run in the following environments:

Office 2000 on WinXP
Office 2003 on WinXP
Office 2010 on WinXP
Office 2000 on Win7
Office 2003 on Win7
Office 2010 on Win7

When I wrote my code for Office 2000 on WinXP things were simple because directory paths were the same across all computers and I could hard code pathing when using a shell command to launch other files.

My new approach is to make a function call to the Windows registry to determine the default executable and path for opening a file based upon its extension (see apicFindExecutable in basWindows API module).

I'm able to use code to create a shell call and debug print it to the immediate window. If I put my cursor in the immediate window at the end of the shell call and hit [enter] the external file will open as desired. If I try to open the external file directly through code, I get a file not found error.

To recreate the error take the following steps:
(1) browse to files that are accessible from your computer
(2) click the PREPARE DATA AND OPEN MAIL MERGE DOCUMENTS command button

Shell function call is made by the fnOpenFile function located in the basOpenFile module. There has to be a trick here that I'm missing.

Any help would be appreciated.


PS: Apologies in advance if my code documentation is incomplete. I also tried to strip out all of the unnecessary code, forms, and modules prior to posting the DB

 

[tranchemontaigne]

Arghhh.."Upload of attachment failed"



Here's code to determine the executable file and path:

Function apicFindExecutable(strDataFile As String, strDir As String) As String
  'Resturns exectuable for passed data file.
Dim lngApp As Long
Dim strApp As String
  
  
    strApp = Space(260)
    lngApp = FindExecutable(strDataFile, strDir, strApp)
    
    If lngApp > 32 Then
        apicFindExecutable = strApp
    Else
        apicFindExecutable = "No matching application."
    End If
    
    
End Function

Here's the code that should be opening the external file

Public Function fnOpenFile(strFileExtension As String, strFilePath As String) As Boolean
'////////////////////////////////////////////////////////////////////////////////////
'// Author: Tranchemontaigne
'////////////////////////////////////////////////////////////////////////////////////
'// Revision History
'//     Date            Editor          Description
'//     ==========================================================
'//     07 Jan 2009     Tranchemontaigne    -Created
'//     20 Aug 2013     Tranchemontaigne    -Imported into Lyme Letters DB
'//     03 Sep 2013     Tranchemontaigne    -Improved error handling
'//                                     -Split function into a set of function calls
'//                                      to address problem of different people using
'//                                      different versions of Windows and MS Office
'//
'////////////////////////////////////////////////////////////////////////////////////
'// Description
'//     Function opens a file based upon file type association
'//
'////////////////////////////////////////////////////////////////////////////////////
'// Inputs:
'//     Variable            Type    Description
'//     =============================================================================
'//     strFileExtension    String  file extension
'//     strFilePath         String  File name and path
'//
'////////////////////////////////////////////////////////////////////////////////////
'// Requirements:
'//     Visual Basic for Applications
'//     Microsoft ActiveX Data Objects 2.1 Library
'//     OLE Automation
'//     Microsoft Access 9.0 Object Library
'//     fnAccessVersionID           (basWindowsAPI module)
'//     fnOpen_with_MS_Office_2000  (basOpenFile module)
'//
'////////////////////////////////////////////////////////////////////////////////////
On Error GoTo Err_fnOpenFile

Dim strError As String
Dim blFile_Open As Boolean
Dim str_Path As String
Dim strLaunch_Path As String
Dim lng_Position As Long

            lng_Position = 999
            strLaunch_Path = ""
            
            lng_Position = InStrRev(strFilePath, "\")
            str_Path = Left(strFilePath, lng_Position - 1)
            strLaunch_Path = Trim(apicFindExecutable(strFilePath, str_Path))
            strLaunch_Path = Left(strLaunch_Path, Len(strLaunch_Path) - 1)
            strLaunch_Path = Chr(34) & Chr(34) & Chr(34) & _ 
                                     strLaunch_Path & _ 
                                     Chr(34) & Chr(34) & _ 
                                     " " & _ 
                                     Chr(34) & Chr(34) & _ 
                                     Trim(strFilePath) & _ 
                                     Chr(34) & Chr(34) & Chr(34)

            'this is what a the desired shell command looks like
            'Call Shell("""C:\Program Files\Microsoft Office\Office\WINWORD.EXE"" "" & strFilePath & """, vbMaximizedFocus)
            
            'Here's my dynamic shell command instruction output in the immediate window
            Debug.Print "Call Shell(" & strLaunch_Path & ", vbMaximizedFocus)"
            

             'Here's the dynamic shell call that cannot find its file
             Call Shell(strLaunch_Path, vbMaximizedFocus)

    
    fnOpenFile = True


Exit_fnOpenFile:
    Exit Function
    
    
Err_fnOpenFile:
    strError = "strFileExtension: " & strFileExtension & _
                Chr(10) & _
                Chr(13) & _
                "strFilePath: " & strFilePath & _
                Chr(10) & _
                Chr(13) & _
                "lng_Position: " & lng_Position & _
                Chr(10) & _
                Chr(13) & _
                "strLaunch_Path: " & strLaunch_Path & _
                Chr(10) & _
                Chr(13) & _
                "Error: " & Err.Number & ": " & Err.Description
    
    Debug.Print strError
    Call fnLogError(gstrObject, "fnOpenFile", strError)
    
    MsgBox strError, vbCritical, "fnOpenFile encountered an error"
    
    
    fnOpenFile = False
    
    Resume Exit_fnOpenFile
    
    
    
End Function

EDIT: Fixed syntax error in comment line showing desired syntax

 

[spikepl]

I think you've got lost in all the quotes! The secret is simple: all file paths that contain spaces MUST be enclosed in quotes. Your
'Call Shell("C:\Program Files\Microsoft Office\Office\WINWORD.EXE """ & strFilePath & """", vbMaximizedFocus)

looks quite fine. Your str_LaunchPath does not. WHat you need to print.debug is what is in the brackets of the Shell command. That has to look like your own example.

 

[tranchemontaigne]

The only way I've ever gotten this to work is to have the path to the executable is wrapped in double quotes, and the path to the file is wrapped in double quotes, and a single quote wrapped around the entire concatenated string.

Here is what the debug.print outputs to the immediate window (actual copy/paste from immediate window follow)

Call Shell("""C:\Program Files\Microsoft Office\Office\EXCEL.EXE"" ""H:\Book2.xls""", vbMaximizedFocus)

Call Shell("""C:\WINDOWS\system32\NOTEPAD.EXE"" ""H:\log.txt""", vbMaximizedFocus)

If I place my cursor at the end of either of these lines in the immediate window and hit enter then the file will open correctly.

I'd like to think that the quotes were my problem, but I think I still need help.

It looks like the sample in my code is bad from prior attempts to work through the desired number of quotes.

 

[spikepl]

You do need help indeed. But you need to do what I tell you to do, no more and no less, and not continue what you have been doing, because that does not work!

Print ALONE the content of the brackets in the Shell command. This you can paste directly into a command window and test.

If you have some string containing a path, or two, the way to do the command is this

DIm myCommand as String

myCommand= Chr(34) & path1 & chr(34) & " " & chr(34) & path2 & chr(34)

debug.print myCommand ' the output you can paste directly into a command window and test - if it doesn't work then its wrong

ret=Shell( myCommand, someParameter)

 

[tranchemontaigne]

For the record, here are the values when the error occurs with my original code

strFileExtension: xls

strFilePath: H:\Book2.xls

lng_Position: 3

strLaunch_Path: """C:\Program Files\Microsoft Office\Office\EXCEL.EXE"" ""H:\Book2.xls"""

Error: 53: File not found

Here's the code change made per your recommendation

strLaunch_Path = Chr(34) & strLaunch_Path & Chr(34) & " " & Chr(34) & Trim(strFilePath) & Chr(34)

Your suggestion works like a charm. My apologies for being a bit thick between the ears.

Lots of appreciation.