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