Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need graphs from Excel (Access 2003/07)

    What coding needs to go into a button in Access to open up a graph in Excel?
    How do I get Excel to link to a table/query in the Access program?

    Being developed in Office 2003, but may need to be compatible with Office 97

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need graphs from Excel (Access 2003/07)

    why not build the graph in access?

  3. #3
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need graphs from Excel (Access 2003/07)

    1. Excel gives me more control over legends and graph types. Access forces me to make groups vs. absolute values
    2. Did not think that Office 97 would support graphs in Access.

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

    Re: Need graphs from Excel (Access 2003/07)

    You can do quite a bit with graphs in Access. The Chart Wizard will always use a totals or crosstab query, but you can set the Row Source of a chart control to any table, query or SQL statement.

    See the Microsoft Access 97 Sample Graphs database - a free download demonstrating how you can manipulate charts in VBA.

    If you want to create a chart in Excel based on Access data, you can use Data | Get External Data | New Database Query to link Access data into a worksheet; you can then create a chart based on the data. The linked data can be updated automatically or manually. You can also create a pivot table in Excel based on Access data without importing the data themselves, then create a chart based on the pivot table.

  5. #5
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need graphs from Excel (Access 2003/07)

    Thanks Hansv

    I need a chart similar to the stock report open/close/high/low/volume that Excel offers, think it will be too much work to create in access.

    Thanks for the Excel info about linking to Access. I should get to this over the weekend.

    I made a button to open Excel, but don't have syntax to tell it what spreadsheet and worksheet to open. Chart is on worksheet2 of 3. Can you provide a sample of correct code for my Access button?

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

    Re: Need graphs from Excel (Access 2003/07)

    Probably the easiest way is to use the FollowHyperlink method in the On Click event of the button:
    <code>
    Application.FollowHyperlink "C:ExcelTest.xls#'SheetName'!A1"
    </code>
    where
    C:ExcelTest.xls is the full path and filename of the workbook, SheetName is the name of the sheet that you want to activate and A1 is the cell to be selected. The # character is used to separate the filename from the reference within the file.

Posting Permissions

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