Microsoft Graph automation help

[landar]

I have code I wrote that automates an Excel application and MS Graph via an Event Procedure in Access (this is all done with Office 2000).

Whenever I run the code, I watch the Excel application open, the first sheet is populated with the data, and then I watch it automatically generate the graph. Just as the graph starts to be created it blanks out and I receive the following error in Access:

run-time error 1004

Method 'SeriesCollection' of object '_Chart' failed

The code I am running is as follows:


If Me!ClientSelectionGroup = 10 Then
If IsNull(Me!StartDate) Xor IsNull(Me!EndDate) Then
MsgBox "You must specify both a Start Week and an End Week."
Exit Sub
End If

If IsNull(Me!StartDate) And IsNull(Me!EndDate) Then
FilterStr = ""
Else
FilterStr = " WHERE ReportDate Between #" & Me!StartDate & "# and #" & Me!EndDate & "#"
End If

If IsNull(Me!xFeldList) Then
MsgBox "Please select a x field to create a chart for."
Exit Sub
End If

TableStr = "TableName"
FieldStr = Me!xFieldList.Column(0)
FieldNameStr = Me!xFieldList.Column(1)
PeriodTypeStr = "ReportDate"
PeriodTypeNameStr = "Date"
End If

SQLstr = "SELECT " & PeriodTypeStr & ", " & FieldStr & " FROM " & TableStr & FilterStr & " ORDER BY " & _
PeriodTypeStr & " DESC"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQLstr)
Set xl = CreateObject("Excel.Application")

If rs.RecordCount > 0 Then
With xl
.Visible = True
.UserControl = False
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)

With objSht
.Cells(2, 1).CopyFromRecordset rs
End With

Range("A1").Select
.ActiveCell.FormulaR1C1 = PeriodTypeStr
.ActiveCell.Font.Bold = True
Range("B1").Select
.ActiveCell.FormulaR1C1 = FieldStr
.ActiveCell.Font.Bold = True

Range("A2").Select
i = 1
Do
ActiveCell.Offset(1, 0).Activate
i = i + 1
Loop Until IsEmpty(ActiveCell)


Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B" & i), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R" & i & "C1"
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = FieldNameStr
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = PeriodTypeNameStr
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = FieldNameStr
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False
ActiveChart.HasDataTable = False

End With
Else
MsgBox "No records meet your current criteria."
End If

Thank you for any help you can provide.

~Landar

 

[Fornatian]

I think the error will be a logical one based on your 'i' counter. Step through the code line by line using a bookmark and F8 to determine which line throws the error, I'm sure it will be either:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B" & i), or

ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R" & i & "C1"

I find it easier to leave the code in the host application and call the macro that runs it.

 

[landar]

The line:

ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R" & i & "C1"

is definitely where the error is showing up.

If I remove the

ActiveChart.SeriesCollection(1).Delete

before it, the program runs, however, it only runs every other time I click the button the above Event Procedure is associated with.

This seems weird to me. Every other time it runs perfectly, and the times in between I receive the error:

Method 'Range' of object '_Global' failed

and it highlights the line:

Range("A1").Select

If I click end and run the procedure again, it works fine.

I checked my reference files and everything I need (plus some stuff) are already in there, and I also messed around with the order of precedence for the MS Excel and Graph 9.0 libraries with no change.

If I place a . before Range, it runs once and then moves onto a different area I do not have a . in front of. When I get down to the lines where the original abort occured and place the . in front of the ActiveChart lines, the database refuses to run anything, providing me with an automation error at the original abort line.

As a last resort, in case this is a case of corruption, I created a blank database, imported everything into it, set up my reference files and then ran it. I am still experiencing the exact same problems.

 

[Fornatian]

delete your object variables at end of the run and see if that helps.

Set db = nothing
Set rs = nothing
Set xl = nothing
Set objwkb = nothing
Set objsht = nothing

 

[landar]

I should have put that into my code on principle. However, it unfortunately has not changed anything. I am still have the same problem.

~Landar

 

[Fornatian]

try replacing


ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R" & i & "C1"
with

strX as string
strX = "A2:A"&i
ActiveChart.SeriesCollection(1).XValues = _
Worksheets("Sheet1").Range(strX)

 

[landar]

That worked!!! Thank you so very very much.

That solved the first problem I was having, but the second one still remains. Every other time I run the Event Procedure I receive the error:

Run-time error '1004':

Method 'Range' of object '_Global' failed

on the line:

Range("A1").Select

as detailed above.

~Landar

 

[Fornatian]

Don't use the select method it is slow and visible, instead directly interact with the Range object.

Range("A1") = PeriodTypeStr
Range("B1") = FieldStr
Range("A1:B1").Font.Bold = True

 

[landar]

I like that. It is more efficient code. Unfortunately, it does not solve the problem.

The same line still gives me the same error. If I add a . in front of these lines (e.g. change Range to .Range) then it runs and the next time aborts at the next non-dotted line, etc. until it reaches the line:

.ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B" & i), PlotBy:=xlColumns

Which gives me the following error:

Run-time error '-2147417851 (80010105)':

Automation error
The server threw an exception.

~Landar