VBA Open Folder Not Working Properly in Excel 2019

[bhadsock]

I have the code below that is supposed to parse a string in an Excel cell and open a mapped network drive based on that string. The code has worked perfectly for at least 5 years and still works today except for anyone that has Excel 2019. We have Excel versions from 2003 to 2019 running on Windows 10. The Excel 2019 users, the folder is opened to "My Documents" and not the specified folder. I have verified that the drives are mapped properly and you can paste the string into explorer and it will open the folder. I have searched everywhere and could not find a solution. Has something related to this changed in Excel 2019?

Here is the code:

Dim sFolderPath As String
Dim sIndexNumber As String
    
' Get the index number
sIndexNumber = Sheets("ProposedTo").Cells(2, 7)
    
' Create the file path using the IndexNumber
sFolderPath = "Q:\ProjectFiles\" + Mid(sIndexNumber, 1, 2) + "\" + Mid(sIndexNumber, 3, 2) + "\" + Mid(sIndexNumber, 5, 2) + "\" + Mid(sIndexNumber, 7, 2) + "\" + Mid(sIndexNumber, 9, 4) + "\"
    
' Open the folder in new windows explorer
Shell "explorer.exe " & """" & sFolderPath & """", vbNormalFocus

The "IndexNumber" string is a 14 digit number and the directory structure is broken down in 2 character increments until the final directory being 4 characters and the last 2 characters of the string are ignored.

Example:
Index Number = 12345678901234
Path would be: Q:\ProjectFiles\12\34\56\78\0912\

Like I said, the exact same worksheet works fine on all computers on our network except for the users who have Excel 2019.

Thanks in advance for any help!

 

[Yongle]

1. Are you using a Mac?
- I remember something similar and I think it was Mac related
- a rogue character mysteriously appeared in the filepath string when a new version was installed
- in that case it was immediately visible and correctable by by using Replace() to strip it out
- not sure that would be the case here

2. What happens if you test with a totally manual equivalent of the concatenatation using a known valid path ?
(something like this)

Shell "explorer.exe " & """" & "Q:\ProjectFiles\12\34\56\78\0912\" & """", vbNormalFocus

3. Can VBA find the path?
Try this modification

sFolderPath = "Q:\ProjectFiles\" + Mid(sIndexNumber, 1, 2) + "\" + Mid(sIndexNumber, 3, 2) + "\" + Mid(sIndexNumber, 5, 2) + "\" + Mid(sIndexNumber, 7, 2) + "\" + Mid(sIndexNumber, 9, 4) + "\"
MsgBox Len(Dir(sFolderPath, vbDirectory))

If message box returns a zero the path is not found

4. This is unlikely to make any difference but I do remember this being an issue with something a while ago
Replace + with &

sFolderPath = "Q:\ProjectFiles\" + Mid(sIndexNumber, 1, 2) + "\" + Mid(sIndexNumber, 3, 2) + "\" + Mid(sIndexNumber, 5, 2) + "\" + Mid(sIndexNumber, 7, 2) + "\" + Mid(sIndexNumber, 9, 4) + "\"
BECOMES  
sFolderPath = "Q:\ProjectFiles\" & Mid(sIndexNumber, 1, 2) & "\" & Mid(sIndexNumber, 3, 2) & "\" & Mid(sIndexNumber, 5, 2) & "\" & Mid(sIndexNumber, 7, 2) & "\" & Mid(sIndexNumber, 9, 4) & "\"

If it makes no difference revert to your original code

 

[bhadsock]

Yongle,

Thanks for you reply. Here are the responses to your questions:

1. Windows 10
2. Using a hard coded correct path as you suggested to try (pasted from explorer) it still opens to "My Documents" and not the correct folder.
3. I added the MsgBox as you indicated. In Excel 2016 (and prior) it reports a 1 and opens the correct folder. In Excel 2019 (the exact same worksheet) I get: "Run time error '52': Bad file name or number"
4. The + or & made no difference.

 

[starl]

Interesting issue...
I'd try a debug.print so you can see the folderpath being generated.
so, after your line

sFolderPath = ...

put

debug.print sFolderPath

then go into the VBE and see what appears in the immediate pane (CTRL+G)

normally, when I have a bad path, len(dir) returns a 0.. not sure what it takes to get an actual error message.

 

[Yongle]

@starl is correct but the message [ returned via MsgBox Len(Dir(sFolderPath, vbDirectory)) ] confirms that the folder path is valid

But that does not necessarily mean that the affected PCs can actually access anything in that folder
Doing it the old fashioned way ... on one of those PCs ... using Excel manually ... are you able to open a workbook in a folder that makes VBA throw error 52 ?

 

[starl]

Hi Yongle, MsgBox returned a 1 on the pc's where it's working, but generated an error on the Excel 2019. Which makes me wonder what the path being created is. Then, once the user had the path the code is created (if the issue wasn't obvious then), was going to suggest a copy/paste to open manually. That would check for user rights.

 

[Yongle]

@starl @bhadsock

- it looks like @starl has proven that there is a specific issue with Excel2019
- I do not know if it's the folder path or something that happens when VBA is doing something with it

If this line generates the same value on Excel 2019 as on other versions , then the string can be manipulated
Please test and report back

Debug.Print Len(sFolderPath)

 

[Yongle]

Oops, I should have said
If this line generates a DIFFERENT value on Excel 2019 as on other versions , then the string can be manipulated

 

[Yongle]

Thanks for you reply. Here are the responses to your questions:
1. Windows 10
2. Using a hard coded correct path as you suggested to try (pasted from explorer) it still opens to "My Documents" and not the correct folder.
3. I added the MsgBox as you indicated. In Excel 2016 (and prior) it reports a 1 and opens the correct folder. In Excel 2019 (the exact same worksheet) I get: "Run time error '52': Bad file name or number"
4. The + or & made no difference.

"Using a hard coded correct path as you suggested to try (pasted from explorer) it still opens to "My Documents" and not the correct folder"
- which tells us that the line of code is not working, rather than a problem with the path string
- VBA appears to be using the default path even when the string is known to be correct
- it may be because you need to tell VBA to change the current drive to Q

Try this ...
Insert the 7 lines below sFolderPath = ..
And run the code again

sFolderPath = "Q:\ProjectFiles\" + Mid(sIndexNumber, 1, 2) + "\" + Mid(sIndexNumber, 3, 2) + "\" + Mid(sIndexNumber, 5, 2) + "\" + Mid(sIndexNumber, 7, 2) + "\" + Mid(sIndexNumber, 9, 4) + "\"
Debug.Print 1, CurDir()
ChDrive "Q"
Debug.Print 2, CurDir()
ChDir sFolderPath
Debug.Print 3, CurDir()
CurDir ("Q:\")
Debug.Print 4, CurDir()

If the code now works
- delete the Debug.Print lines

If the code fails
- look at the immediate window in VBA (it's displayed using {CTRL} G )
- active drive should be Q
- active folder should be sFolderPath
- do the 4 messages show the drive and folder changing correctly?

 

[bhadsock]

The issue is resolved. It turns out it was a Windows permission issue and nothing to do with Excel. Thanks to the troubleshooting tips y'all provided, I was able to pin point the issue. I also learned about the immediate window in VBA which I have never used before and was instrumental in finding the issue.

Thanks again for the help!