deko,

you CAN use object variables.
just set them to nothing at the end of your code.

you CAN use with/end with ONLY if used with 'plain' object variables

this is ok:
set xla = createobject("excel.application")
set wkb = xla.workbooks("foo.xls")
set wks = wkb.worksheets(3)
....
with wks
.cells(3,4) = 1
end with
....
set wks = nothing
wkb.close
set wkb = nothing
xla.quit
set xla = nothing


Following with/endwith forces VBA to create it's own( internal)
object variable for the worksheet and should be avoided as it is
uncertain if it can be dereferenced when you attempt to quit the
automated instance.

with wkb.worksheets(3)
.cells(3,4) = 2
end with
set wkb = nothing


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


deko wrote :

Couple of thoughts.

Is wksname a string containing the sheet name, or an object with the
sheet?
If the latter, try wksname.Name.

Does the sheet name include spaces, if so, try something like

strLastTest = "='" & wksName & "'!A" & lr

Finally, is lr initialised?

If none of these, show the code that initialises the variables, and
where the error occurs.

I think I was making it more complicated than it needed to be. This
seems to do the trick:

xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(i + 1, 2) = _

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A" & lr)

Below is more complete code. One thing I'm doing differently is not
using any object variables or With/End With statements - I've heard
that can be a cause of Excel not quitting properly with xlapp.Quit.

I'd appreciate any additional feedback you care to offer...



For i = 1 To sn.Count 'loop through collection of sheet names
If blnStop Then Exit For
Forms("frmMain")!txtStatus = "Creating chart " & i & " of " &
sn.Count
DoEvents
lc =
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells
(xlCel lTypeLastCell).Column
lr =
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A1").SpecialCells
(xlCel lTypeLastCell).Row

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s(1).EntireRow.Horiz
ontalA lignment = xlCenter

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s(1).EntireRow.Font.
Bold = True
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e _
(xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cel ls(2,
2), _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s((lr + 3),
lc)).NumberFormat = "0.00000"
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s((lr + 2), 1) =
"Precision"
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s((lr + 2),
1).Font.Bold = True
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s((lr + 2),
2).Formula = "=AVERAGE(C2:C" & lr & ")"
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s((lr + 3), 1) =
"Repeatability"
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s((lr + 3),
1).Font.Bold = True
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s((lr + 3),
2).Formula = "=STDEV(B2:B" & lr & ")"
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A2:A" &
lr).NumberFormat = "mm/dd/yyyy hh:mm;@"
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B2:B" &
lr).Formula = "=AVERAGE(" & xlapp.Workbooks _

(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workboo ks(strXlsFile).Works
heets _
(sn(i)).Cells(2, 4),
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s _
(2, lc)).Address(0, 0) & ")"
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("C2:C" &
lr).Formula = "=STDEV(" & xlapp.Workbooks _

(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workboo ks(strXlsFile).Works
heets _
(sn(i)).Cells(2, 4),
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s _
(2, lc)).Address(0, 0) & ")"
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Colu mns.AutoFit
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s((lr + 5),
1) = _ "Created " & Format(Date, "Medium Date") & " with
Munch 1.5.12" strRange = "'[" &
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Pare nt.Name & _
"]" & xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Name
& "'!" & _

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e(xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Cells(2, 4),
xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Cells(lr, lc)).Address If
blnSummary Then
xlapp.Workbooks(strXlsFile).Worksheets(1).Name = "Summary"
xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(i + 1, 1) = sn(i)
xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(i + 1, 2)
= _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("A" & lr)
xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(i + 1, 3)
= _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("B" & lr)
xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(i + 1, 4)
= _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Rang e("C" & lr)

xlapp.Workbooks(strXlsFile).Worksheets(1).Range("B :B").NumberFormat =
"mm/dd/yyyy hh:mm;@"

xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(1) .EntireRow.Horizonta
lAlign ment = xlCenter

xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(1) .EntireRow.Font.Bold
= True
xlapp.Workbooks(strXlsFile).Worksheets(1).Columns. AutoFit
End If
dblMin = xlapp.Evaluate("MIN(IF(" & strRange & " < 0, " &
strRange & "))")
dblMax =
xlapp.Max(xlapp.Workbooks(strXlsFile).Worksheets(s n(i)).Range _
(xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cel ls(2,
4), _
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cell s(lr, lc)))
dblMid = dblMax - dblMin Select Case dblMid
Case Is 1
dblCtl = 0.1
bytRound = 1
strFormat = "0.00"
Case 0.25 To 1
dblCtl = 0.15
bytRound = 2
strFormat = "0.000"
Case Is < 0.25
dblCtl = 0.2
bytRound = 4
strFormat = "0.0000"
End Select
dblMid = dblMid * dblCtl
Set objChart =
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Char tObjects.Add _
(Left:=100, Top:=24, Width:=650, Height:=500).Chart
objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _
Worksheets(sn(i)).Range("A1:B" & xlapp.Workbooks _

(strXlsFile).Worksheets(sn(i)).Range("B1").End(xlD own).Row), _
PlotBy:=xlColumns objChart.ChartType = xlLineMarkers
objChart.Parent.Name = sn(i)
objChart.HasLegend = False
objChart.HasTitle = True
objChart.ChartTitle.Text = GetChartTitle(sn(i))
objChart.ChartTitle.Font.Bold = True
objChart.Axes(xlCategory, xlPrimary).HasTitle = False
objChart.Axes(xlValue, xlPrimary).HasTitle = False
objChart.Axes(xlCategory).TickLabels.Font.Size = 10
objChart.Axes(xlCategory).TickLabels.Orientation = 90
objChart.Axes(xlValue).TickLabels.NumberFormat = strFormat
objChart.Axes(xlValue).MaximumScale = Round((dblMax + dblMid),
bytRound)
objChart.Axes(xlValue).MinimumScale = Round((dblMin - dblMid),
bytRound)
objChart.Axes(xlValue).MajorGridlines.Border.Color Index = gl
objChart.PlotArea.Interior.ColorIndex = pa
objChart.ChartArea.Interior.ColorIndex = ca
j = 4 'data begins in column 4
Do While j <= lc 'add series
If blnStop Then Exit Do
Set objSeries = xlapp.Workbooks(strXlsFile).Worksheets _

(sn(i)).ChartObjects(1).Chart.SeriesCollection.New Series
objSeries.Values = xlapp.Workbooks(strXlsFile).Worksheets _
(sn(i)).Range(xlapp.Workbooks(strXlsFile).Workshee ts _
(sn(i)).Cells(2, j), xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Cells(lr, j)) j = j + 1
Loop
If blnStop Then Err.Raise USER_STOP
k = 0
Do While k < (objChart.SeriesCollection.Count) 'configure
series If blnStop Then Exit Do
k = k + 1 'series 1 = Mean
If k 1 Then
objChart.SeriesCollection(k).Name =
GetChartTitle(sn(i), _ "suffix") & "_" & (k - 1)
objChart.SeriesCollection(k).Border.ColorIndex = st
objChart.SeriesCollection(k).Border.Weight = xlThin
objChart.SeriesCollection(k).Border.LineStyle = xlNone

objChart.SeriesCollection(k).MarkerBackgroundColor Index = xlNone

objChart.SeriesCollection(k).MarkerForegroundColor Index = st
objChart.SeriesCollection(k).MarkerStyle = xlDot
objChart.SeriesCollection(k).Smooth = False
objChart.SeriesCollection(k).MarkerSize = 2
objChart.SeriesCollection(k).Shadow = False Else
objChart.SeriesCollection(k).Border.ColorIndex = sm
objChart.SeriesCollection(k).Border.Weight = xlThin
objChart.SeriesCollection(k).Border.LineStyle =
xlContinuous
objChart.SeriesCollection(k).MarkerBackgroundColor Index = sm
objChart.SeriesCollection(k).MarkerForegroundColor Index = sm
objChart.SeriesCollection(k).MarkerStyle = xlDiamond
objChart.SeriesCollection(k).Smooth = False
objChart.SeriesCollection(k).MarkerSize = 3
objChart.SeriesCollection(k).Shadow = False
objChart.SeriesCollection(k).ErrorBar Direction:=xlY,
Include:=xlBoth, _ Type:=xlCustom,
Amount:=xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).Range("C2:C" & lr), _
MinusValues:=xlapp.Workbooks(strXlsFile).Worksheet s _
(sn(i)).Range("C2:C" & lr)
objChart.SeriesCollection(k).ErrorBars.Border.Colo rIndex = eb
objChart.SeriesCollection(k).ErrorBars.Border.Weig ht =
xlThin

objChart.SeriesCollection(k).ErrorBars.Border.Line Style = xlContinuous
End If
objChart.Axes(xlCategory).TickLabels.NumberFormat =
"mm/dd/yyyy hh:mm;@"
objChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
Loop
Next i
xlapp.Workbooks(strXlsFile).Save
xlapp.Workbooks(strXlsFile).Close