Enable Access create a graph in Excel

[Guest]

Hi,

I am designing a database that shall store some hundred thousand rows of
data.
I want to use the graphic facilities in Excel to create an XY-diagram with
10 simultaneous curves. When I tried to create such a diagram in Access the
wizard complained and told me that only 6 curves could be created in a single
graph. In Excel it works nice.

However, I think that I have reason to let Excel create the graph for me and
that Access then import the graph into an Access report - without that the
user knows that Excel has created the graph. To get the Excel code I switched
on the Macro Recorder and created the graph. I then copied, modified and
loaded the code into Access in order to let Access open Excel as an object
that created the graph.

Here is the original Excel macro code:

(in the spreadsheet Column A contains the X-value, columns B to K contains
the data)

Dim strx, strq, stry, strz As String
Dim x, y, z As Long
strx = InputBox("Input the start value", "Scaling of the graph")
x = CLng(strx) + 1
strx = CStr(x)
stry = InputBox("Input the stop value", "Scaling of the graph")
y = CLng(stry) + 1
stry = CStr(y)
strz = "A1:K1,A" & strx & ":K" & stry
strq = "A" & strx

Range(strz).Select
Range(strq).Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Channel_P").Range(strz), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

With ActiveChart.Axes(xlCategory)
.MinimumScale = x - 1
.MaximumScale = y - 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

Here ends the macro code from Excel.

****************************************

Here is the code that I copied from Excel, modified it and loaded it into
Access:

Dim obExcelProg As Object
Dim obGetFile As Object
Dim bolIsRunning As Boolean
Dim strPath, strFileName As String
Dim strx, strq, stry, strz As String
Dim x, y, z As Long

On Error Resume Next

Set xl = GetObject(, "Excel.Application")
If Err.number <> 0 Then
Set obExcelProg = CreateObject("Excel.Application")
bolIsRunning = False
Else
bolIsRunning = True
End If

strFileName = "data.xls"
strPath = "C:\temp\"
Set obKalkylBlad = obExcelProg.ActiveSheet
Set obGetFile = obExcelProg.Workbooks
obGetFile.Open FileName:=strPath & strFileName

strx = InputBox("Input the start value", "Scaling of the graph")
x = CLng(strx) + 1
strx = CStr(x)
stry = InputBox("Input the stop value", "Scaling of the graph")
y = CLng(stry) + 1
stry = CStr(y)
strz = "A1:K1;A" & strx & ":K" & stry
strq = "A" & strx

obExcelProg.Range(strz).Select
obExcelProg.Range(strq).Activate
obExcelProg.Charts.Add
obExcelProg.ActiveChart.ChartType = xlXYScatterLinesNoMarkers 'this
line doesn't work

obExcelProg.ActiveChart.SetSourceData
Source:=obExcelProg.Sheets("data").Range(strz), PlotBy:=xlColumns

obExcelProg.ActiveChart.Location Where:=xlLocationAsNewSheet


With obExcelProg.ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

With obExcelProg.ActiveChart.Axes(xlCategory)
.MinimumScale = x - 1
.MaximumScale = y - 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

With obExcelProg.ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

'(obExcelProg.Visible = True) used when debugging
obExcelProg.ActiveWorkbook.SaveAs FileName:="C:\temp\test.xls"

Stop

' the code continues here ........

' Save workbook and quit Excel
obExcelProg.ActiveWorkBook,Close False
if Not (bolIsRunning) Then
obExcelProg.Quit
End if

Set obExcelProg = Nothing

************************************

I have assumed that the Excel code should be preceeded with the object
obExcelProg to work properly in the Access environment. However all the
Access code above do not work. Access does not complain when executing the
incorrect code.

I should appreciate if someone could guide me how to deal with the Excel
code in the Access environment.


WindowsXP Home, Access 2002, Excel 2002

Regards

 

[Duane Hookom]

Before you get too far, the Wizard might limit you to 6 curves but I have
created a test chart in Access with 32 data sets. The number of lines/curves
depends on your chart's Row Source property.