use command button in access to open excel file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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.
 
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!)
 
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?
 
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,

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


Steve Schapel said:
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

S. H. Drew said:
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).
Click to expand...
Click to expand...
 
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)

Steve Schapel said:
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

S. H. Drew said:
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).
Click to expand...
Click to expand...
 
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'"
 
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!
 
hi steve!
I been using microsoft excel for my sales record, i want to create a program that, if I click the command button it will transfer for a new sheet and i want my new sheet will same as my previous work sheet using command button... can you give me an example of syntax for my problem??? thank you
 
Back
Top