File name with spaces in Call Shell function 2

[lynns]

I am trying to use an Access application as a sort of menu system. The user selects the file from a combo box. The columns in the combo box then furnish the program needed to open the file, the file path, the file name and the file extension.

This works pretty well until I run acrose a file name with a space in it. Sample code and values provided are shown below

Dim stAppName As String
Dim ProgramName As String
Dim FileLocation As String
Dim FileName As String
Dim FileExtension As String

ProgramName = "C:\Program Files\Microsoft Office\Office\msaccess.exe "
FileLocation = "\\Hlkoas01\Dover2\Warranty\Pjob costs\"
FileName = "pjob cost"
FileExtension = ".mdb"

stAppName = ProgramName & FileLocation & FileName & FileExtension

The routine submits the following string for the variable stAppName

C:\Program Files\Microsoft Office\Office\msaccess.exe \\Hlkoas01\Dover2\Warranty\Pjob costs\pjob cost.mdb

Call Shell(stAppName, 1)

This command tries to open \\Hlkoas01\Dover2\Warranty\Pjob.mdb which does not exist.

Any ideas how to resolve the space problem? I can not rename the file or directory as it does not "belong" to me.

Thanks

LynnS

 

[SgtJarrow]

try including single quotes around your variable....

stAppName = ProgramName & FileLocation & "'" & FileName & "'" & FileExtension

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[LittleSmudge]

You actually need to get some double quotes INTO the string that goes to the Call Shell()

So use

stAppName = ProgramName & """" & FileLocation & FileName & FileExtension & """"

In that way the string variable stAppName will be

C:\Program Files\Microsoft Office\Office\msaccess.exe "\\Hlkoas01\Dover2\Warranty\Pjob costs\pjob cost.mdb"


And that will work fine.

Then write out 100 times
"I must not use spaces in object names - EVER."




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D