File and pathname with spaces

[Charles Bushby]

Below is an extract of some code that fails because there are spaces in the pathname and the filename (e.g. "AR timesheet.xlsm". I have tried various options such as Chr(34), """"" and %20 but can't seem to get it to work. If I take out the spaces in the path and filename then the code works fine but for various reasons I have to have spaces.


Dim FolderName As String, wbName As String
FolderName = "\\sw-dc2\Shared\Central Region\Time sheets"
wbName = Dir(FolderName & "" & "*.xlsm")

 

[Fluff]

I suspect it's not the spaces, but the fact that you are missing a \ from the end of the folder name ie
FolderName = "\\sw-dc2\Shared\Central Region\Time sheets \ "
Then again it just stripped out the final\ in my suggestion & may have done the same for you

 

[Charles Bushby]

Yes it stripped out the \ in my post, however it is in the code so is not the problem.

 

[Fluff]

In that case I'm at a loss.
I've regularly used files & directories which have spaces in their names without any problems.

 

[xenou]

It does seem odd that you would leave out the path separator between the folder path and the file name.
What is the code supposed to do? Can't tell from the snippet. Is the path a local folder, a file share, or a remote location? What about other files in the folder? Why are you using dir() here when you want a particular file, rather than using the file name? Note that in general I wouldn't recommend DIR() with unc paths - probably using a file system object is better. I'm not really sure if DIR works with UNC paths but I don't really know for sure.

 

[Charles Bushby]

I have sorted it now - I had accidentally truncated the pathname. For reference this now works:

FolderName = "\\sw-dc2\Shared\Central Region\Time sheets\Current time sheets"
wbName = Dir(FolderName & "*.xlsm")

The Dir seems to work fine.

Thanks for you help