Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Open Excel workbook from Access Form (2000/XP)

    I would like to have a command button on a form that when clicked opens a specific excel workbook. I have written the following code. I can get it to open a fresh excel workbook but can't get past the bit to open the specific file, can you help?

    Private Sub cmdExcel_Click()
    Dim AppExcel As Excel.Application
    On Error Resume Next
    AppActivate "Microsoft Excel"
    If Err Then
    Shell ("C:Program FilesMicrosoft OfficeOffice10excel.exe"), vbNormalNoFocus
    AppActivate "Microsoft Excel"

    End If

    On Error GoTo 0

    With AppExcel
    .Workbooks.Open ("Cocuments and Settingslocation of file.xls")
    End With

    End Sub
    Jerry
    Jerry

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Open Excel workbook from Access Form (2000/XP)

    Your code appears to be a mix of Automation and use of the Shell command. I'm not sure you want to use Automation - do you want a visible copy of Excel so a user can manipulate data in Excel, or are you trying to do manipulation of data in code from Access? If it's the latter, you might want to look at <!mskb=210288>Microsoft Knowledge Base Article 210288<!/mskb> and you might want to have a look at our Automation tutorial on our website - select Support/Tutorials and it's at the top of the list.
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Excel workbook from Access Form (2000/XP)

    Hi Wendell
    I will fill you in a bit about the background. I have created a database that someone is inputting results of a questionairre. Due to time restraints to get the DB up and running I created an Excel workbook which was connected to qryAll. Due to the complexity of the breakdown of responses I was not able to provide a full set of queries within the database and did them as simple CountIf() in the workbook where qryAll was connected through using a database query.

    I created a Report menu which is a mixture of buttons that go off to reports in the DB. To smarten it up I had the "smart" idea of setting cmdExcel to open the Excel workbook report as it has a nearly complete summary detail of the results.

    The code I wrote in my previous post was taken from my old Microsoft Access 97 VBA book from the Automation section, it was originally used to automate a word document, but I thought I would adapt it using the Shell command and then see if I could get the workbook to open!

    It was one of my "Wouldn't it be nice to have...." ideas, as opposed to a "We must have...."

    Jerry
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Excel workbook from Access Form (2000/XP)

    If you simply want to open an existing Excel workbook, you can use ShellExecute. See <post#=320106>post 320106</post#> for the code and an example of how to use it.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Excel workbook from Access Form (2000/XP)

    Hi Hans

    That's the one, thank you

    Jerry
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •