• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

Variable in Shell Call under VBA

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?
0
jonlake
Asked:
jonlake
  • 3
  • 2
1 Solution
 
pritamduttCommented:
You could use %TEMP% variable instead of the path.
0
 
jonlakeAuthor Commented:
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.
0
 
Steve KnightIT ConsultancyCommented:
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"
thepath="c:\temp"

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?

Steve
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Steve KnightIT ConsultancyCommented:
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.
0
 
jonlakeAuthor Commented:
Very helpful, thanks
0
 
Steve KnightIT ConsultancyCommented:
no problem
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now