Creating an Excel Chart through Access VBA

[Zygor]

I am trying to create a simplle chart in Excel via Access. I have the following code which works fine. My problem is that it runs great from my PC, but when I move the database to a server it errors out on the line - .ActiveChart.ChartType = xlColumnClustered


Private Sub cmdSendToExcel_Click()
Dim fldCount As Integer
Dim dblRHeight As Double

On Error GoTo Err_Routine
Dim nCount As Integer

Set rstExcel = Me.RecordsetClone
If rstExcel.RecordCount > 0 Then
rstExcel.MoveFirst
End If

Set xcl = CreateObject("Excel.Application")
xcl.Visible = True

With xcl
.Workbooks.Add
.Sheets("Sheet1").Activate
.Range("A2").CopyFromRecordset rstExcel
.Columns.AutoFit
End With

With xcl
.Sheets("Sheet1").Activate
dblRHeight = .Rows(1).Height

fldCount = rstExcel.Fields.Count - 1
For nCount = 0 To fldCount 'number of columns in the table "tbl_Database_AllMonths" -1 (starts at 0 not 1)
.Cells(1, 1 + nCount).Formula = rstExcel.Fields(nCount).Name
.Cells(1, 1 + nCount).Font.Bold = True
Next nCount
.Columns.AutoFit
If boolChart = True Then
.Range("A" & rstExcel.RecordCount + 5).Select
.Charts.Add
.ActiveChart.ChartType = xlColumnClustered
.ActiveChart.SetSourceData Source:=.Sheets("Sheet1").Range("AY2:AY" & rstExcel.RecordCount + 1), PlotBy:=xlColumns
.ActiveChart.SeriesCollection(1).Name = "=""Data Row"""
.ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With .ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Charted Values"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
.ActiveChart.ChartArea.Select
.ActiveSheet.Shapes("Chart 1").IncrementLeft -177.75
.ActiveSheet.Shapes("Chart 1").IncrementTop ((rstExcel.RecordCount - 7) * dblRHeight)
End If
End With

rstExcel.Close
Set rstExcel = Nothing

Exit Sub

Err_Routine:
Select Case Err.Number
Case 94
Resume Next
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
End Select

End Sub

 

[Remou]

Is the Excel DLL available on the server? Have you checked your references?

It is more normal to put front-ends with code such as that above on the users PC, rather than the server.

 

[SkipVought]

Hi,

I would be referencing the appropriate object...

   dim wb as excel.workbook, ws as excel.worksheet, ch as excel.chart
        set wb = xcl.Workbooks.Add
        set ws = wb.Sheets("Sheet1")
        ws.Range("A2").CopyFromRecordset rstExcel
        .Columns.AutoFit
    
    With ws
        dblRHeight = .Rows(1).Height
        
        fldCount = rstExcel.Fields.Count - 1
        For nCount = 0 To fldCount   'number of columns in the table "tbl_Database_AllMonths" -1 (starts at 0 not 1)
            .Cells(1, 1 + nCount).Formula = rstExcel.Fields(nCount).Name
            .Cells(1, 1 + nCount).Font.Bold = True
        Next nCount
        .Columns.AutoFit
        If boolChart = True Then
            .Range("A" & rstExcel.RecordCount + 5).Select
           set ch =  wb.Charts.Add
            ch.ChartType = xlColumnClustered
...

The reason is that most of the obejcts that your should be referencing in the With statement is NOT the application object.

When you create a chart, it adds a Chart Object to the Workbook as a sheet.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Zygor]

Turns out that if I replace the xlcolumncluster with 51 it works great.