use array in access module to create excel chart

[pungentSapling]

Ok here goes.....
I have a 2d array that is holding the proper information. The array is poulated in access. I need to use excel's cahrting capability to build a chart based on the info...
How do I pass the array into excel so I can use it as the base of my chart? There seems to be plenty of info on sending whole queries and tables, but what about variable values.


Any help would be great... thanks.

P

 

[WayneRyan]

P,

This is an excerpt from something I did a few years ago.
Hopefully, it will give you a start ...

' *****************************************
Dim LowRight As String
Dim FrameTotals(4) As Single

Dim app As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet
Dim rng As Excel.Range
Dim cht As Excel.Chart

Set app = CreateObject("Excel.Application")
app.Visible = True
Set wb = app.Workbooks.Add
Set sht = wb.ActiveSheet

sht.Cells(3, 1) = "Project"
sht.Cells(3, 2) = "82/86"
sht.Cells(3, 3) = "89A"
sht.Cells(3, 4) = "SDS"
sht.Cells(3, 5) = "Totals"

For RowCtr = 1 To ProjCtr
sht.Cells(RowCtr + 3, 1) = ProjectNames(RowCtr)
sht.Cells(RowCtr + 3, 2) = STR(Int(Frame82(RowCtr) / 60))
sht.Cells(RowCtr + 3, 3) = STR(Int(Frame89(RowCtr) / 60))
sht.Cells(RowCtr + 3, 4) = STR(Int(FrameSDS(RowCtr) / 60))
If RowCtr = 1 Then
sht.Cells(RowCtr + 3, 5) = STR(Int(FrameNT(1) / 60))
Else
sht.Cells(RowCtr + 3, 5) = STR(Int((Frame82(RowCtr) + Frame89(RowCtr) + FrameSDS(RowCtr)) / 60))
End If
Next RowCtr

sht.Cells(RowCtr + 4, 1) = ProjectNames(RowCtr)
sht.Cells(RowCtr + 4, 2) = STR(Int(FrameTotals(1) / 60))
sht.Cells(RowCtr + 4, 3) = STR(Int(FrameTotals(2) / 60))
sht.Cells(RowCtr + 4, 4) = STR(Int(FrameTotals(3) / 60))
sht.Cells(RowCtr + 4, 5) = STR(Int((FrameTotals(1) + FrameTotals(2) + FrameTotals(3)) / 60))

Set rng = sht.Range("A1", "E15")
rng.EntireColumn.AutoFit

sht.Cells(1, 1) = "WSSL Frame Usage Between " & MiscData(1) & " And " & MiscData(2)

LowRight = "D" & Trim(STR(ProjCtr + 3))
Set rng = sht.Range("B5", LowRight)
Set cht = sht.Parent.Charts.Add

With cht
.ChartWizard rng, xl3DColumn, 2, xlRows, , , , "WSSL Frame Usage Between " & MiscData(1) & " And " & MiscData(2), , "Hours"
.SeriesCollection(1).XValues = sht.Range("B3", "D3")
For RowCtr = 1 To ProjCtr - 1
.SeriesCollection(RowCtr).name = ProjectNames(RowCtr + 1)
Next RowCtr
End With

app.WindowState = xlMaximized
' *******************************************

hth,
Wayne