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.

--
Steve Schapel, Microsoft Access MVP


S. H. Drew wrote:
> 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!

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" wrote:

> 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.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> S. H. Drew wrote:
> > 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

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?

--
Steve Schapel, Microsoft Access MVP

S. H. Drew wrote:
> 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!)
>

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" wrote:

> 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?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> S. H. Drew wrote:
> > 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 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 wrote:
> 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).
>

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" wrote:

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

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" wrote:

> 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 wrote:
> > 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,

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'"

--
Steve Schapel, Microsoft Access MVP


S. H. Drew wrote:
> 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)
>

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!

"Steve Schapel" wrote:

> 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'"
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> S. H. Drew wrote:
> > 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)
> >
>