use command button in access to open excel file

Discussion in 'Microsoft Access Macros' started by Guest, Mar 10, 2005.

  1. Guest

    Guest 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!
     
    Guest, Mar 10, 2005
    #1
    1. Advertisements

  2. 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!
     
    Steve Schapel, Mar 10, 2005
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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!

    >
     
    Guest, Mar 10, 2005
    #3
  4. 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!)
    >
     
    Steve Schapel, Mar 10, 2005
    #4
  5. Guest

    Guest Guest

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

    >
     
    Guest, Mar 10, 2005
    #5
  6. 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).
    >
     
    Steve Schapel, Mar 10, 2005
    #6
  7. Guest

    Guest Guest

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

    >
     
    Guest, Mar 10, 2005
    #7
  8. Guest

    Guest Guest

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

    >
     
    Guest, Mar 10, 2005
    #8
  9. 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)
    >
     
    Steve Schapel, Mar 10, 2005
    #9
  10. Guest

    Guest Guest

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

    >
     
    Guest, Mar 10, 2005
    #10
  11. Guest

    ronnie.222.a67

    Joined:
    Mar 21, 2012
    Messages:
    1
    Likes Received:
    0
    Location:
    Boracay Island, Philippines
    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
     
    ronnie.222.a67, Mar 21, 2012
    #11
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Command button to open form for edit

    Guest, Oct 7, 2003, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    512
    Larry
    Oct 7, 2003
  2. Guest
    Replies:
    7
    Views:
    462
    Guest
    Dec 1, 2004
  3. Guest
    Replies:
    1
    Views:
    239
    SusanV
    Jun 14, 2006
  4. Sue

    Access 2007 command button macros

    Sue, Mar 5, 2008, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    3,808
    JBurlison
    Mar 18, 2008
  5. Juan
    Replies:
    0
    Views:
    514
Loading...

Share This Page