A
Ana via AccessMonster.com
Hello!
I have this code, and it is supposed to drow graphs dynamicly from access
table to excel. It does it ok, but when I close excel I get message "Problem
""CreateXLChart() Error 1004: Unable to get the Axes Property of the Chart
class". Does anyone know why, and what I should to to stop getting it?
Thanks!
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
I have this code, and it is supposed to drow graphs dynamicly from access
table to excel. It does it ok, but when I close excel I get message "Problem
""CreateXLChart() Error 1004: Unable to get the Axes Property of the Chart
class". Does anyone know why, and what I should to to stop getting it?
Thanks!
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