Windows XP SHell commands and paths with imbedded spaces

[bytehdz1]

I have had this problem for years but my solution is not acceptable for this client. I use the following VBA function to open specific files in Excel

Public Function gfunOpenExcel(inPath As Variant) As Integer
'open the given file in Microsoft Excel
Dim lsShellCommand As String 'text holding the open command and path
Dim RetVal As Long 'dummy variable
lsShellCommand = "C:\Program Files\Microsoft Office\Office\excel.exe " & inPath
RetVal = Shell(lsShellCommand, 1)

End Function

It runs fine as long as inPath does not contain spaces, so I cannot open files in "D:\My Documents\". I have tried "D:\""My Documents""\", "D:\[My Documents]\", and "D:\'My Documents'\" with no success.

There has to be a simple solution for this. Your thoughts please.

 

[muckshifter]

I know nuffin ... but Linux has a similar problem with a space in a folder name ... to overcome this you use " \ "

eg;
My\ Documents

the " \ " tells Linux to ignore the space.

My simple solution is NOT to use a 'space' in a folder name.

I could pose your question to a VB scripting Guru, but it may take sometime for him to answer.

 

[muckshifter]

I posted the question for you anyway ...

 

[bytehdz1]

Thank you for your reply. Yes that is my typical, never use the "enhanced" MS naming convention, but only use names that have 8 alphanumeric characters. But this client insists upon using the My Documents folder and (some) clients are NEVER wrong. I have found a work around using the RunApp command from a macro (RunApp is not allowed in VBA), but I do not like to write macros for my applications, especially with all the MS warnings that they will not be supported in a future version.


SO..., if anybody knows of a VBA solution to the SHELL command, I would greatly appreciate it.

 

[bytehdz1]

OK, it appears the solution IS simple. The file string needs its own double quote delimiters. so the command should be written as
lsShellCommand = "C:\Program Files\Microsoft Office\Office\excel.exe " & """" & inPath & """"

This will work as long as you don't have a double quote in the file name.

Sorry to have bothered you.

 

[muckshifter]

No bother ... pleased you found a solution.

 

[muckshifter]

If this is any help, this is what I got back ...

The Shell function can be used to start Excel from VBA but it isn't the best. If you first open the project, click 'Tools > References' and make sure that Excel is selected, you have total access to the Excel object model (if you're running the VBA from Excel, you don't need to set the reference since it is already set).

Something like
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Set oXL = GetObject("Excel.Application")
Set oWb = oXL.Workbook.Open(FileName:=strFullPath)
would open the xls file named in strFullPath.

If the question is about dealing with special folders as part of a path statement, something like
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(&H5&)
strPathMyDoc = objFolder.Self.Path
would return the path to 'My Documents'

I'd need to know more about the context of how and where this function was being used to offer more advice on dealing with spaces and other quirks with the path.