[AccessD] Excel file in Access

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





More information about the AccessD mailing list