Solved

Passing variable within a Shell statement

Posted on 2011-03-08
13
556 Views
Last Modified: 2013-11-27
I need to set a variable and include that variable within the Shell(FP, vbNormalFocus)

I tried setting the variable as string and variant, the FP returns the correct value but the Shell does not recognize it.  What am I missing?

K
Private Function ViDataX_Process_Request()
  On Error GoTo ViDataX_Process_Request_Err
  
  Dim lRetVal As Long  'Return value
  Dim lProcId As Long  'Process id
  Dim lProcHnd As Long  'Process handle
  Dim FP As Variant      'String file path of
  '-- Check that the ViDataX executable exists.
    If ViDataX_File_Exists_W7 = True Then
        FP = "mc_VIDATAX_VB_APP_W7"
    ElseIf ViDataX_File_Exists_XP = True Then
        FP = "mc_VIDATAX_VB_APP_XP"
    Else
        MsgBox "Required ViDataX executable has not been installed on this machine." _
           & vbCrLf & "Contact your application support.", vbOKOnly, "ViDataX"
        Exit Function
    End If
    
  '-- Open the "User Wait" form.
  DoCmd.OpenForm "FZ_ViDataX_UserWait"
  'ClassificationRecID True
  
  '-- Shell for ViDataX to process the request.
  lProcId = Shell(FP, vbNormalFocus)
  
  If lProcId <> 0 Then
    '-- Get the handle to the shelled ViDataX process.
    lProcHnd = OpenProcess(Synchronize, 0, lProcId)
    If lProcHnd <> 0 Then
      '-- Cycle until the shelled ViDataX process ends.
      Do While True
        lRetVal = WaitForSingleObject(lProcHnd, 30)
        If lRetVal = WAIT_TIMEOUT Then
          DoEvents  'Allows form displays from ViDataX
        Else
          Exit Do
        End If
      Loop
      '-- Close the process handle.
      CloseHandle (lProcHnd)
    End If
  End If
    
  '-- Close the "User Wait" form.
  DoCmd.Close acForm, "FZ_ViDataX_UserWait"
  'ClassificationRecID False
  
  Exit Function

ViDataX_Process_Request_Err:
  MsgBox Error$
End Function

Open in new window

0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +3
13 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35075287
Since you are not including a directory (or drive spec), the executable must be found within the PATH environmental variable with the way you are calling it. It appears this is not the case. Try passing the full executeable path to the Shell function. If there are spaces anywhere in the path, you'll want to surround the path with quotation marks:

FP = Chr(39) & strPathWithSpaces & Chr(39)
lProcId = Shell(FP, vbNormalFocus)


You can use
strPath = Environ("PATH")
to check the path value programatically, but your going to need to know the location of the executeable if it is not dependably within the PATH.

Hope this helps,
pT72
0
 

Author Comment

by:Karen Schaefer
ID: 35075373
mc_VIDATAX_VB_APP_W7 is the file path.


Hence:

 '-- Check that the ViDataX executable exists.
    If ViDataX_File_Exists_W7 = True Then
        FP = "mc_VIDATAX_VB_APP_W7"
    ElseIf ViDataX_File_Exists_XP = True Then
        FP = "mc_VIDATAX_VB_APP_XP"
    Else...

Private Const mc_VIDATAX_VB_APP_XP As String = _
    "C:\Program Files\abc\FTCS Apps\ViDataX\ViDataXSE.exe"  'Software Express _
    Frontend
Private Const mc_VIDATAX_VB_APP_W7 As String = _
   "C:\Program Files (x86)\abc\FTCS Apps\ViDataX\ViDataX.exe"  'ViDataX Application

it balks at the FP being set as the  "C:\Program Files (x86)\abc\FTCS Apps\ViDataX\ViDataX.exe"  
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35076127
Try putting the path in quotes like this:

Private Const mc_VIDATAX_VB_APP_W7 As String = _
   """C:\Program Files (x86)\abc\FTCS Apps\ViDataX\ViDataX.exe"""  'ViDataX Application
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35076191
What happens if you don't use the parenthesis?
0
 

Author Comment

by:Karen Schaefer
ID: 35084761
What happens if you don't use the parenthesis?

On what???? the Shell????

K
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35085088
Sorry,
;-)

No I meant in the path...


My main question is why/how the "(x86)" is enclosed in parenthesis.?

Although not illegal characters per se, the parenthesis are not commonly use in file/folder names.

So if the actual folder hierarchy name was changed to something a bit more standard:
    C:\Program Files\x86\abc\FTCS Apps\ViDataX\ViDataX.exe

...would the code work...?
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35085109
Did you try this yet? I didn't see a response on any error msgs, etc

Try putting the path in quotes like this:

Private Const mc_VIDATAX_VB_APP_W7 As String = _
   """C:\Program Files (x86)\abc\FTCS Apps\ViDataX\ViDataX.exe"""  'ViDataX Application
0
 

Author Comment

by:Karen Schaefer
ID: 35085128
No that is the name of the directory - not my choice.  I got the solution by removing the quotes around the FP = "...." to FP = macroname.

Thanks for the input.

K
0
 
LVL 2

Expert Comment

by:bobHacker
ID: 35175741
Hello,

