Default properties of chart from VBA

[Ana via AccessMonster.com]

Hello! I have VBA code in access which is sending table from access to excel
and creating chart. I want to change the properties of that chart from VBA,
like to put white plot area and bold font etc. I have a code for that but it
is just NOT working and I don't know why. Everything I say like for example
xpAcc.ActiveChart.Legend. Position = xlBottom he just ignores and sets
everything on default (legend position left, grey plot area...).
Any ideas why? How am I to tell him to skip default values and listen to me?
Thx!
Ana

 

[Tom van Stiphout]

On Fri, 26 Jun 2009 08:24:28 GMT, "Ana via AccessMonster.com"

Very hard to tell without you showing us a bit more code, for example
how you create the Excel.Application object, the chart object, etc.

-Tom.
Microsoft Access MVP

 

[Ana via AccessMonster.com]

Here is my code, I would really appreciate help:

Private Sub Command24_Click()

On Error GoTo ErrorHandler
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Dim wb, wb1 As Object
Dim db As Database
Dim rs As DAO.Recordset

Dim i, j, k As Integer
Dim iRowCount As Integer
Dim iBorder As Integer
Dim iFieldNum As Integer
Dim iRecordCount As Integer
Dim s As String
Dim sSQL As String
Dim sDate As String
Dim Spath As String
Dim Sfile As String
Dim sSysMsg As String
Dim vSysCmd As Variant
sSysMsg = "Creating Excel Chart Test"
Set wb = xlApp.workbooks.Add()
Set db = CurrentDb
Spath = "C:\"
Sfile = "Excel Chart Test"

sSQL = "SELECT * " _
& "FROM QZbirnaK " _
& "ORDER BY Datzbir;"
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
rs.MoveLast 'force error 3021 if no records
rs.MoveFirst
iRecordCount = rs.RecordCount
vSysCmd = SysCmd(acSysCmdInitMeter, sSysMsg, iRecordCount)
xlApp.Visible = True
'' wb.worksheets(1)
wb.worksheets(1).Name = "ChartData"
wb.worksheets(1).Cells(1, 1).Value = "Podaci za Dijagram ZBIRNE tabele"

i = 2
For iFieldNum = 1 To rs.Fields.Count
wb.worksheets(1).Cells(i, iFieldNum).Value = rs.Fields(iFieldNum - 1).Name
Next
i = i + 1
Do Until rs.EOF
For iFieldNum = 1 To rs.Fields.Count
wb.worksheets(1).Cells(i, iFieldNum).Value = Nz(rs.Fields(iFieldNum - 1), "")
Next
vSysCmd = SysCmd(acSysCmdUpdateMeter, i)
i = i + 1
rs.MoveNext
Loop
iRowCount = i - 1
'' xlApp
xlApp.Charts.Add
xlApp.ActiveChart.SeriesCollection(1).XValues = "=ChartData!R3C1:R" &
iRowCount & "C1"

For j = 3 To rs.Fields.Count
k = j - 2
If k <> 2 Then
xlApp.ActiveChart.SeriesCollection.NewSeries
End If
xlApp.ActiveChart.SeriesCollection(k).Name = "Serija " & k & ": " & wb.
worksheets(1).Cells(2, j).Value
xlApp.ActiveChart.SeriesCollection(k).Values = "=ChartData!R3C" & j & ":R" &
iRowCount & "C" & j
Next
xlApp.ActiveChart.HasTitle = True
xlApp.ActiveChart.ChartTitle.Caption = "Prikaz Dijagrama ZBIRNIH TABELA"

xlApp.ActiveChart.axes(xlCategory, xlPrimary).HasTitle = True
xlApp.ActiveChart.axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x-
osa"
xlApp.ActiveChart.axes(xlCategory).TickLabels.Orientation = 45

xlApp.ActiveChart.axes(xlValue, xlPrimary).HasTitle = True
xlApp.ActiveChart.axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y-
osa"

For iBorder = 7 To 11
Next
End ''
'' wb.Range("A2:B2")
For iBorder = 7 To 10
Next
End ''
End ''
GoTo ThatsIt
ErrorHandler:
Select Case Err.Number
Case 3021
Case Else
MsgBox "Problem '' CreateXLChart()" & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description
End Select
ThatsIt:
vSysCmd = SysCmd(acSysCmdClearStatus)
If iRecordCount = 0 Then
wb.Close SaveChanges:=False
xlApp.Quit
End If
Set wb = Nothing
Set xlApp = Nothing
Set rs = Nothing
Set db = Nothing

End Sub

 

[Tom van Stiphout]

On Fri, 26 Jun 2009 14:24:05 GMT, "Ana via AccessMonster.com"

Here is some code that concerns me:
xlApp.Charts.Add
xlApp.ActiveChart.SeriesCollection(1).XValues = "=ChartData!R3C1:R" &
iRowCount & "C1"

You are assuming that the added chart becomes the active chart. Why
not be specific and do something like:
dim myChart as Object
set myChart = xlApp.Charts.Add
myChart.SeriesCollection(1).XValues = "=ChartData!R3C1:R" & iRowCount
& "C1"

-Tom.
Microsoft Access MVP