Using strings for network path, not mapped drive, with Shell in VBA
QUESTION UPDATED, PLEASE SEE BELOW
I am trying to use string variables (exedir - the full network drive directory of an exe file, and inputdir - the location of the input file argument) to launch an exe with its argument using shell in VBA.
The commented out line in the script below does not work. If I manually set the drive using a letter I can get it to work, as shown below, but the .exe and .lam input file used here are both on networked drives (the variable comp is the unique name of the users PC, which sets the name of the .lam input file, so the input file name is different for every user).
I'm not too familiar with the syntax and borrowed it from elsewhere. Am I missing a quotation mark or maybe have too many in the commented out row?
Sub CallExeWithInput()
Set wsh = VBA.CreateObject("WScript.Shell")
'Statusnum = wsh.Run(Command:="""" & exedir & """""""" & inputdir & """""", windowstyle:=1, waitonreturn:=False)
SetCurrentDirectory "M:path"
Statusnum = wsh.Run(Command:="foo.exe ""M:path" & comp & ".lam""", windowstyle:=1, waitonreturn:=False)
End Sub
Example of exedir: \networkpathfoo.exe
Example of inputdir: \networkpathcompname.lam
Example of comp: compname << found using Environ$("computername")
I'm aware of some previous questions, having read many to get this far.
I don't want to provide the letter of the drive, and ideally want to launch the .exe with the input file using string variables to input everything. One major reason I want to only use strings, is that they can be controlled by a single variable, and when the directory changes (say with an upgrade of the .exe) it will be easy to update this script.
UPDATE
Following the comments kindly provided below I arrived here:
SetCurrentDirectory fldr
Statusnum = wsh.Run(Command:="foo.exe " & quote & inputdir & quote, windowstyle:=1, waitonreturn:=False)
Where quote = chr(34) and fldr = \networkpath
Interestingly, if inputdir is defined as a path to a lettered drive (inputdir = M:etc), this works. If it is to a network drive path (inputdir = \networketc), it launches but the .exe immediately crashes with an invalid floating point operation.
How can I alter this so the input file can be provided as a network path?
If this is not possible I will presumably need a subroutine that locates which letter the user has mapped the network drive onto in order to build my string, such as this:
path = ":foobar"
dim x as integer
for x = 65 to 90
If CBool(Len(Dir(Chr(x) & path))) Then
msgbox "It's in drive " & Chr(x)
exit for
end if
next x
excel vba shell
edited Dec 20 '18 at 23:34
Community♦
11
asked Nov 12 '18 at 15:48
PetrichorPetrichor
4221316
|
show 1 more comment
QUESTION UPDATED, PLEASE SEE BELOW
I am trying to use string variables (exedir - the full network drive directory of an exe file, and inputdir - the location of the input file argument) to launch an exe with its argument using shell in VBA.
The commented out line in the script below does not work. If I manually set the drive using a letter I can get it to work, as shown below, but the .exe and .lam input file used here are both on networked drives (the variable comp is the unique name of the users PC, which sets the name of the .lam input file, so the input file name is different for every user).
I'm not too familiar with the syntax and borrowed it from elsewhere. Am I missing a quotation mark or maybe have too many in the commented out row?
Sub CallExeWithInput()
Set wsh = VBA.CreateObject("WScript.Shell")
'Statusnum = wsh.Run(Command:="""" & exedir & """""""" & inputdir & """""", windowstyle:=1, waitonreturn:=False)
SetCurrentDirectory "M:path"
Statusnum = wsh.Run(Command:="foo.exe ""M:path" & comp & ".lam""", windowstyle:=1, waitonreturn:=False)
End Sub
Example of exedir: \networkpathfoo.exe
Example of inputdir: \networkpathcompname.lam
Example of comp: compname << found using Environ$("computername")
I'm aware of some previous questions, having read many to get this far.
I don't want to provide the letter of the drive, and ideally want to launch the .exe with the input file using string variables to input everything. One major reason I want to only use strings, is that they can be controlled by a single variable, and when the directory changes (say with an upgrade of the .exe) it will be easy to update this script.
UPDATE
Following the comments kindly provided below I arrived here:
SetCurrentDirectory fldr
Statusnum = wsh.Run(Command:="foo.exe " & quote & inputdir & quote, windowstyle:=1, waitonreturn:=False)
Where quote = chr(34) and fldr = \networkpath
Interestingly, if inputdir is defined as a path to a lettered drive (inputdir = M:etc), this works. If it is to a network drive path (inputdir = \networketc), it launches but the .exe immediately crashes with an invalid floating point operation.
How can I alter this so the input file can be provided as a network path?
If this is not possible I will presumably need a subroutine that locates which letter the user has mapped the network drive onto in order to build my string, such as this:
path = ":foobar"
dim x as integer
for x = 65 to 90
If CBool(Len(Dir(Chr(x) & path))) Then
msgbox "It's in drive " & Chr(x)
exit for
end if
next x
excel vba shell
edited Dec 20 '18 at 23:34
Community♦
11
asked Nov 12 '18 at 15:48
PetrichorPetrichor
4221316
1
Build the command you're going to run with the shell in a variable, then look at it using the debugger. Does it look correct?
– Comintern
Nov 12 '18 at 15:55
You need to know what value you're trying to achieve to compare with what you've got and know what's missing or superfluous.
– Mathieu Guindon
Nov 12 '18 at 15:58
@Comintern Thanks and nope, experimenting now. I'm not a fan of all of the """ syntax, but it's beginning to make more sense.
– Petrichor
Nov 12 '18 at 16:06
1
Dim Quote as String,Quote = Chr(34), then replace the multitude of multiple quotes with& Quote &to make it reasonably readable. At one point, you have""""""""which, to my eye ends up being"""which is 3 double quotes in a row, which probably isn't right.
– FreeMan
Nov 12 '18 at 16:06
@FreeMan great suggestion, thanks
– Petrichor
Nov 12 '18 at 16:20
|
show 1 more comment
QUESTION UPDATED, PLEASE SEE BELOW
I am trying to use string variables (exedir - the full network drive directory of an exe file, and inputdir - the location of the input file argument) to launch an exe with its argument using shell in VBA.
The commented out line in the script below does not work. If I manually set the drive using a letter I can get it to work, as shown below, but the .exe and .lam input file used here are both on networked drives (the variable comp is the unique name of the users PC, which sets the name of the .lam input file, so the input file name is different for every user).
I'm not too familiar with the syntax and borrowed it from elsewhere. Am I missing a quotation mark or maybe have too many in the commented out row?
Sub CallExeWithInput()
Set wsh = VBA.CreateObject("WScript.Shell")
'Statusnum = wsh.Run(Command:="""" & exedir & """""""" & inputdir & """""", windowstyle:=1, waitonreturn:=False)
SetCurrentDirectory "M:path"
Statusnum = wsh.Run(Command:="foo.exe ""M:path" & comp & ".lam""", windowstyle:=1, waitonreturn:=False)
End Sub
Example of exedir: \networkpathfoo.exe
Example of inputdir: \networkpathcompname.lam
Example of comp: compname << found using Environ$("computername")
I'm aware of some previous questions, having read many to get this far.
I don't want to provide the letter of the drive, and ideally want to launch the .exe with the input file using string variables to input everything. One major reason I want to only use strings, is that they can be controlled by a single variable, and when the directory changes (say with an upgrade of the .exe) it will be easy to update this script.
UPDATE
Following the comments kindly provided below I arrived here:
SetCurrentDirectory fldr
Statusnum = wsh.Run(Command:="foo.exe " & quote & inputdir & quote, windowstyle:=1, waitonreturn:=False)
Where quote = chr(34) and fldr = \networkpath
Interestingly, if inputdir is defined as a path to a lettered drive (inputdir = M:etc), this works. If it is to a network drive path (inputdir = \networketc), it launches but the .exe immediately crashes with an invalid floating point operation.
How can I alter this so the input file can be provided as a network path?
If this is not possible I will presumably need a subroutine that locates which letter the user has mapped the network drive onto in order to build my string, such as this:
path = ":foobar"
dim x as integer
for x = 65 to 90
If CBool(Len(Dir(Chr(x) & path))) Then
msgbox "It's in drive " & Chr(x)
exit for
end if
next x
excel vba shell
edited Dec 20 '18 at 23:34
Community♦
11
asked Nov 12 '18 at 15:48
PetrichorPetrichor
4221316
QUESTION UPDATED, PLEASE SEE BELOW
I am trying to use string variables (exedir - the full network drive directory of an exe file, and inputdir - the location of the input file argument) to launch an exe with its argument using shell in VBA.
The commented out line in the script below does not work. If I manually set the drive using a letter I can get it to work, as shown below, but the .exe and .lam input file used here are both on networked drives (the variable comp is the unique name of the users PC, which sets the name of the .lam input file, so the input file name is different for every user).
I'm not too familiar with the syntax and borrowed it from elsewhere. Am I missing a quotation mark or maybe have too many in the commented out row?
Sub CallExeWithInput()
Set wsh = VBA.CreateObject("WScript.Shell")
'Statusnum = wsh.Run(Command:="""" & exedir & """""""" & inputdir & """""", windowstyle:=1, waitonreturn:=False)
SetCurrentDirectory "M:path"
Statusnum = wsh.Run(Command:="foo.exe ""M:path" & comp & ".lam""", windowstyle:=1, waitonreturn:=False)
End Sub
Example of exedir: \networkpathfoo.exe
Example of inputdir: \networkpathcompname.lam
Example of comp: compname << found using Environ$("computername")
I'm aware of some previous questions, having read many to get this far.
I don't want to provide the letter of the drive, and ideally want to launch the .exe with the input file using string variables to input everything. One major reason I want to only use strings, is that they can be controlled by a single variable, and when the directory changes (say with an upgrade of the .exe) it will be easy to update this script.
UPDATE
Following the comments kindly provided below I arrived here:
SetCurrentDirectory fldr
Statusnum = wsh.Run(Command:="foo.exe " & quote & inputdir & quote, windowstyle:=1, waitonreturn:=False)
Where quote = chr(34) and fldr = \networkpath
Interestingly, if inputdir is defined as a path to a lettered drive (inputdir = M:etc), this works. If it is to a network drive path (inputdir = \networketc), it launches but the .exe immediately crashes with an invalid floating point operation.
How can I alter this so the input file can be provided as a network path?
If this is not possible I will presumably need a subroutine that locates which letter the user has mapped the network drive onto in order to build my string, such as this:
path = ":foobar"
dim x as integer
for x = 65 to 90
If CBool(Len(Dir(Chr(x) & path))) Then
msgbox "It's in drive " & Chr(x)
exit for
end if
next x
excel vba shell
excel vba shell
edited Dec 20 '18 at 23:34
Community♦
11
asked Nov 12 '18 at 15:48
PetrichorPetrichor
4221316
edited Dec 20 '18 at 23:34
Community♦
11
asked Nov 12 '18 at 15:48
PetrichorPetrichor
4221316
edited Dec 20 '18 at 23:34
Community♦
11
edited Dec 20 '18 at 23:34
Community♦
11
edited Dec 20 '18 at 23:34
Community♦
11
11
asked Nov 12 '18 at 15:48
PetrichorPetrichor
4221316
asked Nov 12 '18 at 15:48
PetrichorPetrichor
4221316
asked Nov 12 '18 at 15:48
PetrichorPetrichor
4221316
4221316
1
Build the command you're going to run with the shell in a variable, then look at it using the debugger. Does it look correct?
– Comintern
Nov 12 '18 at 15:55
You need to know what value you're trying to achieve to compare with what you've got and know what's missing or superfluous.
– Mathieu Guindon
Nov 12 '18 at 15:58
@Comintern Thanks and nope, experimenting now. I'm not a fan of all of the """ syntax, but it's beginning to make more sense.
– Petrichor
Nov 12 '18 at 16:06
1
Dim Quote as String,Quote = Chr(34), then replace the multitude of multiple quotes with& Quote &to make it reasonably readable. At one point, you have""""""""which, to my eye ends up being"""which is 3 double quotes in a row, which probably isn't right.
– FreeMan
Nov 12 '18 at 16:06
@FreeMan great suggestion, thanks
– Petrichor
Nov 12 '18 at 16:20
|
show 1 more comment
1
Build the command you're going to run with the shell in a variable, then look at it using the debugger. Does it look correct?
– Comintern
Nov 12 '18 at 15:55
You need to know what value you're trying to achieve to compare with what you've got and know what's missing or superfluous.
– Mathieu Guindon
Nov 12 '18 at 15:58
@Comintern Thanks and nope, experimenting now. I'm not a fan of all of the """ syntax, but it's beginning to make more sense.
– Petrichor
Nov 12 '18 at 16:06
1
Dim Quote as String,Quote = Chr(34), then replace the multitude of multiple quotes with& Quote &to make it reasonably readable. At one point, you have""""""""which, to my eye ends up being"""which is 3 double quotes in a row, which probably isn't right.
– FreeMan
Nov 12 '18 at 16:06
@FreeMan great suggestion, thanks
– Petrichor
Nov 12 '18 at 16:20
1
1
Build the command you're going to run with the shell in a variable, then look at it using the debugger. Does it look correct?
– Comintern
Nov 12 '18 at 15:55
Build the command you're going to run with the shell in a variable, then look at it using the debugger. Does it look correct?
– Comintern
Nov 12 '18 at 15:55
You need to know what value you're trying to achieve to compare with what you've got and know what's missing or superfluous.
– Mathieu Guindon
Nov 12 '18 at 15:58
You need to know what value you're trying to achieve to compare with what you've got and know what's missing or superfluous.
– Mathieu Guindon
Nov 12 '18 at 15:58
@Comintern Thanks and nope, experimenting now. I'm not a fan of all of the """ syntax, but it's beginning to make more sense.
– Petrichor
Nov 12 '18 at 16:06
@Comintern Thanks and nope, experimenting now. I'm not a fan of all of the """ syntax, but it's beginning to make more sense.
– Petrichor
Nov 12 '18 at 16:06
1
1
Dim Quote as String, Quote = Chr(34), then replace the multitude of multiple quotes with & Quote & to make it reasonably readable. At one point, you have """""""" which, to my eye ends up being """ which is 3 double quotes in a row, which probably isn't right.– FreeMan
Nov 12 '18 at 16:06
Dim Quote as String, Quote = Chr(34), then replace the multitude of multiple quotes with & Quote & to make it reasonably readable. At one point, you have """""""" which, to my eye ends up being """ which is 3 double quotes in a row, which probably isn't right.– FreeMan
Nov 12 '18 at 16:06
@FreeMan great suggestion, thanks
– Petrichor
Nov 12 '18 at 16:20
@FreeMan great suggestion, thanks
– Petrichor
Nov 12 '18 at 16:20
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265621%2fusing-strings-for-network-path-not-mapped-drive-with-shell-in-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53265621%2fusing-strings-for-network-path-not-mapped-drive-with-shell-in-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Build the command you're going to run with the shell in a variable, then look at it using the debugger. Does it look correct?
– Comintern
Nov 12 '18 at 15:55
You need to know what value you're trying to achieve to compare with what you've got and know what's missing or superfluous.
– Mathieu Guindon
Nov 12 '18 at 15:58
@Comintern Thanks and nope, experimenting now. I'm not a fan of all of the """ syntax, but it's beginning to make more sense.
– Petrichor
Nov 12 '18 at 16:06
1
Dim Quote as String,Quote = Chr(34), then replace the multitude of multiple quotes with& Quote &to make it reasonably readable. At one point, you have""""""""which, to my eye ends up being"""which is 3 double quotes in a row, which probably isn't right.– FreeMan
Nov 12 '18 at 16:06
@FreeMan great suggestion, thanks
– Petrichor
Nov 12 '18 at 16:20