Excel graphs in access (1 Viewer)

rikklaney1

Registered User.
Local time
Yesterday, 18:46
Joined
Nov 20, 2014
Messages
157
Morning all,

I've been asked to import some excel graphs to display on an access form. I haven't found a way to link to the actual graphs in the spreadsheet so I linked to it as a table. I am trying to recreate the graphs in access but what I want to know is if there is any way to conditionally format an access graph the way you can in excel? I.E if value is less than a target value then that bar is red else that bar is green. I have found no way to do this so far. Alternatively is there a way to just link to the chart in the excel spreadsheet and display that?
 

Ranman256

Well-known member
Local time
Yesterday, 21:46
Joined
Apr 9, 2015
Messages
4,339
Access has very basic graphing.
If you want the better graphs,use Excel,
export the data to Excel (transferspreadsheet) and the excel graph can update based on the new data.
 

JHB

Have been here a while
Local time
Today, 02:46
Joined
Jun 17, 2012
Messages
7,732
.. I.E if value is less than a target value then that bar is red else that bar is green. I have found no way to do this so far. ..
Could you post a sample in Excel sheet, (zip it).
 

jleach

Registered User.
Local time
Yesterday, 21:46
Joined
Jan 4, 2012
Messages
308
I'm not sure of how to display Excel graphs "live" in Access. Access charting is sub-par, although recent version(s?) do have some improvements.

I think the generally accepted technique is to grab the instance of Excel via automation, get the excel chart object programmatically, snapshot it as a gif image, then load the gif into a picture control in Access. There's a bit of flicker onscreen during the process, but works relatively well if you have a solid setup.

Myself, I prefer embedded JavaScript for data visualizations: https://dymeng.com/resources/browse-embed/

Anyway, here's an example for the Excel automation/chart snapshot if you want to try (obviously replace values with your own, etc). It assumes there's an excel file that the application has control of that acts as a template for generating the chart:


Code:
'RefreshAndGetPath
Private Function rgp_rcCaseloadFUSN_YTD(ci As REGISTERED_CHART_INFO, Data As Variant) As String
On Error GoTo Err_Proc
'=========================
  Dim ret As String
  'Dim wb As Excel.Workbook
  Dim wb As Object
  'Dim ws As Excel.Worksheet
  Dim ws As Object
  Dim Row As Integer
  'Dim co As Excel.ChartObject
  Dim co As Object
'=========================

  'data: array 0 to 2, %Therapist;%PrimaryClinic;$Organization
  
  Set wb = GetWorkbook(ci)
  
  Set ws = wb.Worksheets(ci.WorksheetName)
  
  ws.Cells(14, 2).Value = Data(0)
  ws.Cells(15, 2).Value = Data(1)
  ws.Cells(16, 2).Value = Data(2)
  
  Set co = ws.ChartObjects(ci.ChartObjectName)
  
  co.Chart.Refresh
  
  If FileSys.FileExists(ci.OutputFilePath) Then Kill ci.OutputFilePath
  DoEvents
  
  If co.Chart.Export(ci.OutputFilePath, "GIF") Then
    ret = ci.OutputFilePath
  End If
  
  Set co = Nothing

  wb.Save
  
'=========================
Exit_Proc:
  On Error Resume Next
  wb.Saved = True
  wb.Close
  rgp_rcCaseloadFUSN_YTD = ret
  Exit Function
Err_Proc:
  Err.source = "Charting.rgp_rcCaseloadFUSN_YTD"
  Select Case Err.Number
    Case Else
      HandleError
  End Select
  Resume Exit_Proc
  Resume
End Function
 

Users who are viewing this thread

Top Bottom