Graphing in Access

[Guest]

It's probably just becasue I'm lazy or just don't get but I find the Graphing
capabilities in Access to somewhat limiting and unintuitive relative to
Excel.

Are there and 3rd party graphing utilities that can be integrated with and
Access based App? I found something promising called Chart FX but it doesn't
look like it can placed in an Access form.

Thanks in advance

 

[Arvin Meyer [MVP]]

I prefer Excel for graphing too. I use automation between Excel and Access
to send the data to an Excel range which the Excel graph will then read,
then a line or 2 of VBA code saves the graph as a jpeg or a gif file which
is then linked back to the Access form or report. The entire process
completes in under a second and allows me to use the rich graphing engine in
Excel.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[SF]

I do a lot of manual data export to excel to do graph. It is a very tedious
work since I don't know coding. Do you want to share some code here.

SF

 

[Arvin Meyer [MVP]]

Here's some code. You'll need to create your own Range and charts on your
own paths and correct the code accordingly:

Private Sub cmdButton_Click()

' © 2004 - 2006 Arvin Meyer
' You are free to use it in any application,
' provided the copyright notice is left unchanged.

Dim appXL As Excel.Application
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rngCurr As Excel.Range
Dim chtXL As Excel.Chart
Dim strPath As String

On Error GoTo Error_Handler

' Open the current database and query
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from qryMyQuery Where ID =" &
Me.txtID, dbOpenSnapshot)

Set appXL = New Excel.Application
Set wkb = appXL.Workbooks.Open("C:\FolderName\ExcelFileName.xls")
Set wks = wkb.Worksheets(1)
'appXL.Visible = True

With wks
'Create the Column Headings
.Cells(1, 1).Value = "ID"
.Cells(1, 2).Value = "Column1"
.Cells(1, 3).Value = "Column2"
.Cells(1, 4).Value = "Column3"
.Cells(1, 5).Value = "Column4"
.Cells(1, 6).Value = "Column5"
.Cells(1, 7).Value = "Column6"
.Cells(1, 8).Value = "Column7"
.Cells(1, 9).Value = "Column8"
.Cells(1, 10).Value = "Column9"
.Cells(1, 11).Value = "Column10"
.Cells(1, 12).Value = "Column11"
.Cells(1, 13).Value = "Column12"
.Cells(1, 14).Value = "Column13"
.Cells(1, 15).Value = "Column14"
.Cells(1, 16).Value = "Column15"
.Cells(1, 17).Value = "Column16"
.Cells(1, 18).Value = "Column17"
'Fill Values
.Cells(2, 1).Value = rst!ID
.Cells(2, 2).Value = rst!DataFromField1
.Cells(2, 3).Value = rst!DataFromField2
.Cells(2, 4).Value = rst!DataFromField3
.Cells(2, 5).Value = rst!DataFromField4
.Cells(2, 6).Value = rst!DataFromField5
.Cells(2, 7).Value = rst!DataFromField6
.Cells(2, 8).Value = rst!DataFromField7
.Cells(2, 9).Value = rst!DataFromField8
.Cells(2, 10).Value = rst!DataFromField9
.Cells(2, 11).Value = rst!DataFromField10
.Cells(2, 12).Value = rst!DataFromField11
.Cells(2, 13).Value = rst!DataFromField12
.Cells(2, 14).Value = rst!DataFromField13
.Cells(2, 15).Value = rst!DataFromField14
.Cells(2, 16).Value = rst!DataFromField15
.Cells(2, 17).Value = rst!DataFromField16
.Cells(2, 18).Value = rst!DataFromField17
End With

DoEvents

strPath = "C:\FolderName\Images\FileName" & wks.Cells(2, 1) & ".gif"

' Build a GIF image from the Excel chart
If FileExists(strPath) Then
Kill strPath
End If

Set chtXL = wks.ChartObjects(2).Chart
chtXL.Export FileName:=strPath, FilterName:="GIF"

DoEvents

'Rebuild the image on the form
FillGraph (strPath)

Exit_Here:
wkb.Close xlDoNotSaveChanges
Set wkb = Nothing
Set appXL = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

Private Sub FillGraph(strPath As String)
If FileExists(strPath) = True Then
Me.imgControl.Picture = strPath
Else
Me.imgControl.Picture = "C:\FolderName\NoImage.gif"
End If
End Sub

Public Function FileExists(strPath As String) As Integer
On Error Resume Next

Dim intLen As Integer

intLen = Len(Dir(strPath))

FileExists = (Not Err And intLen > 0)

End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access