Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Louisiana
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating linked Excel File (Access 2000)

    Greetings.
    I have linked an excel graph to a report (and form). However, when the excel graph is updated, the link in Access shows the old data. I have tried to use the .Action =
    but I get the following error: [Program Name] can't perform the operation specified in the Action property of the Visual Basic procedure you're trying to run, error # 2793.
    How can I update by code a linked (or embedded) Excel chart in Access 2000?
    Thanks for any assistance!

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

    Re: Updating linked Excel File (Access 2000)

    When I tried this in my test database, a linked chart on a report always updated automatically. On a form, it updated automatically if the workbook was open when the form was opened, but not if the workbook was closed. The code to update a chart is

    Me.NameOfTheChartControl.Requery

    You can put this in the On Load event of the form and in the On Open event of the report. Of course, you must use the actual name of the chart control; if it contains spaces or punctuation, surround it with square brackets:

    Me.[Name Of The Chart Control].Requery

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Louisiana
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks and a 2nd problem

    Many thanks. In Automation, I was closing excel prior to opening the report/form. Your suggestion fixed this

    When I open a link to a graph, I only see a portion of it. I have tried setting the Source Item to include all the cells containing the graph but this does not work. Any Ideas?

    Thanks again,

    Tom

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

    Re: Thanks and a 2nd problem

    Take a look at the Size Mode property of the chart control (it is in the Format tab of the Properties window). Set it to Stretch or Zoom instead of Clip.

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    Louisiana
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks again.

    Thanks for your idea. Streach/Zoom did not permit the inclusion of the full graphic. However, I cheated and just deleted and copied it directly from Excel then changed its properties. This worked.

    I really appreciate all the hard work y'll do, even way out here in Lousiana.

  6. #6
    New Lounger
    Join Date
    Jun 2002
    Location
    Louisiana
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OK, I was wrong

    When I copy the Chart it becomes embedded Access will not allow me to change it to linked.

    I can link to a worksheet or embed a Chart, but I want to link to an Excel chart. I will be using automation to change the underlying data for the Chart.

    I tried changing the properties both for a linked worksheet and an embedded Graph with no luck.

    If I attempt to view the graph in the worksheet, only a portion of the graph appears. If I change Size Mode to Zoom or Stretch it just effects the fraction of the graphic already showing.

    Is it possible to LINK an Excel Graph, and if so, how?

    Sincerely,

    One Frustrated Puppy

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

    Re: OK, I was wrong

    1. Click once on the chart in Excel.
    2. Copy to the clipboard (Edit | Copy or Ctrl+C)
    3. Switch to Access.
    4. Open the form or report in design view.
    5. Select Edit | Paste Special...
    6. Click Paste Link, then OK.
    7. Set the Size Mode property of the control.

  8. #8
    New Lounger
    Join Date
    Jun 2002
    Location
    Louisiana
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks, it worked!

    Many thanks, I'm back on the road again!

    Regards,

Posting Permissions

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