use command button in access to open excel file

[S. H. Drew]

I would like to have a command button on a form that I could click that would
open a specific file in Excel. So far, all I have been able to do is create
a button that opens a blank document in Excel. Is there a way to do this
using a macro, or would a code have to be written? At this point, I'll take
either!

 

[Steve Schapel]

S. H. Drew

You can uase a RunApp macro action for this. The syntax for the Command
Line argument is like this...
"C:\Program Files\...\Excel.exe" "C:\YourFolder\YourFile.xls"

Another option is to have your file path/name in a textbox on a form,
based on a Hyperlink data type field, and enter the data like this...
File:\\C:\YourFolder\YourFile.xls
.... so then click on thios to open the file in Excel.

 

[S. H. Drew]

Hi Steve! Thanks for responsing. I couldn't get the RunApp method to work;
it opened Excel, but then I got a message which among other things said the
filename cannot contain a colon ) as one of its characters. Since my
filename contains a colon (h:\marketing\sales planning...), I'm guessing
that's why it won't open the file. BUT, the hyperlink idea worked great! I
just formatted the hyperlink to look like a button, so I got what I needed.
Thanks so much for your help! (p.s. if you've got any ideas on how to get
around the colon issue, I'm all ears!)

 

[Steve Schapel]

S. H. Drew

I do not understand the problem about the colon. I am sure it doesn't
refer to the colon in the drive letter name as in h: so it must be some
other problem. What is the rest of the path/file name? You did enclose
in ""s like I showed you?

 

[S. H. Drew]

Yes, I enclosed the filename in quotes. The message comes up in Excel and it
reads:

The file could not be accessed. Try one of the following:
Make sure the specified folder exists.
Make sure the folder that contains the file is not read-only.
Make sure the filename does not contain any of the following
characters: < > ? [ ] : | or *
Make sure the file/pathname doesn't contain more than 218 characters.

Messages 1, 2 and 4 don't apply as 1)the specified folder does exist, 2) the
file is not read only, and 3) the path/filename isn't more than 218
characters. So I'm guessing the colon is what's throwing it off; I'm
thinking maybe there's some obscure setting in Excel that's causing this.
(I'm using Access and Excel 2003 versions, if that helps).

 

[Steve Schapel]

S. H. Drew,

I can't replicate the problem. This is a common procedure which I have
done many times without incident, so I can't figure it out right at the
moment.

Could you post back with the full Command Line argument setting you are
using.

 

[S. H. Drew]

Hi Steve! I've tried several different ways of writing this code. When I
use the below code, Excel opens but I get the message I wrote about earlier:

Dim stAppName As String
stAppName = "excel.exe h:\marketing\sales
planning\excel\forms\material_num_Setup.xls"
Call Shell(stAppName, 1)

When I try this different code, I get a "syntax error" message and am sent
directly to the Visual Basic code editor:

Dim stAppName As String
stAppName = "C:\Program Files\Microsoft Office\OFFICE11\excel.exe"
"H:\Marketing\Sales Planning\Excel\Forms\materialsetupform.xls"

Since my excel file is on a public drive (H) verses my hard drive (C), I'm
wondering if that's part of the problem. Unfortunately, the file needs to
stay on the H drive. Thanks for all your help

S. H. Drew,

I can't replicate the problem. This is a common procedure which I have
done many times without incident, so I can't figure it out right at the
moment.

Could you post back with the full Command Line argument setting you are
using.

--
Steve Schapel, Microsoft Access MVP

Yes, I enclosed the filename in quotes. The message comes up in Excel and it
reads:

The file could not be accessed. Try one of the following:
Make sure the specified folder exists.
Make sure the folder that contains the file is not read-only.
Make sure the filename does not contain any of the following
characters: < > ? [ ] : | or *
Make sure the file/pathname doesn't contain more than 218 characters.

Messages 1, 2 and 4 don't apply as 1)the specified folder does exist, 2) the
file is not read only, and 3) the path/filename isn't more than 218
characters. So I'm guessing the colon is what's throwing it off; I'm
thinking maybe there's some obscure setting in Excel that's causing this.
(I'm using Access and Excel 2003 versions, if that helps).

 

[S. H. Drew]

When I try this code, I get a syntax error message and am sent directly to
Visual Basic Editor:

Dim stAppName As String
stAppName = "C:\Program Files\Microsoft Office\OFFICE11\excel.exe"
"H:\Marketing\Sales Planning\Excel\Forms\materialsetupform.xls"
Call Shell(stAppName, 1)

When I try this code, I get the error message I described earlier:

Dim stAppName As String
stAppName = "excel.exe h:\marketing\sales
planning\excel\forms\material_num_Setup.xls"
Call Shell(stAppName, 1)

S. H. Drew,

I can't replicate the problem. This is a common procedure which I have
done many times without incident, so I can't figure it out right at the
moment.

Could you post back with the full Command Line argument setting you are
using.

--
Steve Schapel, Microsoft Access MVP

Yes, I enclosed the filename in quotes. The message comes up in Excel and it
reads:

The file could not be accessed. Try one of the following:
Make sure the specified folder exists.
Make sure the folder that contains the file is not read-only.
Make sure the filename does not contain any of the following
characters: < > ? [ ] : | or *
Make sure the file/pathname doesn't contain more than 218 characters.

Messages 1, 2 and 4 don't apply as 1)the specified folder does exist, 2) the
file is not read only, and 3) the path/filename isn't more than 218
characters. So I'm guessing the colon is what's throwing it off; I'm
thinking maybe there's some obscure setting in Excel that's causing this.
(I'm using Access and Excel 2003 versions, if that helps).

 

[Steve Schapel]

S. H. Drew,

Well, I'm glad I asked for the full details. All this time, I've been
thinking we were using a RunApp macro, and discussing the Command Line
argument. And now I learn that you're not using a macro at all, let
alone RunApp, you're using a VBA procedure with the Shell() function.
I've been trying to teach you to fly a helicopter while you are sitting
on a motorbike.

A string variable is just one string. Try it like this...
stAppName = "'C:\Program Files\Microsoft Office\OFFICE11\excel.exe'
'H:\Marketing\Sales Planning\Excel\Forms\materialsetupform.xls'"

 

[S. H. Drew]

Hi Steve! I had tried the RunApp macro first, but didn't have any luck. The
code I sent you was based on what Access wrote when I created a command
button on a form and selected "Run Application" for the button's function. I
should have made that more clear. FYI, I just tried the RunApp macro
function and this time it worked. So even though the visual basic code I
wrote still isn't working (I tried what you just sent me), you've given me a
couple other options that give me what I need. Thanks a bunch!