Using Access to create dynamic chart in Excel

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I am trying to create a dynamic chart on an Excel *** via the code below
and cannot get it to work. I use the TransferSpread*** method to create the
Excel file and then open it in code

Here is some sample data from the *** that is created:

Site Function Attendance Trainer Raiting Asmt 1 Asmt 2 Asmt 3
S1 F1 1 1.4 0 1.1 0 0
S1 F2 0 0 0 2 0 0
S1 F3 1 1.45 0 1.5 0 0

There could be any number of rows, but always the same # of columns. And
there will always be at least 1 row.

I want a simple bar graph with the title being "TEMPE ASSESSMENT TRAINER".
The position and the size of the chart to be Left:=390, Width:=300, Top:=5,
Height:=200. The X values from 0.00 to 3.5 at .5 intervals. The function to
be the Category axis at the bottom. The Trainer column to be the Series 1
data.

The code below is slightly modified code from a macro that craps out at the
16th line (the SetSourceData line) with this error:

"Methods 'Sheets' of object '_Global' failed"


**SAMPLE CODE**

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryUserPrepReadinessBySiteAndFunction9", "C:\VCCT Training DB\Employee
Readiness Results Report.xls", True

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

Set objActiveWkb = objXL.Workbooks.Open("C:\VCCT Training DB\Employee
Readiness Results Report.xls")

Set objActiveWkb = objXL.Application.ActiveWorkbook

objActiveWkb.Worksheets("qryUserPrepReadinessBySiteAndFu").Cells(1,
1).Select

objXL.Visible = True

Dim myChtObj As ChartObject

Set myChtObj =
objActiveWkb.Worksheets("qryUserPrepReadinessBySiteAndFu").ChartObjects.Add(Left:=390, Width:=300, Top:=5, Height:=200)

myChtObj.Chart.ChartType = xlColumnClustered

myChtObj.SetSourceData
Source:=Sheets("qryUserPrepReadinessBySiteAndFu").Range("A1:G4"),
PlotBy:=xlRows

myChtObj.SeriesCollection.NewSeries

myChtObj.SeriesCollection(1).XValues =
"=qryUserPrepReadinessBySiteAndFu!R2C2:R4C2"

myChtObj.SeriesCollection(1).Values =
"=qryUserPrepReadinessBySiteAndFu!R2C4:R4C4"

myChtObj.SeriesCollection(1).Name =
"=qryUserPrepReadinessBySiteAndFu!R1C4"

myChtObj.Location Where:=xlLocationAsObject,
Name:="qryUserPrepReadinessBySiteAndFu"

With myChtObj.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With

With myChtObj.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With

myChtObj.HasLegend = False

myChtObj.HasDataTable = False


Any help with getting this to work would be most appreciated.

Much thanks!
Clint Herman
.

Quantcast