Building a shell string

[mattmlm]

I need to create a button to load various documents with either word or excel. I have done some test with the Shell command and I have got it to work but I could do with being able to build the shell string from variables rather than having only as static.

This code works fine:

Dim retval As Long
retval = Shell("C:\Program Files\Microsoft Office\Office\excel.exe c:\my_file.xls", 1)

But what I want to be able to do is build it from variables e.g.

The form would have a combo box for the office path,
Another for the folder the excel files are stored in,
Then the file name would be part static part from a combo; so it would look something like:

Shell(Combo_officelocation & “\excel.exe” & “c:\” & Combo_excelfilefolder & “\” & Combo_file_prefix & “filename.xls”)

Is this possible with the shell command or is there another way of achieving this?

Thanks in advance.

 

[modest]

You answered your own question?

Dim retval As Long
Dim strShell As String

strShell = Me.Comob_officelocation & "\excel.exe c:\" & Me.Combo_excelfilefolder & "\" & Me.Combo_file_prefix & "filename.xls"

retval = Shell(strShell,1)

There are two things to remember though, the first is you might want to put quotes around your path (in case your folder names have spaces in them) and the second is that I think there's a better way to open an excel file.

 

[ghudson]

Yes you can easily do it. I suggest that you test your string with a message box so that you can "see" the string you are passing to the Shell.

Something like this...

AppActivate Shell("Excel.exe " & Combo_excelfilefolder & “\” & Combo_file_prefix & “filename.xls”, vbMaximizedFocus)

Although “filename.xls” should not be hardcoded.

The AppActivate will ensure the opened file receives the focus.
The ("Excel.exe " is all that is needed to open Excel.
The vbMaximizedFocus will open the Excel window as maximized.

Your final string should look like this...

AppActivate Shell("Excel.exe X:\Directory\File.xls", vbMaximizedFocus)

The quotes and spaces are very imporant or else the shell will not work. If you have problems opening the file because of the spaces then you could create a string to pass to the Shell. Like this...

Dim x As String
Dim y As String
x = "Excel.exe " [COLOR=Green]'notice the space at the end this value![/COLOR]
y = "X:\Directory\File.xls"
AppActivate Shell(x & y, vbMaximizedFocus)

 

[mattmlm]

Nearly!

Ok, I’m very nearly there with this!

The problem I have now is the one of the folder names has a space in it!

e.g. x:\some_project\excel files\the_file.xls

When excel opens I get two error messages, one saying cannot find file “x:\some_project\excel.xls” this a second says cannot open “files\the_file.xls” Is there any way to get it to pass the space correctly? I don’t think I will be able to rename the folders as they are on a server that I don’t have control over

Thanks for the help so far

 

[ghudson]

Now I remeber the pains of Shell and spaces. I normally use an OpenFile API.

Check out the OpenFile function in my Browse [Find a directory or file] sample. It will allow you to select any file and open it.

Here is how you would use it and file/directory names with spaces are no problem with the OpenFile API. Put this in a public module and run the test function at the bottom.

Option Compare Database
Option Explicit

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Function OpenFile(sFileName As String)
On Error GoTo Err_OpenFile

    OpenFile = ShellExecute(Application.hWndAccessApp, "Open", sFileName, "", "C:\", 1)

Exit_OpenFile:
    Exit Function

Err_OpenFile:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_OpenFile

End Function

Public Function TestOpeningFile()
On Error GoTo Err_TestOpeningFile
    
    Dim Y As String
    Dim Z As String
    
    Y = "C:\Temp\Testing 1 2 3\"
    Z = "Test File.xls"
    
    OpenFile Y & Z
    AppActivate Z, True
    
Exit_TestOpeningFile:
    Exit Function
    
Err_TestOpeningFile:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_TestOpeningFile
    
End Function

 

[modest]

or you can keep the code you had and just send a quote character around your string. Right now you have- debug.print "Hello", which outputs: Hello

[B]But you need to wrap it around quotes so-[/B]
debug.print """Hello""" outputs: "Hello"
debug.print """" & strVariable & """" outputs: "Hello"
debug.print chr$(34) & "Hello" & chr$(34) outputs: "Hello"
debug.print chr$(34) & strVariable & chr$(34) outputs: "Hello"
[COLOR=Green]'Use one of the methods above[/COLOR]


[COLOR=Red][B]Don't use the method below[/B][/COLOR]
debug.print ""Hello"" would not be right

More info
debug.print " ""Hello"" " would print: _"Hello"_ (notice leading/ending space)
2 quotes inside a string prints a quote w/o ending/beginning a string
3 quotes prints a quote but ends/begins a string
4 quotes does the same thing as chr$(34) or chr(34)

 

[mattmlm]

Working!!

Excellent!

Took a while to get my head round the Chr$(34) and how to use it!

The full string finished up looking like:

C:\Program Files\Microsoft Office\Office\winword.exe "x:\my file\mydocument.doc"

Works a treat! Hopefully a few more brownie points at work for me!

Cheers for all your help!

Another winner

 

[modest]

No problem, first the basics, then the world