Software

Ask something
Discussion Locked

Shell Function

0 helpful
6 replies

by jrobins

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

6 replies

by liame

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!

by tony hopkinson

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.

by montgomery gator

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 :-)

by tony hopkinson

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.

by jrobins

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
Text18.SetFocus
Directory = Text18.Text
Program = "C:\Program Files\Microsoft Office\Office10\Excel.exe "
File = (Directory & "\Expenses")
Shell (Program & File)

by montgomery gator

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.

This discussion is closed to new replies.