Solved: Punctuation in Folder names

[mdmackillop]

It's a losing battle requesting users not to use punctuation in folder names, so my code to open the folders using Explorer is failing.
Here's a typical example where Pth is usually created from 3 separate fields. I get the error: "The path '2' does not exist or is not a directory"
Any suggestions?

[vba]
Sub test()
'Pth = "C:\Aval\" & Me.Town & "\" & Me.Street & "\" & Me.NameNo
Pth = "C:\Aval\Birnam\The Avenue\Bonaccord, 2"
Shell Environ("windir") & "\Explorer.exe " & Pth, vbMaximizedFocus
End Sub
[/vba]

[Charlize]

How does the user creates a directory with a , in it ? I can't do it by using the manual way. When using code I generally run a script to remove all the illegal characters and create the folder or do a check when inputting their adress. I've got problems with users using a . in filenames. Automatic opening by right checking of extension fails if they used a . somewhere in the name. The program that needs to open the file can't do it because it doesn't recognise the extension.

Charlize

[Oorang]

The explorer command line is looking for the old dos style file names. You could write a routine to convert the path to the Dos Style name, but that particular function is already prebuilt in the Microsoft Scripting RunTime. (See Below)
[VBA]Option Explicit
#Const ccReferenceSet = False
Sub Test()
#If ccReferenceSet Then
'Requires referene to microsoft Scripting Runtime
Dim oFSO As Scripting.FileSystemObject
Set oFSO = New Scripting.FileSystemObject
#Else
Dim oFSO As Object
Set oFSO = VBA.CreateObject("Scripting.FileSystemObject")
#End If
Dim sPth As String
sPth = "C:\My Test. YourTest too\"
sPth = oFSO.GetFolder(sPth).ShortPath
VBA.Shell VBA.Environ$("windir") & "\Explorer.exe " & sPth, vbMaximizedFocus
End Sub[/VBA]

[mdmackillop]

Thanks Oorang,
I've converted it into a simple function that I can apply to a few projects.
Regards
Malcolm

[mdmackillop]

Hi Charlize,
A comma is not listed in the 9 illegal characters. The text for the folder name is also used in letter headings etc, so I can't always remove punctuation.

[tstom]

I get the error: "The path '2' does not exist or is not a directory"

My experience is that the space is your problem, not the comma. That is, the space between the comma and your 2. As a rule, when shelling, I always enclose my path arguments in quotes rather there are spaces or not.

pth = Chr(34) & "C:\Aval\Birnam\The Avenue\Bonaccord, 2" & Chr(34)

[Oorang]

Hi Tom,
Shell looks for Dos Style names so if you want to be technical the problem is both the comma and the space. You can read more about it here: http://www.computerhope.com/issues/ch000209.htm

[mdmackillop]

Hi Tom,
I don't find the problem occuring with spaces only. In this example "The Avenue" does not cause an error. Regarding enclosure in quotes, the names are always compiled from merge fields or userforms and passed as variables.