Solved

Passing variable within a Shell statement

Posted on 2011-03-08
13
552 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
  • 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now