Create Excel Chart from On-Click event in Access?

[therick]

I need the ability to create an excel chart (bar graph and pie chart) from an Access form. Any help in the right direction would be great. I assume I creat a new Excel object and pass data parameters to it but have yet to find any example code or get intellisense to work in VB. Any help here would be excellent.

 

[bagheera]

I have no experience in doing what you asking, but...;-)
If you put a control on form (let us say a button), you can attach it a macro. One of macro's action could be OutputTo which could export a table into Excel file. In this moment is all that I know. I think that based on the exported excel file you construct your chart and link to an OLE control on an Access form.

 

[1306]

Hi,

I have a code that creates pie chart from Access in Excel. But my only problem is to set the Legend label. On legend it shows 1, 2 instead of appropriate labels.

The code:
'sSource is the table name or query that you pass
Public Sub CreatePieChart(sSource As String, msFileName As String)

Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim XValuesRange As Excel.Range

Const cINITIALDATA As Integer = 2
Const cTIMESTAMPCOL As Integer = 1

'Export Data
'msFullPath is the path and the name of your excel file
If Dir(msFullPath) <> &quot;&quot; Then
Kill msFullPath
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, sSource, msFullPath, False

'Open Excel sheet
Set xlApp = CreateObject(&quot;Excel.Application&quot
Set xlWrkbk = xlApp.Workbooks.Open(msFullPath)

'format data
Set xlSourceRange = xlWrkbk.Worksheets(1).Range(&quot;a1&quot.CurrentRegion
xlSourceRange.AutoFormat xlRangeAutoFormatClassic3

'get the range
iUsed = xlWrkbk.Worksheets(1).UsedRange.Rows.Count
iCol = xlWrkbk.Worksheets(1).UsedRange.Columns.Count

'set range
Set xlSourceRange = xlWrkbk.Worksheets(1).Range(xlWrkbk.Worksheets(1).Cells(cINITIALDATA, iCol - 1), _
xlWrkbk.Worksheets(1).Cells(iUsed, iCol))
Set XValuesRange = xlWrkbk.Worksheets(1).Range(xlWrkbk.Worksheets(1).Cells(cINITIALDATA, cTIMESTAMPCOL), _
xlWrkbk.Worksheets(1).Cells(iUsed, cTIMESTAMPCOL))

' Create a new chart.
Set xlChartObj = xlApp.Charts.Add
' Format the chart.
With xlChartObj

' Specify chart type as Pie.
.ChartType = xlPie
' Set the range of the chart.
.SetSourceData Source:=xlSourceRange, PlotBy:=xlRows
' Specify that the chart is located on a new sheet.
.Location WHERE:=xlLocationAsNewSheet
' Create and set the title; set title font.
.HasTitle = True
With .ChartTitle
.Characters.Text = &quot;Duplicate Names of Access Application&quot; '& vbCrLf
.Font.Size = cFONTSIZE
.Font.Bold = True
End With

.ApplyDataLabels xlDataLabelsShowPercent

'format legend
.HasLegend = True
.Legend.Position = xlLegendPositionRight

'fill area format
.ChartArea.Fill.Visible = True

'plot area format
.PlotArea.Fill.Visible = True

End With

exit_Here:
On Error Resume Next
' Save and close the workbook
' and quit Microsoft Excel.
With xlWrkbk
.Save
.Close
End With

xlApp.Quit
Set xlSourceRange = Nothing
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing
Exit Sub

End Sub

 

[pdldavis]

Hi, something else you might consider is creating the chart in Access. I created an report in Access that manipulated the data I needed for a graph. I then exported the report out as an Excel Sheet and created a link back to the graph using the following:

Dim myDir
myDir = Dir(&quot;C:\pqfolder&quot;, vbDirectory)
'Check to see if directory exists
If myDir = &quot;&quot; Then
'Create the Directory
MkDir (&quot;C:\pqfolder&quot
End If

Dim MyFile
MyFile = Dir(&quot;C:\pqfolder\PQdata.xls&quot
'Check to see if PQdata file exists.
If MyFile <> &quot;&quot; Then
'Delete the file
Kill (&quot;c:\pqfolder\Pqdata.xls&quot
End If

'Open data report and save as excel file.
DoCmd.OpenReport &quot;rptPQcharts&quot;, acViewPreview
DoCmd.Close acReport, &quot;rptPQcharts&quot;, acSaveYes
DoCmd.OutputTo acOutputReport, &quot;RptPQCharts&quot;,_ acFormatXLS, &quot;c:\pqfolder\PQdata.xls&quot;

'Create Link to Excel PQ Data
DoCmd.RunMacro &quot;MacroTransferPQ&quot;

'Display the Graph (FrmRpjections)
DoCmd.OpenForm &quot;FrmProjections&quot;, acNormal

The end result creates graphs on the fly and you can manipulate the charts in a manner similar to those used
in Excel.