Opening folder when the folder name has a comma in it.

[jodyblau]

I am using the following code to open a folder:

Dim folderstring As String

Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenR ecordset("Selec t * from tblMisc_Setting s
where Description = 'Clients'", dbOpenDynaset)

folderstring = "explorer.e xe "
folderstring = folderstring & rs1!Data
folderstring = folderstring & "\"
folderstring = folderstring & Me.Client_Name

Shell folderstring, vbNormalFocus
rs1.Close
This seems to work great until the folder name has a comma in it. Some
of the folders will be a client's name in the format of Last, First
It appears to think that the "," is acting like a "\" because it gives
a message saying that a directory of the First Name of the client
doesn't exist.
Is there some way to fix this comma problem?

Thanks,

Jody

[greg.long]

Jody

<It appears to think that the "," is acting like a "\" because it gives
a message saying that a directory of the First Name of the client
doesn't exist.

A quirk I've just faced too - using VBA shell to create a folder
allows a comma, but navigating to that same folder by shelling to
Explorer fails.

Bummer.

The solution I've adopted is --- since the foldernames are generated
from table attributes, I do an ASCII check of the intended foldername
characters to ensure they're well-formed.

An alternative would be to test the creation and reading of the folder
in a temporary directory and proceeding from there - bit kludgy but
precise.

Can you remove the commas?

Greg

[jodyblau]

I would like to avoid removing the commas if at all possible.

Can you give me an example of the work around?

Thanks,

Jody

[jodyblau]

Is it possible to create a hyperlink object programatically and use
this to open a folder instead of the shell thing?

Jody

[Beowulf]

jodyblau wrote:

I am using the following code to open a folder:

Dim folderstring As String

Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenR ecordset("Selec t * from tblMisc_Setting s
where Description = 'Clients'", dbOpenDynaset)

folderstring = "explorer.e xe "
folderstring = folderstring & rs1!Data
folderstring = folderstring & "\"
folderstring = folderstring & Me.Client_Name

Shell folderstring, vbNormalFocus
rs1.Close

Have you tried enclosing the name of the folder in double-quotes before
sending the whole folderstring to Shell()?

Something like this may help:

folderstring = "explorer.e xe "
folderstring = folderstring & """" & rs1.Fields("Dat a")
folderstring = folderstring & "\"
folderstring = folderstring & Me.Client_Name & """"

the same thing, but using the ASCII code for the double-quote (if that's
what you prefer):

folderstring = "explorer.e xe "
folderstring = folderstring & Chr(34) & rs1.Fields("Dat a")
folderstring = folderstring & "\"
folderstring = folderstring & Me.Client_Name & Chr(34)

[jodyblau]

That worked wonderfully!

Thanks,
Jody Blau

[David W. Fenton]

Beowulf <be************ *****@hotmail.c om> wrote in
news:1EkNf.1077 5$XE6.7631@trnd dc07:

Have you tried enclosing the name of the folder in double-quotes
before sending the whole folderstring to Shell()?

That's necessary for any directory or filename that has a space in
it. When writing Shell statements, you must enclose any file/path
argument in quotes, no matter what, in order to account for all
possibilities.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/