Unable to correctly open a network drive from VBA

Question

I have VBA code set up so that when the macro is run, a folder is created on a network drive, and then should open the created folder. However, it will only ever open the documents folder instead.

If I change the code to the below it correctly opens the folder in question (in this case the Applications folder) - how can I get it to open the newly created one instead?

If .Value = "Section 50" And Cells(i, 6) <> "" Then
    dirName = Cells(4, i).Values
    MkDir ("\\richmond\data\Environment\Traffic & Transport\T&T - NetworkManagement\Street Works 2019-20\Licences\Section 50\Applications 2019-20\" & Cells(i, 4) & " (" & Cells(i, 12) & ")")
    Call Shell("explorer.exe" & " " & "\\richmond\data\Environment\Traffic & Transport\T&T - NetworkManagement\Street Works 2019-20\Licences\Section 50\Applications 2019-20\", vbNormalFocus)

This code is part of a larger script so I've just shown the necessary section.

If .Value = "Section 50" And Cells(i, 6) <> "" Then
    dirName = Cells(4, i).Values
    MkDir ("\\richmond\data\Environment\Traffic & Transport\T&T - NetworkManagement\Street Works 2019-20\Licences\Section 50\Applications 2019-20\" & Cells(i, 4) & " (" & Cells(i, 12) & ")")
    Call Shell("explorer.exe" & " " & "\\richmond\data\Environment\Traffic & Transport\T&T - NetworkManagement\Street Works 2019-20\Licences\Section 50\Applications 2019-20\" & Cells(i, 4) & " (" & Cells(i, 12) & ")", vbNormalFocus)

Try this `Call Shell(chr(34) & "explorer.exe".... ")" & chr(34), vbNormalFocus)` What we are doing is padding the spaces in the path — Siddharth Rout, Aug 09 '19 at 08:28
To understand it... `Dim sPath as String` and then `sPath = "\\richmond\data\Environment\Traffic & Transport\T&T - NetworkManagement\Street Works 2019-20\Licences\Section 50\Applications 2019-20\" & Cells(i, 4) & " (" & Cells(i, 12) & ")"` and finally `Call Shell(chr(34) & "explorer.exe" & " " & sPath & chr(34), vbNormalFocus)` — Siddharth Rout, Aug 09 '19 at 08:32
Your shell command fails on blanks in path! It has to be surounded by double-quotes, what needs doubled double-quotes`""`(or concat an Chr(34) for better readability) see https://stackoverflow.com/a/11205721/9439330. Or use [Workbook.FollowHyperlink](https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.followhyperlink) as alternative. — ComputerVersteher, Aug 09 '19 at 08:53
@SiddharthRout same issue unfortunately! Still just opens my documents folder. — ffc2004, Aug 09 '19 at 08:56
@SiddharthRout path needs to be quoted seperate to be interpreted as one argument. — ComputerVersteher, Aug 09 '19 at 08:57
@ComputerVersteher I have the same line of code running on a different network drive and it all works perfectly, so not sure why it's the case here. All the paths are correct. — ffc2004, Aug 09 '19 at 08:57
@ComputerVersteher Code there is `If .Value = "Mobile Plant" And Cells(i, 6) <> "" Then dirName = Cells(4, i).Values MkDir ("\\wbcphfil01.wbc.lan\dts\groups\operational_services\opserv_group\Enforcement\NRSWA\Licences\Mobile Plant\Applications 2019-20\" & Cells(i, 4) & " (" & Cells(i, 12) & ")") Call Shell("explorer.exe" & " " & "\\wbcphfil01.wbc.lan\dts\groups\operational_services\opserv_group\Enforcement\NRSWA\Licences\Mobile Plant\Applications 2019-20\" & Cells(i, 4) & " (" & Cells(i, 12) & ")", vbNormalFocus) ` — ffc2004, Aug 09 '19 at 09:00
OK its the`&`that causes the trouble! But additional quotes around path solves this. To debug this. use a variable for the path and debug.print it. then try the result path on a commandline. — ComputerVersteher, Aug 09 '19 at 09:03
@ComputerVersteher why would the exact same code not run as expected when all that's changed is the path? They both use the same end section of `& Cells(i, 4) & " (" & Cells(i, 12) & ")", vbNormalFocus)` — ffc2004, Aug 09 '19 at 09:38
It's the **&** in the path! Try on commandline! Always surround paths with quotes on commandline, as the usually fail on blanks too (surprisingly not with explorer). — ComputerVersteher, Aug 09 '19 at 09:42
Start a command line (cmd.exe) and use the string passed to the`Shell`function there, what should create same results There you can play with quoting and paths containing an ampersand or not:. — ComputerVersteher, Aug 09 '19 at 10:25
I've found the issue - I had tried additional quotes around the path so it was `Call Shell("explorer.exe" & " " & ""\\richmond\data\Environment\Traffic & Transport\T&T - NetworkManagement\Street Works 2019-20\Licences\Mobile Plant\Applications 2019-20\" & Cells(i, 4) & " (" & Cells(i, 12) & ")"", vbNormalFocus)` , but I actually needed another set of double quotes around it. — ffc2004, Aug 09 '19 at 10:43
See my first comment. To create a duoble-quote inside a string you need to double it (escape). E.g.: `StringWithOneDoubleQuote = """"`would show `" `as the first and the last d-quotes are the string-literal delimiters and the second one escapes the third that is displayed at the end. As this is confusing use `StringWithOneDoubleQuote = "" & Chr(34) & ""`where the Chr function returns a d-quote to the string. — ComputerVersteher, Aug 09 '19 at 11:12

ComputerVersteher · Answer 1 · 2019-08-09T11:02:03.480

If you use Ampersands(&) in your folders path and don't escape them with^ or double-quotes it makes the command-line think that a new command is following the ampersand as it is used to run multiple commands on a single line.

Your documents folder is opened becauseexplorer \\is a explorer command-line switch.

For testing you can open a command-line (cmd.exe) and try:

explorer.exe \\richmond\data\Environment\Traffic & Transport\

what should showcommand Transport not foundas the ampersand concats multiple commands and the shell thinksTransportis the next one, but didn't find it.

Now try

explorer.exe "\\richmond\data\Environment\Traffic & Transport\"

or

explorer.exe \\richmond\data\Environment\Traffic ^& Transport\

Both commands are working now as the ampersand is escaped.

As alternative you can use Workbook.FollowHyperlink what escapes the ampersand automatic. Try

ThisWorkbook.FollowHyperlink "\\richmond\data\Environment\Traffic & Transport\"

Unable to correctly open a network drive from VBA

1 Answers1