PDA

View Full Version : Insert Excel cell range in Shell command



akalehzan
12-12-2016, 11:29 AM
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
12-12-2016, 12:03 PM
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
12-12-2016, 12:16 PM
Kenneth,
Thanks for your solution.
if I want [H2] = as Excel cell H2
how to do that?

Abi

Kenneth Hobs
12-12-2016, 12:51 PM
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
12-12-2016, 01:53 PM
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
12-12-2016, 02:50 PM
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
12-12-2016, 03:12 PM
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
12-12-2016, 05:00 PM
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/content.php?148-Shortcut-Range-Notation
http://www.vbaexpress.com/forum/content.php?140-The-Evaluate-Method
'some speed tests
http://blog.excelhero.com/2010/06/05/when_working_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
12-13-2016, 01:02 PM
Kenneth,

Thanks you so much for your help.

Thx-Abi