none
Updating linked Excel charts that are used in Access Reports RRS feed

  • Question

  • This is driving me crazy.  So I have 3 charts, from three Excel files,  in a report that are made in excel, from Accecss Automation,  and are linked to an Access report.  In Access VB I delete the excel files and recreate it each time.  However, when I open the report it does not update it unless I close it a few times and reopen it a few times.  It is like it is keeping it in Cache, forever.  I have tried the following code and none of it works whether I put it in on load, current....does not matter.  Anyone have any other ideas or maybe instead of link export the graph out of excel and import it  or something.  At this point I am willing to try anything

    Set appexcel = CreateObject("Excel.Application")
     appexcel.Workbooks.Open "C:\Budget\1xg.xls"
     appexcel.ActiveWorkbook.RefreshAll
     Set appexcel = Nothing
     Set appexcel = CreateObject("Excel.Application")
     appexcel.Workbooks.Open "C:\Budget\lteg1.xls"
     appexcel.ActiveWorkbook.RefreshAll
     Set appexcel = Nothing
     Set appexcel = CreateObject("Excel.Application")
     appexcel.Workbooks.Open "C:\Budget\lteg2.xls"
     appexcel.ActiveWorkbook.RefreshAll
     Set appexcel = Nothing
     'Me.Refresh
     'Me!OLEChart.Action = acOLEUpdate
     Me.Requery
    Me.OLEUnbound10.Requery
    Me.OLEUnbound6.Requery
    Me.OLEUnbound8.Requery



    • Edited by mckenph Thursday, April 14, 2016 1:34 PM
    Thursday, April 14, 2016 1:32 PM

