Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing a string to the shell function (2002)

    I'm trying to build a string inside an on click event that will contain the name of an executable program and it's required parameters. I then want to be able to run the program using the shell function. The parameters need to be built dynamically based on certain criteria, which is why I have to build the string programmatically. My problem, I think, is that I'm not building the string correctly or maybe I can't pass a string to the shell function . No matter how I build the string I get a run time error, 13 or 53, when the shell function is executed.. I know I need to have the executable name and path in quotes, which I can't seem to accomplish with out generating another error. I can run the executable from a batch file with a static parameters, so I know the program and parameters should work. I also tried to create a batch file at run time and then execute the batch file, but I run into the same issue not being able to build the string correctly. I'm looking for advice on how to handle this problem. Any help would be greatly appreciated.


    Set db = CurrentDb()
    Set rsPrprts = db.OpenRecordset("SELECT COUNT(*) As TlRcrds FROM tblPrprtyExprt")

    strField = Str(rsPrprts.Fields("TlRcrds"))

    db.Close

    StrIimCmd1 = "C:PROGRA~1InternetMacros3iimpro.exe"
    StrIimCmd2 = " -macro Images -loop " & strField & " -noexit"
    strIimCmd = StrIimCmd1 + StrIimCmd2

    'dblRtnVl = Shell(strIimCmd, vbMinimizedNoFocus)
    Shell strIimCmd, vbMinimizedNoFocus

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Passing a string to the shell function (2002)

    I prefer using the Windows ShellExecute API function, it is similar to simpler VBA Shell function, but provides more options, including a separate argument for the parameters to be used. As for Shell function, I have no idea what "iimpro.exe" is, or what command-line arguments it uses, but the main issue is, if there are spaces in either the path to the executable, or the command-line arguments, they need to be delimited by double-quotes. When constructing the string, use Chr$() function to add the quotes (ASCII code 34). Example:

    Public Sub TestShellFunction()

    Dim dblRtn As Double
    Dim strPath As String

    strPath = Chr$(34) & "C:Program FilesMicrosoft OfficeOfficeMSACCESS.EXE" & Chr$(34) & " " & _
    Chr$(34) & "C:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.MDB" & Chr$(34)
    dblRtn = Shell(strPath, vbNormalFocus)

    End Sub

    This will open Access, and open Northwind.mdb (A2K file locations used). Note use of Chr$(34) to delimit paths that have spaces. If you don't use the delimiters, when you run Shell it will try to open something called "C:Program". Since in your sample code you are using the short path (8.3) format for the executable, that should be OK (may want to use delimiters to be safe), but you will need to add delimiters for the arguments because there are spaces between each item. Also ensure there is a space between the path and the arguments as shown in example above.

    HTH

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a string to the shell function (2002)

    Thanks for the help, your response was exactly what I needed. I'll look into the ShellExecute function as well.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing a string to the shell function (2002)

    Iimpro is macro software that I want to execute when the user clicks on a button, it will gather images from the internet that will later be used for reporting. I still can't get macro software to run correctly, I think I'm still doing something wrong around the variable when I build the string. The strField variable contains the number of loops in the code sample below. The macro string appears to be correct, displayed in a msgbox before execution, and it executes the correct macro (Images), but it only loops once and then exits. The last two parameters are the loop and noexit parameter, which should leave the macro software running. I can paste the string into a batch file and it runs successfully. But when the same string is executed from Access the macro runs the first iteration and then exits, so I'm pretty sure that the string is incorrect.


    String to execute macro:
    "C:Program FilesInternetMacros3iimpro.exe" -macro Images -loop 2
    -noexit


    VBA Code:
    Set db = CurrentDb()
    Set rsPrprts = db.OpenRecordset("SELECT COUNT(*) As TlRcrds FROM tblPrprtyExprt")

    strField = Trim(Str(rsPrprts.Fields("TlRcrds")))

    db.Close
    strIimCmd = Chr(34) & "C:PROGRAM FILESInternetMacros3iimpro.exe" & Chr(34) & " " & _
    Chr(34) & "-macro" & Chr(34) & " " & Chr(34) & "Images" & Chr(34) & " " & _
    Chr(34) & "-loop" & Chr(34) & " " & Chr(34) & strField & Chr(34) & " " & _
    Chr(34) & "-noexit" & Chr(34)

    MsgBox "IimCmd " & strIimCmd

    Shell strIimCmd, vbMaximizedFocus

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing a string to the shell function (2002)

    I'm jumping into this thread without knowing what went before, I'm just commenting on the string you construct

    If you look at the command line you want to construct, you will see that only the first part is surrounded in quotes. But your MsgBox instruction will show you that you put in far more Chr(34)'s than needed: the result is

    IimCmd "C:PROGRAM FILESInternetMacros3iimpro.exe" "-macro" "Images" "-loop" "<contents of field>" "-noexit"

    Try

    strIimCmd = Chr(34) & "C:PROGRAM FILESInternetMacros3iimpro.exe" & Chr(34) & _
    " -macro Images -loop " & strField & " -noexit"

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Passing a string to the shell function (2002)

    Sorry, previous reply may not have been clear on where the delimiters are needed - the individual items within the argument string do not necessarily need to be delimited. You may have better luck with the Windows ShellExecute API function. Here is example based on info provided:

    Sub TestRunIimpro(ByRef strLoops As String)

    Dim strPath As String ' Path to app
    Dim strFile As String ' File name
    Dim strArgs As String ' Command-line args

    ' String to execute macro - example:
    ' "C:Program FilesInternetMacros3iimpro.exe" -macro Images -loop 2 -noexit
    ' Recordset determines number of loops passed to sub

    strPath = "C:Program FilesInternetMacros3"
    strFile = "iimpro.exe"
    strArgs = "-macro Images -loop " & strLoops & " -noexit"

    ShellExecute Application.hWndAccessApp, "Open", strPath & "" & strFile, _
    strArgs, strPath, SW_MINIMIZE

    'SW_MINIMIZE = VBA Shell vbMinimizedNoFocus (value = 6)

    End Sub

    Note that this sub is called from the sub that opens the recordset & determines how many loops are needed (strLoops argument). This may or may not work, I don't have the iimpro program so have no way of testing this. If interested, see attached text file, exported VBA code module that includes the example above, plus necessary API declarations needed to use ShellExecute. Also look at 2nd example:

    Sub TestOpenNorthwindExcl()

    Dim strPath As String ' Path to app
    Dim strFile As String ' File name
    Dim strArgs As String ' Command-line args

    strPath = "C:Program FilesMicrosoft OfficeOffice"
    strFile = "MSACCESS.EXE"
    strArgs = Chr$(34) & "C:Program FilesMicrosoft OfficeOfficeSamplesNorthwind.MDB" & _
    Chr$(34) & " " & "/excl /cmd ABC"

    ShellExecute Application.hWndAccessApp, "Open", strPath & "" & strFile, _
    strArgs, strPath, SW_SHOWNORMAL
    End Sub

    Note the strArgs variable that specifies command-line arguments uses delimiters for path to Northwind.mdb, because this is a single item within the arguments string, which contains spaces. But rest of the argument string ("/excl /cmd ABC") does not require delimiters, as the individual items contain no spaces. When ran this sub, code opened Northwind.mdb in Exclusive mode (/excl) and set the Command (/cmd) function string to "ABC" (as confirmed in Options). If the Command string had spaces in it, then the double-quote (Chr$(34)) delimiters would have been necessary.

    HTH
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •