Private Sub Form_Load()
Dim objxl As New Excel.Application
Dim objxlbook As Excel.Workbook
Dim objxlsheet As Excel.Worksheet
Dim objxlchart As ChartObject
Dim strfile As String
Dim db As Database
Dim rs As String
Dim intloop As Integer
End Sub
Private Sub Recordsource_Click()
Set objxlbook = objxl.Workbooks.Add
Set objxlsheet = objxlbook.Worksheets("sheet1")
strfiloe = "C:\Report.xls"
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(Me.Reports)
intloop = 1
With objxlsheet
.Cells(intloop, 1) = rs.Fields(0).Name
.Cells(intloop, 2) = rs.Fields(1).Name
intloop = intloop + 1
Do
.Cells(intloop, 1) = rs.Fields(0)
.Cells(intloop, 2) = rs.Fields(1)
intloop = intloop + 1
rs.MoveNext
Loop Until rs.EOF
Set objxlchart = .ChartObjects.Add(156, 0, 0, 300)
objxlchart.Activate
With objxlchart.Chart
If Me.ChartTypeOption.Value = 1 Then
.ChartType = xl3DPieExploded
Else
.ChartType = xl3DColumn
End If
.ChartArea.Border.LineStyle = xlNone
.SeriesCollection.Add Source:=objxlbook.Sheets("sheet1").Range("A1:B" & intloop - 1)
.HasTitle = True
.ChartTitle.Text = Me.Reports
.Legend.Position = xlLegendPositionRight
.ApplyDataLabels xlDataLabelsShowValue
End With
.Cells(1, 1).Activate
End With
If Len(Dir(strfile)) > 0 Then Kill strfile
objxlboo.SaveAs strfile
objxlbook.Activate
objxl.Visible = True
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set objxlchart = Nothing
Set objxlsheet = Nothing
Set objxlbook = Nothing
objxl.Quit
Set objxl = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