All replies

  • First of all, are you running the whole application on a local drive or in a network environment?

    Best regards, George

    Thursday, April 14, 2016 3:21 PM
  • It is on a local drive
    Thursday, April 14, 2016 3:24 PM
  • "In Access VB I delete the excel files"

    Not in your code here ...


    Best regards, George

    Thursday, April 14, 2016 3:26 PM
  • That code is actually in a function. The code above is just for the report to "try" to update the graphs. The actual graphs are being created and the files are changing. It is just in the report, when it opens, the graphs are not updated. SO the portion that is actually creating the graphs is working fine. The links are not.
    Thursday, April 14, 2016 3:30 PM
  • Well, you are not deleting any files, yet you claim so.

    How can I help you?


    Best regards, George

    Thursday, April 14, 2016 3:33 PM
  • Maybe you have it already:

    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources


    Best regards, George

    Thursday, April 14, 2016 3:40 PM
  • Here is one of the graph functions.  Again this is working fine

    Public Function CreateExcelChartbuild1xlte()
    
        Dim rst As ADODB.Recordset
        
        ' Excel object variables
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlChart As Excel.Chart
        Const conQuery = "qryplanthold1x"
        Const conSheetName = "1xlte"
        Dim i As Integer
        
        On Error GoTo HandleErr
        DoCmd.SetWarnings False
        If Dir("c:\budget\1xg.xls") <> "" Then
       Kill "c:\budget\1xg.xls"
    End If
       ' DoCmd.OpenQuery "Building- Budget"
        ' Create Excel Application object.
        Set xlApp = New Excel.Application
        
        ' Create a new workbook.
        Set xlBook = xlApp.Workbooks.Add
        
        ' Get rid of all but one worksheet.
        xlApp.DisplayAlerts = False
        For i = xlBook.Worksheets.Count To 3 Step -1
            xlBook.Worksheets(i).Delete
        Next i
        xlApp.DisplayAlerts = True
        'xlBook.Worksheets (2)
        Set xlSheet = xlBook.Worksheets(2)
       
        xlSheet.Name = "Chart"
        
        Set xlSheet = xlBook.Worksheets(1)
        ' Capture reference to first worksheet.
        Set xlSheet = xlBook.ActiveSheet
        
        ' Change the worksheet name.
        xlSheet.Name = conSheetName
        
        
        
        ' Create recordset.
        Set rst = New ADODB.Recordset
        rst.Open _
         Source:=conQuery, _
         ActiveConnection:=CurrentProject.Connection
       ' 'xlApp.Visible = True
        With xlSheet
            ' Copy field names to Excel.
            ' Bold the column headings.
            With .Cells(1, 1)
                .Value = rst.Fields(0).Name
                .Font.Bold = True
            End With
            With .Cells(1, 2)
                .Value = rst.Fields(1).Name
                .Font.Bold = True
            End With
            With .Cells(1, 3)
                .Value = rst.Fields(2).Name
                .Font.Bold = True
            End With
                   With .Cells(1, 4)
               .Value = rst.Fields(3).Name
                .Font.Bold = True
            End With
                    With .Cells(1, 5)
                .Value = rst.Fields(4).Name
                .Font.Bold = True
            End With
             ''       With .Cells(1, 6)
               '' .Value = rst.Fields(5).Name
             '''   .Font.Bold = True
          ''  End With
            ''        With .Cells(1, 7)
              ''  .Value = rst.Fields(6).Name
            ''    .Font.Bold = True
          '' End With
            ''        With .Cells(1, 8)
              ''  .Value = rst.Fields(7).Name
          ''     .Font.Bold = True
          ''  End With
        ''         With .Cells(1, 9)
          ''   .Value = rst.Fields(8).Name
            '' .Font.Bold = True
        '' End With
        
            ' Copy all the data from the recordset
            ' into the spreadsheet.
            xlApp.DisplayAlerts = True
            DoCmd.SetWarnings True
           .Range("A2").CopyFromRecordset rst
         
            ' Format the data.
            .Columns(1).AutoFit
            With .Columns(2)
                .NumberFormat = "0.000"
                .AutoFit
            End With
            With .Columns(3)
                .NumberFormat = "0.000"
                .AutoFit
            End With
            With .Columns(4)
                .NumberFormat = "0.000"
                .AutoFit
            End With
            With .Columns(5)
                .NumberFormat = "0.000"
                .AutoFit
            End With
            With .Columns(6)
                .NumberFormat = "0.000"
                .AutoFit
            End With
           '' With .Columns(7)
             ''   .NumberFormat = "0.00"
               '' .AutoFit
           '' End With
      ''      With .Columns(7)
      ''          .NumberFormat = "0.00"
     ''           .AutoFit
     ''       End With
     ''      With .Columns(8)
     ''          .NumberFormat = "#.##%"
     ''          .AutoFit
      ''      End With
        End With
        
        ' Create the chart.
    '    Set xlSheet = xlBook.Worksheets(2)
        Set xlChart = xlApp.Charts.Add
        With xlChart
            .ChartType = xlBarClustered
            .HasLegend = True
            .Legend.Font.ColorIndex = 5
            .SetSourceData xlSheet.Cells(1, 1).CurrentRegion
            .PlotBy = xlColumns
            
            .Location _
             Where:=xlLocationAsObject, _
             Name:="chart"
             
        End With
        
        ' Setting the location loses the reference, so you
        ' must retrieve a new reference to the chart.
        With xlBook.ActiveChart
            .HasTitle = True
            .HasLegend = True
            With .ChartTitle
                .Characters.Text = "1X Performance"
                .Font.Size = 16
                .Shadow = True
                .Border.LineStyle = xlSolid
            End With
            With .SeriesCollection(1)
              .Border.Weight = xlThin
              .Border.LineStyle = xlNone
              .Shadow = False
              .InvertIfNegative = False
              .Interior.ColorIndex = xlNone
              .ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
            End With
    
            With .SeriesCollection(1).DataLabels
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Position = xlLabelPositionInsideBase
                .Orientation = xlHorizontal
            End With
            With .SeriesCollection(2)
              .Border.Weight = xlThin
              .Border.LineStyle = xlNone
              .Shadow = False
              .InvertIfNegative = False
              .Interior.ColorIndex = xlNone
              .ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
            End With
    
            With .SeriesCollection(2).DataLabels
               .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Position = xlLabelPositionInsideBase
                .Orientation = xlHorizontal
            End With
     With .SeriesCollection(3)
              .Border.Weight = xlThin
              .Border.LineStyle = xlNone
              .Shadow = False
              .InvertIfNegative = False
              .Interior.ColorIndex = xlNone
              .ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
            End With
    
            With .SeriesCollection(3).DataLabels
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Position = xlLabelPositionInsideBase
                .Orientation = xlHorizontal
            End With
            With .SeriesCollection(4)
              .Border.Weight = xlThin
              .Border.LineStyle = xlNone
              .Shadow = False
              .InvertIfNegative = False
              .Interior.ColorIndex = xlNone
              .ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
            End With
    
            With .SeriesCollection(4).DataLabels
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Position = xlLabelPositionInsideBase
                .Orientation = xlHorizontal
            End With
             
       
             
            With .ChartGroups(1)
                .GapWidth = 20
                .VaryByCategories = True
            End With
            .Axes(xlCategory).TickLabels.Font.Size = 8
            .Axes(xlCategoryScale).TickLabels.Font.Size = 8
         End With
         xlApp.ActiveSheet.Shapes("Chart 1").ScaleWidth 1.5, msoFalse, msoScaleFromTopLeft 'change chart dimensions
             xlApp.ActiveSheet.Shapes("Chart 1").ScaleHeight 1.59, msoFalse, msoScaleFromTopLeft
           
        ' Display the Excel chart.
        'xlApp.Visible = True
        xlApp.DisplayAlerts = False
    xlBook.SaveAs ("c:\budget\1xg.xls"), ConflictResolution:=xlLocalSessionChanges, FileFormat:=56
    xlBook.Close
    xlApp.DisplayAlerts = True
    Set xlSheet = Nothing
    'xlApp.Quit
    
    ExitHere:
        On Error Resume Next
        ' Clean up.
        rst.Close
        Set rst = Nothing
        Set xlSheet = Nothing
        Set xlBook = Nothing
        Set xlApp = Nothing
        xlApp.Quit
        'DoCmd.OpenReport "Building Budget Graph", acViewPreview
        DoCmd.SetWarnings True
        Exit Function
        
    HandleErr:
        MsgBox Err & ": " & Err.Description, , "Error in CreateExcelChart"
        Resume ExitHere
    End Function
    

    Thursday, April 14, 2016 3:42 PM
  • Maybe you have it already:

    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources


    Best regards, George


    I tried that but get a remote server error
    Friday, April 15, 2016 2:34 PM
  • OK figured what  did wrong but still not updating the chart
    Friday, April 15, 2016 2:52 PM
  • Hi,mckenph

    As far as I know that there are some issue about Access do not update OLE object on Report. So I suggest you could check whether this issue can be reproduced on Form.

    I will keep on doing the research about your problem. There might be some delay about the response. Appreciate your patience.

    Thanks for your understanding.

    Friday, April 15, 2016 3:49 PM
  • Check this setting for the workbook in question:

    File->Options->Formulas->Workbook. Set Calculation to Automatic


    Best regards, George

    Sunday, April 17, 2016 3:15 PM