Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    Post Access 2010 Report OLEUnbound Object Not Using Lasted Excel Chart Data

    I have an Access reporting process that:

    1 Using a Report Selection Form, which allows the setting of Query criteria,
    2 Runs a Query, which then
    3 Opens and Excel Workbook, and
    4 Deletes old data from the Workbook Sheet, then
    5 Closes the Workbook and Quits Excel, after which it
    6 Transfers data from the Access Query to a sheet (having the Query's name) in the Excel Workbook, then
    7 The Excel Workbook is again opened and a Chart (sheet, not an embedded chart object) is set as the active sheet. (Thus when opened the Workbook opens to display the chart rather than the data the chart is based upon.)
    8 The Excel Workbook is then saved and Excel Quit
    9 A Report using an OLEUnbound object linked to the Excel Workbook is then opened to display the selected Chart in the Workbook. Then
    10 Close the Report

    It all appears to work just fine the first time the Report is run - it displays the data selected by the Query and contained in the Excel Workbook.

    However, if the criteria specified in the Report Selector Form is changed, all goes well, the Excel Workbook is updated (confirmed by viewing it in Excel) BUT the Chart displayed in the Report is not updated.

    Closing the Report, and/or the Report Selector, and/or the whole database does not cause the Chart currently in the Excel Workbook to be displayed - only the Chart displayed when the Report was last saved from Design mode.

    The OLEUnbound object Properties are set as follows:
    SourceItem: Set to the Chart in the Excel Workbook
    Source Doc: Full path/filename.extension of the Workbook
    OLE Type: Linked
    OLE Type Allowed: Linked
    OLE Class: Microsoft Excel 12
    Class: Excel.Sheet.12
    Update Options: Manual
    Display Type: Content
    Enabled: Yes

    I've spent many hours trying to resolve this issue and it's driving me.

    I hope someone has a solution to this issue as I started out using Access's charting to not avail - that resulted in truncated X-Axes. So decide to use this approach with Excel which was supposed to be more reliable. That certainly isn't my experience thus far.

    There's gotta be a way....

    Cheers

    Trevor

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    A Solution

    It looks like I've found a solution to this issue. In the Report I have included the following Event Procedure:

    Code:
    Private Sub Report_Activate()
    
    
    Me!OLEChart.Action = acOLEUpdate
    
    
    End Sub
    Now to move on to solve another problem.

    Cheers

    Trevor

Posting Permissions

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