MartyConnelly
martyconnelly at shaw.ca
Mon Feb 7 19:40:23 CST 2005
One method
Private Sub Command1_Click()
On Error GoTo Err_Command0_Click
' create a form with an unbound control frame select Excel
' set control properties locked=no and enabled=yes
'OLE Object Properties Example excel chart
' put unbound object on form ,set to excel chart and add command button
'The following example creates a linked OLE object using
'an unbound object frame named OLE1 and
'sizes the control to display the object's entire contents
'when the user clicks a command button.
'set unbound control locked properties to no and enabled yes, It will
squawk and complain if you dont
OLE1.Class = "Excel.Sheet" ' Set class name.
OLE1.OLETypeAllowed = acOLELinked ' Specify type of object.
OLE1.SourceDoc = "C:\Excel\Copy of Blood sugar chart1.xls" ' or
"C:\My Documents\mensuelle.xls" ' Specify source file.
OLE1.SourceItem = "R1C1:R5C5" ' Specify data to create link to.
OLE1.Action = acOLECreateLink ' Create linked object.
OLE1.SizeMode = acOLESizeZoom ' Adjust control size.
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
or other methods
'http://msdn.microsoft.com/office/understanding/excel/technicalarticles/default.aspx?pull=/library/en-us/odc_xl2003_ta/html/odc_xl_manipulatecharts.asp
' Number of points in each Series
Const cNumCols = 10
'Number of Series
Const cNumRows = 2
Sub testexcelchart()
Dim objXL As Object
Dim objBook As Object
Dim objSheet As Object
Dim objChart As Object
Dim iRow As Integer
Dim iCol As Integer
Dim itest As Long
'Declare the required size of the dynamic array
Dim aTemp(cNumRows, cNumCols)
'Start Excel and create a new workbook
Set objXL = CreateObject("Excel.application")
Set objBook = objXL.Workbooks.Add
'Set a reference to Sheet1
Set objSheet = objBook.Worksheets.Item(1)
' Insert Random data into Cells for the two Series:
Randomize (Now())
For iRow = 1 To cNumRows
For iCol = 1 To cNumCols
aTemp(iRow, iCol) = Int(Rnd * 50) + 1
Next iCol
Next iRow
objSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp
' Add a chart object to the first worksheet
Set objChart = objSheet.ChartObjects.Add(50, 40, 300, 200).Chart
itest =
objChart.SetSourceData(Source:=objSheet.Range("A1").Resize(cNumRows,
cNumCols))
' Make Excel Visible:
objXL.Visible = True
objXL.UserControl = True
'set objects to nothing and use quit app command
End Sub
or something like
Sub MyTest()
Dim tmpArray(3, 3) As Variant
tmpArray(1, 1) = #1/1/2001#
tmpArray(2, 1) = #1/2/2001#
tmpArray(3, 1) = #1/3/2001#
tmpArray(1, 2) = 10
tmpArray(2, 2) = 20
tmpArray(3, 2) = 40
tmpArray(1, 3) = 10
tmpArray(2, 3) = 20
tmpArray(3, 3) = 40
MakeChart (tmpArray)
End Sub
'Then call Excel to make a chart and stick in a xls file
Sub MakeChart(tmpArray As Variant)
Dim objExcel As Excel.Application
Dim objChart As Excel.Chart
Dim intRows As Integer
Dim intRow As Integer
Dim strRange As String
Dim strPrefix As String
Set objExcel = New Excel.Application ' Excel is run but hidden
objExcel.Visible = True 'display it
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Mois" 'Title row in the xls Data sheet
objExcel.Cells(1, 2).Value = "Lits"
objExcel.Cells(1, 3).Value = "EL"
intRows = UBound(tmpArray, 1)
' This loop writes the data from the array to the Excel sheet
For intRow = 1 To intRows
objExcel.Cells(intRow + 1, 1).Value = tmpArray(intRow, 1)
objExcel.Cells(intRow + 1, 2).Value = tmpArray(intRow, 2)
objExcel.Cells(intRow + 1, 3).Value = tmpArray(intRow, 3)
Next intRow
' This is to select the data area to draw the graph
' A graph is always made from the selected area
strRange = "A2:" & Chr$(Asc("A") + UBound(tmpArray, 2) - 1) & _
UBound(tmpArray, 1) + 1
objExcel.Range(strRange).Select
objExcel.Range(Mid(strRange, 4)).Activate
objExcel.Application.Charts.Add
Set objChart = objExcel.ActiveChart
' Here is some formatting for the chart
With objChart
.ChartType = xl3DColumn
.HasTitle = True
.ChartTitle.Text = strPrefix & " - Occupation mensuelle moyenne "
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Caption = "Mois"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Caption = "Résidents"
.HasLegend = False
End With
' Save your Excel work.
' If the file already exists, Excel will prompt you with a message
' asking you if you want to overwrite it.
' I haven't found out how to avoid this prompt yet
objExcel.ActiveWorkbook.Close True, "mensuelle.xls"
' Free up your memory now that you're done
Set objChart = Nothing
Set objExcel = Nothing
End Sub
Dale Kalsow wrote:
>It is a chart in excel that I want to display. So it would be nice if I could just open the excel page in access. Just reading the data will not work.
>
>Any other good ideas?
>
>Thanks!
>
>Dale
>
>MartyConnelly <martyconnelly at shaw.ca> wrote:
>You could use this type of recordset for your report
>
>Dim oConn As New ADODB.Connection
>Dim oRS As New ADODB.Recordset
>oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>"Data Source=C:\Book1.xls;" & _
>"Extended Properties=""Excel 8.0;"""
>
>' Use a range with a specific address (for example, [Sheet1$A1:B10]).
>' Use the sheet name followed by a dollar sign (for example, [Sheet1$]
>or [My Worksheet$]).
>oRS.Open "Select * from [Sheet1$A1:B10]", oConn, adOpenStatic
>
>
>oConn.Close
>
>
>Dale Kalsow wrote:
>
>
>
>>Good Afternoon,
>>
>>I have an excel document that I need to take tabs or a range of cells from and include them in an access report. I am using office 2003.
>>
>>Does any one know how to do this?
>>
>>Thanks!
>>
>>Dale
>>
>>__________________________________________________
>>Do You Yahoo!?
>>Tired of spam? Yahoo! Mail has the best spam protection around
>>http://mail.yahoo.com
>>
>>
>>
>>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada