Problems with spaces in File/Path name

[RichFost]

Hi,

I'm trying to use the code below in a macro to open a folder in an explorer window, but as the folder i'm trying to open has spaces in it's name (i.e. S:\My Work File) it won't work...I get the error message "The path does not exist or is not a directory". :

retval = Shell("explorer.exe s:\My Work File", vbNormalFocus)

If I remove the spaces (i.e. S:\MyWorkFile) it works.

However, the folder is a centrally used one in my company and canot be renamed as it will affect loads of other peoples spreadsheets.

Any ideas how I can get this to work without changing the folder name?

Thanks.

 

[Von Pookie]

Are you positive the folder name actually has spaces?

 

[RichFost]

Yep, the actual folder name & location is:

s:\The Sales Centre\Sales Support

If I change this to

s:\TheSalesCentre\SalesSupport
or
s:\The_Sales_Centre\Sales_Support

it works ok.

But I can't change the folder names as i'd annoy several hundred people in my company who also use those folders !

 

[Richard Schollar]

In your original example, you could try this:

retval = Shell("explorer.exe s:\" & chr(34) & "My Work File" & chr(34), vbNormalFocus)

Don't know if this will work, however, and haven't tested it.

Richard

 

[RichFost]

I tried your suggestion Richard in the format below

retval = Shell("explorer.exe s:\" & Chr(34) & "The Sales Centre\Sales Support" & Chr(34), vbNormalFocus)

and I got a pop up box with the error message

The path 's:\The Sales Centre\Sales Support' does not exist or is not a directory.

Man is this frustrating !!!!

Guess that's why we all love Excel so much. Thanks for trying....

 

[Richard Schollar]

Rich

As a last guess, try this:

retval = Shell("explorer.exe s:\" & Chr(34) & "The Sales Centre" & chr(34) & "\" & chr(34) & "Sales Support" & Chr(34), vbNormalFocus)

Richard

 

[RichFost]

Nope, still get the same error message. Thanks though.

Is there another way I could go about this ?

Thanks.

 

[RichFost]

Incidentally, i'm using Excel 2000 on Windows NT 4.0