Insert Excel cell range in Shell command

[akalehzan]

Helo Expersts,

I like to run a shell command in excel VBA as following:

I have two Excel cells "H2 and I2"

I like to insert the H2 and I2 cells in to shell command, for example:


shell "net use W: \\psych-files\finance /user: + H2 & I2")

But that didn't work:-(

Is there a way to do that?

Thanks for any help,

Abi

[Kenneth Hobs]

It is best to add quotes around paths that could include a space character for such tasks. The rule is to include another quote when embedding quotes. e.g.

Sub Main()  
  Dim s$
  [H2] = "Ken"
  [I2] = "Hobson"
  
  s = "net use W: ""\\psych-files\finance"" /user:" & [H2] & [I2]
  'net use W: "\\psych-files\finance" /user:KenHobson
  Debug.Print s
End Sub

[akalehzan]

Kenneth,
Thanks for your solution.
if I want [H2] = as Excel cell H2
how to do that?

Abi

[Kenneth Hobs]

I don't know what you mean. [H2] is the same as Range("H2").Value and Cells(2, "H").Value and such. .Value is the default property. They are pseudo-prefixed with ActiveWorkbook and ActiveSheet.

[akalehzan]

Kenneth,
It is not as same as Range ("H2") in Excel sheet1. it will input : "ken" IN

net use W: "\\psych-files\finance" /user:Ken

my intend was, what ever users type in Excel "H2" cell such as "jim" to redirected into :
net use W: "\\psych-files\finance" /user:jim


Thx-Abi

[Kenneth Hobs]

Exactly.

[H2]="Ken" was an example as was the 2nd. All you need is the string assigned to the variable "s" in my code. The shell() would simple call "s" or use the string directly. e.g.

Shell "net use W: ""\\psych-files\finance"" /user:" & [H2] & [I2] , vbHide 'or vbNormal

When building strings for Shell() or other things like formulas, I like to assign a variable so that Debug.Print puts a run's result into the Immediate Window. Select the VBE menu View to enable that window if needed. It is a good way to well, Debug your code so that you know the resultant string value. Did you note how I commented the result of my run? I normally like to work it up manually and then I know the syntax needed for the string.

[akalehzan]

Sorry I'm confused here.

I have your code:

Private Sub CommandButton2_Click()
Dim s$
[h2] = "H2"
[I2] = "I2"


s = "net use W: ""\\psych-files\finance"" /user:" & [h2] & [I2]
'net use W: "\\psych-files\finance" /user:KenHobson
Debug.Print s




End Sub

////////////////////////


it does not allowing me to set "S" as string :
s as string


Thx-Abi

[Kenneth Hobs]

So you want /user:H2I2? I highly doubt that.

You can set a string value, s, to be anything that you want.

I think that you want the range value, which I showed with bracket [] method and others. To learn more, see:
http://www.vbaexpress.com/forum/cont...Range-Notation
http://www.vbaexpress.com/forum/cont...valuate-Method
'some speed tests
http://blog.excelhero.com/2010/06/05...ing_in_vba_we/

Sub Main()    
  Dim s$
    '[H2] = "Ken"
    '[I2] = "Hobson"
     
    s = "net use W: ""\\psych-files\finance"" /user:" & [H2] & [I2]
    'Debug.Print s
    MsgBox s
    
    'Shell s, vbNormal
End Sub


Sub Main2()
    Dim s$
    'Range("H2").Value = "Ken"
    'Range("I2").Value = "Hobson"
     
    s = "net use W: ""\\psych-files\finance"" /user:" & _
      Range("H2").Value & Range("I2").Value
    'Debug.Print s
    MsgBox s
    
    'Shell s, vbNormal
End Sub

[akalehzan]

Kenneth,

Thanks you so much for your help.

Thx-Abi