Trouble updating linked Excel chart (unbound object in form)

[JoshuaAnthony]

Hi,

I have a form "EXPORT FORM" that displays individual records and some statistics regarding them. On the same form, I also have an unbound object which is linked to a Microsoft Excel Chart. I have my own 'next record' and 'previous record' buttons. When either of these are pressed, I open another temporary form "TEMP EXPORT FORM" which is basically exactly the same as "EXPORT FORM" except that I open it filtered to only the current record.

I export data (just the one record) to an excel file using the following command:

Dim strFileName As String
DoCmd.OutputTo acOutputForm, "Temp Export Form", acFormatXLS, strFileName

I then close "TEMP EXPORT FORM" and create a chart in a new sheet of strFileName as follows:

' This function was adapted by Joshua A C Lawes
' at 8:31pm 17/02/2004 from code provided in the microsoft support document
' ([url]www.support.microsoft.com[/url])
' "ACC2000: Using Automation to Create a Microsoft Excel Chart"
Private Sub Update_Chart()

    Dim xlApp As Excel.Application
    Dim xlWrkbk As Excel.Workbook
    Dim xlChartObj As Excel.Chart
    Dim xlSourceRange As Excel.Range
    Dim xlColPoint As Excel.Point
    Dim strFileName As String
    
    'DoCmd.OpenForm "Temp Export Form", , , "[Combined NCR Extensions]![NCR Number] = _ 
[Forms]![Export Form]![NCR Number]"
    
    strFileName = "ExportTest.xls"
    'DoCmd.OutputTo acOutputForm, "Temp Export Form", acFormatXLS, strFileName

    ' Create an Excel workbook file (named according to first argument), based on the object
    ' specified in the second argument.
    ' Create a Microsoft Excel object.
    Set xlApp = CreateObject("Excel.Application")
    ' Open the spreadsheet to which you exported the data.
    Set xlWrkbk = xlApp.Workbooks.Open(strFileName)  
    
    ' Determine the size of the range and store it.
    Set xlSourceRange = xlWrkbk.Worksheets(1).Range("A1:I2").CurrentRegion
    ' Make application viewable!
    xlApp.Visible = True
    ' Create a new chart.
    Set xlChartObj = xlApp.Charts.Add
    
    ' Format the chart.
    With xlChartObj

        ' Specify chart type as 3D.
        .ChartType = xlColumnClustered
        ' Set the range of the chart.
        .SetSourceData Source:=xlSourceRange, PlotBy:=xlRows
        ' Specify that the chart is located on a new sheet.
        .Location xlLocationAsNewSheet, Name:="Individual NCR Stats Chart"

        ' Create and set the title; set title font.
        .HasTitle = True
        With .ChartTitle
                .Characters.Text = "Statistics For NCR: " _
                                    & [Forms]![Temp Export Form]![NCR Number]
                .Font.Size = 18
        End With
       
        ' Create and set axis titles; Remove Legend
        With .Axes(xlCategory, xlPrimary)
            .HasTitle = True
            .AxisTitle.Characters.Text = "Locations"
        End With
        With .Axes(xlValue, xlPrimary)
            .HasTitle = True
            .AxisTitle.Characters.Text = "Time (Days)"
        End With
        .HasLegend = False

    End With
    
    ' Save and close the workbook then quit Microsoft Excel.
    With xlWrkbk
        .Save
        .Close
    End With

    xlApp.Quit

End Sub

Now this creates the chart in my file exactly as I wanted but it doesn't update my linked Microsoft Excel Chart object in my form. How can I refresh this object to reflect the new chart based on the record change?

THanks,

Joshua

 

[JoshuaAnthony]

Just to further describe the problem,

I am trying to automate the updating of my unbound object. I have tried setting the object properties 'automatic update' and in tools->options->advanced, set refresh interval to 1 second but the only time it ever updates is if I double-click on it. I don't want to have to do this if possible - I would like to automate the process...