Private Sub cmdMakeChart_Click()
Dim excel_app As Excel.Application
Dim the_date As Date
Dim stop_date As Date
Dim r As Integer
Dim new_chart As Chart
Dim new_book As Workbook
Dim active_sheet As Worksheet
' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
' Comment this line to hide Excel.
excel_app.Visible = True
' Create a new spreadsheet.
Set new_book = excel_app.Workbooks.Add()
' Generate random values for the dates in January 2006.
Set active_sheet = new_book.Sheets(1)
the_date = CDate("1 January 2006")
stop_date = CDate("1 February 2006")
r = 1
Do While the_date < stop_date
active_sheet.Cells(r, 1) = the_date
active_sheet.Cells(r, 2) = Int(Rnd * 90) + 10
the_date = DateAdd("d", 1, the_date)
r = r + 1
Loop
' Make a chart that uses the data.
Set new_chart = Charts.Add()
With new_chart
.ChartType = xlLineMarkers
.SetSourceData Source:=active_sheet.Range("A1:B" & _
Format$(r - 1)), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, _
Name:=active_sheet.Name
End With
active_sheet.Shapes(active_sheet.Shapes.Count).Top = 10
active_sheet.Shapes(active_sheet.Shapes.Count).Left = _
100
active_sheet.Shapes(active_sheet.Shapes.Count).Width = _
600
active_sheet.Shapes(active_sheet.Shapes.Count).Height = _
400
ActiveChart.ChartArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "February Values"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, _
xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text _
= "Value"
End With
' Uncomment the rest of the lines to close Excel.
' Close the workbook without saving.
'excel_app.ActiveWorkbook.Close False
' Close Excel.
'excel_app.Quit
'Set excel_app = Nothing
MsgBox "Ok"
End Sub
|
