| Feedback | Using Access Queries |

Exporting Access charts to image files

Automating the creation of a batch of files using visual basic

A database may contain data which is to be reported and shared with users in another application such as a web page. There might be a requirement to routinely save a series of charts as GIF or PNG image files.

In the following example, there is a table which contains the reasons given for requesting a Finance department to raise credit notes. These are analysed for each department within a hospital. The intention is to generate an individual pie chart for each department and then export and save the images.

A query can contain the sales ledger transaction details and links them to another table containing department names. The process uses a table (called Keydata) containing program variables in order to set a parameter of a particular department name.

A simple form has been created containing a pie chart with the data for the current department.

Linked to the transaction data in order to supply a department name
Keydata table ox
RecordNo Variable Description
1 C:\MyData\ a folder to contain exported image files
6 SURGERY name of the current department
designing a chart

The form containing the chart requires a couple of bits of VBA code linked to property events. The first timer event automatically closes the form after a short time. The second program exports the chart to a file when the form is closed / unloaded.

Private Sub Form_Timer()
If Me.TimerInterval <> 0 Then
Me.TimerInterval = 0
End If
DoCmd.Close A_FORM, "frm_with_chart"
End Sub
  Private Sub Form_Unload(Cancel As Integer)
Dim oleGrf As Object
Dim strFileName As String
'Unlock the control...
Me!Graph9.Locked = False
Me!Graph9.Enabled = True
Set oleGrf = Me!Graph9.Object
strFileName = "Crn_" & Left(LUKeyData(6), 4) & ".GIF"
oleGrf.Export FileName:=LUKeyData(1) & strFileName
Debug.Print Time(), strFileName
Set oleGrf = Nothing
Me!Graph9.Action = acOLEClose 'Restore the lock...
Me!Graph9.Locked = True
Me!Graph9.Enabled = False
End Sub

A user defined function called LUKeyData() in the second program returns the name of the department.

A macro or VBA instruction on another form can call and open this first form repeatedly to generate and export a series of charts.

A form to call and open the chart form Private Sub BtnOpenFrm_Click()

Me.TextDir = "Surgery"
UpdateKeyData 6, "SURGERY"
DoCmd.OpenForm "Frm_with_chart", acNormal, "", "", , acDialog

Me.TextDir = "Cancer"
UpdateKeyData 6, "CANCER"
DoCmd.OpenForm "Frm_with_chart", acNormal, "", "", , acDialog

Me.TextDir = "Corporate"
UpdateKeyData 6, "CORPORATE"
DoCmd.OpenForm "Frm_with_chart", acNormal, "", "", , acDialog

Me.TextDir = "Completed"
End Sub

The chart image files are now available to be incorporated in other web pages.

credit note reasons for Surgery department   Credit note reasons for Corporate department

file: Acc_Chart_Export.htm 2015 Page updated Aug 15