I am using the Shell command to open excel files. These excel files are on our server. The problem I am finding is that if the path has spaces in especially the file itself e.g purchase order.xls, the shell command will not work it will only try and open the file name up to the first space. For the example given it will try and open a file called purchase.xls
Is there anyway to use the shell command to open a file which has spaces? I have treied to replace the space with a _ and this did not work. To get around it at the moment I have made sure that all directories and file names I am trying to open do not have spaces.
Is your need specific to using the Shell command? If not, you might consider using the following code instead.
Dim objExcel As Object
Set objExcel = CreateObject("excel.application")
Not a big deal but one of my little soap box moments. Krista is spot on - I would use automation too. However I would use early binding (google early and late binding to see why). Set a reference (Tools--> References in the VB IDE) to Microsoft Excel x.0 Object Library. Then use the following code:
Dim objExcel As New Excel.Application
Dim exWB as Excel.Workbook
objExcel.Visible = True
Set exWB = objExcel.Workbooks.Open "\\Server\My Folder With A Space\MyWB.xls"
Sorry, what I actually meant by the folders is that I would like to open a folder that contains certain files. So I won't actually be referencing a file, just the folder name. It will need to open the folder then the user selects the file.
OK - here's what I would do. I have not yet figured out a way to have the Excel browser open; however, I have worked around this in the past using filesearch. Basically, I use the filesearch method to dump the filenames into a table (filenames in this example). Then I populate a list box with the filenames table, where users can simply click on the filename to open it.
Here's my filesearch code:
Sub listfiles(filename, path)
Dim ssql As String, i As Integer
.LookIn = path
.filename = filename
.MatchTextExactly = True
If .Execute() > 0 Then
For i = 1 To .foundfiles.Count
ssql = "insert into filenames values('" & Right(.foundfiles(i), Len(.foundfiles(i)) - Len((path)) - 1) & "')"
MsgBox "There were no files found."