Variable in Shell Call under VBA

Posted on 2011-10-05
Last Modified: 2012-05-12
Hi, I have a shell command beneath a button on an access form which works well. My problem is that two directory locations specified within the current shell command can vary from user to user. Here is the VBA:

    'Shell """c:\program files\monarch\program\monarch"" ""C:\Temp\AvgBal.txt"" ""C:\Temp\AverageBalances\ModelFiles\AvgBal.xmod"" ""C:\Temp\AverageBalances\AvgBal.mdb"" /t", vbHide

The location C:\Temp can change, as can the C:\Temp\AverageBalances location. What I need is to create each location as a variable and then place the variable within the shell command. Can this be done, and how might it work?
Question by:jonlake
    LVL 9

    Expert Comment

    You could use %TEMP% variable instead of the path.

    Author Comment

    I think I've nailed it with a little more thought! Here is what I ended up with:

        Dim Part01 As String, Part02 As String, Part03 As String, Part04 As String
        Part01 = "c:\program files\monarch\program\monarch"
        Part02 = "C:\Temp\AvgBal.txt"
        Part03 = "C:\Temp\AverageBalances\ModelFiles\AvgBal.xmod"
        Part04 = "C:\Temp\AverageBalances\AvgBal.mdb"
        Shell Part01 & " " & Part02 & " " & Part03 & " " & Part04 & " /t", vbHide

    Now I can specify the variables within a table.
    LVL 43

    Accepted Solution

    If you want to do it as a variable (would be neater to read anyway) then do something like:

    dim prog as string
    dim thepath as string

    prog="C:\program files\monarch\program\monarch"

    shell "" & prog & "" & " """ & thepath & "AvgBal.txt"" """ & ThePath & "AverageBalances\ModelFiles\AvgBal.xmod"" " & ThePath & """AverageBalances\AvgBal.mdb"" /t", vbHide

    (I think...).

    In summary "" gives you a " as you know.  Plus you terminate and end each string with a single " .  & to bring in a variable value, and & to continue with another string afterwards.

    Is that the sort of thing you meant?

    LVL 43

    Expert Comment

    by:Steve Knight
    Crossed posts there.... yes that sort of thing.  Don't forget any " you need then around paths if they have (or may have) spaces in them.

    Author Closing Comment

    Very helpful, thanks
    LVL 43

    Expert Comment

    by:Steve Knight
    no problem

    Featured Post

    IT, Stop Being Called Into Every Meeting

    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

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    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.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    754 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

    16 Experts available now in Live!

    Get 1:1 Help Now