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)).Range("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)).Range("A1").SpecialCells
> (xlCel lTypeLastCell).Column
> lr =
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("A1").SpecialCells
> (xlCel lTypeLastCell).Row
>
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(1).EntireRow.Horiz
> ontalA lignment = xlCenter
>
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(1).EntireRow.Font.
> Bold = True
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range _
> (xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(2,
> 2), _
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells((lr + 3),
> lc)).NumberFormat = "0.00000"
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells((lr + 2), 1) =
> "Precision"
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells((lr + 2),
> 1).Font.Bold = True
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells((lr + 2),
> 2).Formula = "=AVERAGE(C2:C" & lr & ")"
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells((lr + 3), 1) =
> "Repeatability"
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells((lr + 3),
> 1).Font.Bold = True
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells((lr + 3),
> 2).Formula = "=STDEV(B2:B" & lr & ")"
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("A2:A" &
> lr).NumberFormat = "mm/dd/yyyy hh:mm;@"
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("B2:B" &
> lr).Formula = "=AVERAGE(" & xlapp.Workbooks _
>
> (strXlsFile).Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFile).Works
> heets _
> (sn(i)).Cells(2, 4),
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells _
> (2, lc)).Address(0, 0) & ")"
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("C2:C" &
> lr).Formula = "=STDEV(" & xlapp.Workbooks _
>
> (strXlsFile).Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFile).Works
> heets _
> (sn(i)).Cells(2, 4),
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells _
> (2, lc)).Address(0, 0) & ")"
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Columns.AutoFit
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells((lr + 5),
> 1) = _ "Created " & Format(Date, "Medium Date") & " with
> Munch 1.5.12" strRange = "'[" &
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Parent.Name & _
> "]" & xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Name
> & "'!" & _
>
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range(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)).Range("A" & lr)
> xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(i + 1, 3)
> = _
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("B" & lr)
> xlapp.Workbooks(strXlsFile).Worksheets(1).Cells(i + 1, 4)
> = _
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("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(sn(i)).Range _
> (xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(2,
> 4), _
> xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(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)).ChartObjects.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(xlDown).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.ColorIndex = 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.NewSeries
> objSeries.Values = xlapp.Workbooks(strXlsFile).Worksheets _
> (sn(i)).Range(xlapp.Workbooks(strXlsFile).Worksheets _
> (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).MarkerBackgroundColorIndex = xlNone
>
> objChart.SeriesCollection(k).MarkerForegroundColorIndex = 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).MarkerBackgroundColorIndex = sm
> objChart.SeriesCollection(k).MarkerForegroundColorIndex = 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).Worksheets _
> (sn(i)).Range("C2:C" & lr)
> objChart.SeriesCollection(k).ErrorBars.Border.ColorIndex = eb
> objChart.SeriesCollection(k).ErrorBars.Border.Weight =
> xlThin
>
> objChart.SeriesCollection(k).ErrorBars.Border.LineStyle = 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