I discovered this on a site which uses

http://vbadud.blogspot.com/search/label/Visual Basic Open a Folder in Windows Explorer How to Open Explorer Window using VBA

Hope this helps...

B

Here is the text if the blog page is no longer available...

How to Open a Folder in Windows Explorer using VBA

ShellExecute() Windows API function can be called from a VBA macro to start another program under Microsoft Windows. Use ShellExecute() instead of Shell (a Visual Basic statement) or WinExec() (a Windows API function) to work around the following limitation of the latter commands.

With Shell and WinExec(), you cannot start an application by specifying a file name only. For example, the following Shell statement will fail:

Shell (“c:\temp”)

Declare the API function
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

The following code will open the specified folder in Windows Explorer

Sub Open_ExplorerWindow()
ShellExecute 0, "open", "c:\temp", 0, 0, 1
End Sub  

0
 

Author Comment

by:Karen Schaefer
ID: 35245152
Sorry for the delay  in response - I was away on vacation.

Thanks for the suggestion, however, I need to execute a macro from the shell - since the macro name can vary depending on the version of Windows running. - the version will determine the location of the executable to be run.  Hence the If statement to determine the correct macro to call.

 Modified per your suggestion - not what I  need I need to Call the correct Macro so that the correct version of the executable is run.  

K
Option Compare Database
Option Explicit

'-- Location of the ViDataX executable.
Private Const mc_VIDATAX_VB_APP_XP As String = _
    "C:\Program Files\abc\FTCS Apps\ViDataX\ViDataXS.exe"  'Software Express _
    Frontend
Private Const mc_VIDATAX_VB_APP_W7 As String = _
   "C:\Program Files (x86)\ABC\FTCS Apps\ViDataX\ViDataX.exe"  'ViDataX Application
'Public gAPFilePath As String
'Public gApNo As String
'Private Const mc_VIDATAX_VB_APP As String = _
    "C:\Program Files\Boeing\FTCS Apps\ViDataX\ViDataX.exe"  'ViDataX Application
'-- Standard server for airplane files.
Private Const mc_DFLT_AP_ROOT_DRCTRY As String = "Z:\"

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As _
    Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As _
    Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As _
    Long
Private Const INFINITE = &HFFFF
Private Const Synchronize = &H100000
Private Const WAIT_TIMEOUT = &H102

'Declare the API function
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 Function ViDataX_Process_Request()
  On Error GoTo ViDataX_Process_Request_Err
  
  Dim lRetVal As Long  'Return value
  Dim lProcId As Long  'Process id
  Dim lProcHnd As Long  'Process handle
  Dim FP As Variant      'String file path of
  '-- Check that the ViDataX executable exists.
    If ViDataX_File_Exists_W7 = True Then
        FP = "mc_VIDATAX_VB_APP_W7"
    ElseIf ViDataX_File_Exists_XP = True Then
        FP = "mc_VIDATAX_VB_APP_XP"
    Else
        MsgBox "Required ViDataX executable has not been installed on this machine." _
           & vbCrLf & "Contact your application support.", vbOKOnly, "ViDataX"
        Exit Function
    End If
    
  '-- Open the "User Wait" form.
  DoCmd.OpenForm "FZ_ViDataX_UserWait"
  'ClassificationRecID True
'>>>>>>>>>>>>>>>>>>>>>>>>>>>> See revision
  '-- Shell for ViDataX to process the request.
  lProcId = ShellExecute 0, "open", "c:\temp", 0, 0, 1
  ' Shell(FP, vbNormalFocus)

'>>>>>>> Original Code

 '  lProcId =  Shell(FP, vbNormalFocus)

'>>>>>>> 
  If lProcId <> 0 Then
    '-- Get the handle to the shelled ViDataX process.
    lProcHnd = OpenProcess(Synchronize, 0, lProcId)
    If lProcHnd <> 0 Then
      '-- Cycle until the shelled ViDataX process ends.
      Do While True
        lRetVal = WaitForSingleObject(lProcHnd, 30)
        If lRetVal = WAIT_TIMEOUT Then
          DoEvents  'Allows form displays from ViDataX
        Else
          Exit Do
        End If
      Loop
      '-- Close the process handle.
      CloseHandle (lProcHnd)
    End If
  End If
    
  '-- Close the "User Wait" form.
  DoCmd.Close acForm, "FZ_ViDataX_UserWait"
  'ClassificationRecID False
  
  Exit Function

ViDataX_Process_Request_Err:
  MsgBox Error$
End Function

Open in new window

0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 35268801
Hi Karen,
<<it balks at the FP being set as the  "C:\Program Files (x86)\abc\FTCS Apps\ViDataX\ViDataX.exe" >>

Whenever you pass a variable within a string, you need to wrap the variable component in the CStr function (or equivalent) to identify and interpret it.

Assuming the variable component is   ViDataX, your path would be:
  "C:\Program Files (x86)\abc\FTCS Apps\" & CStr(ViDataX) & "\ViDataX.exe"
0
 

Author Closing Comment

by:Karen Schaefer
ID: 35318140
Thanks for your input - However, I changed the code to hard code the actual filepaths into the variable and that seemed to fix the issue
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question