General discussion


Shell Function

By jrobins ·
I'm trying to use the Shell function to open an Excel file named "Expenses". The target folder changes for each client. The client folder is listed in Text18. The VB code I've tried is as follows:

Dim File As String
Dim Directory As String
Directory = Text18.Text
File = Directory & "\Expenses.xls"
Shell ("C:\Program Files\Microsoft Office_ \Office10\excel.exe File")

The program launches Excel but errors out because it says it can't find the file "File.xls". What is the correct way to code this?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Not a programmer but...

by LiamE In reply to Shell Function

Shouldnt Shell ("C:\Program Files\Microsoft Office_ \Office10\excel.exe File") read more like Shell ("C:\Program Files\Microsoft Office_ \Office10\excel.exe " & File)

Basically you are just passing the text 'File' at the end there not the variable. Dunno what the syntax should be though!

Collapse -

With associations

by Tony Hopkinson In reply to Shell Function

then all you have to so is
Shell(Directory & "\Expenses.xls")
Windows will take care of the rest.
If you want to call excel directly it should be as the previous poster said.

Collapse -

What Tony said should work

by Montgomery Gator In reply to With associations

Adding this message, because jrobins sent me an e-mail through the contact info, but I could not get back to him.

Tony: you may be a raving left winger, but you know your code :-)

Collapse -

It's safe with a specific extension

by Tony Hopkinson In reply to What Tony said should wor ...

but you can get caught out sometimes though with general ones though. I had to fix a problem where someone had given a tab separated variable file the extension txt. Double click on it and it loaded it in notepad strangely enough. Developer said it worked on my machine. Now I wonder why ?

One of the few good things about knowing VB is I know VBA as well. Personally I have extreme doubts about using it for anything serious, but others don't seem to feel that way.

Collapse -

The Solution

by jrobins In reply to Shell Function

I couldn't get any of the above to work either. Kept playing with the code. Here is what I came up with that works:

Dim File As String
Dim Directory As String
Dim Program As String
Directory = Text18.Text
Program = "C:\Program Files\Microsoft Office\Office10\Excel.exe "
File = (Directory & "\Expenses")
Shell (Program & File)

Collapse -

Insert a space

by Montgomery Gator In reply to The Solution

Make sure you insert a space between the name of the executable and the spreadsheet file, and make sure you got the correct directory for the Excel executable. It appears that this code snippet does have the space after "Excel.exe", but that would be easy to miss when typing.

Related Discussions

Related Forums