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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.