Method 'Range' of object '_Global' failed

[Seth Neustein]

I have a database where I am creating dynamic reports on
the fly. That is, I have a button that, after certain
criteria is specified, will open Excel and populate a
sheet with the results of a query and then take these
query results and create a graph. However, I am
experiencing some difficulty with this.

The graph creation only works every other time. It will
work perfectly once, and then the second time it will fail
with the following error:

Run-time error '1004':

Method 'Range' of object '_Global' failed

Then the next time I run it the graph will be created
perfectly again.

Any help anyone can be in solving this issue would be much
appreciated.

The code I am using for this is as follows:

Private Sub CommandCreateChart_Click()

Dim db As Database
Dim rs As Recordset
Dim xl As Object
Dim objWkb, objSht As Variant
Dim StartDateInt, EndDateInt As Date
Dim i As Integer
Dim SQLstr, PeriodTypeStr, PeriodTypeNameStr, FilterStr,
FieldStr, TableStr, FieldNameStr As String

PeriodTypeStr = "WeekCode"
PeriodTypeNameStr = "Week Code"

If IsNull(Me!StartWeek) Xor IsNull(Me!EndWeek) Then
MsgBox "You must specify both a Start Week and an End
Week."
Exit Sub
End If

If IsNull(Me!StartWeek) And IsNull(Me!EndWeek) Then
FilterStr = ""
Else
FilterStr = " WHERE WeekCode Between " & Me!StartWeek
& " and " & Me!EndWeek
End If

If Me!ClientSelectionGroup = 1 Then
If IsNull(Me!{client name}FieldList) Then
MsgBox "Please select a {client name} field to
create a chart for."
Exit Sub
End If

TableStr = "{client name}Counts"
FieldStr = Me!{client name}FieldList.Column(0)
FieldNameStr = Me!{client name}FieldList.Column(1)
End If

If Me!ClientSelectionGroup = 2 Then
{etc. until all clients have been satisfied}
End If

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!{weird client}FieldList) Then
MsgBox "Please select a {weird client} field to
create a chart for."
Exit Sub
End If

TableStr = "{weird client}Counts"
FieldStr = Me!{weird client}FieldList.Column(0)
FieldNameStr = Me!{weird client}FieldList.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

End Sub

~Seth Neustein