Home	Excel	Access
\| 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

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.

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

Page updated Aug 15