Excel VBA - How to enter a reference to another sheet?

I'm kind of green with Excel - this may well be an elementary issue...

I add a series of worksheets to a new workbook with automation from Access.
But the first worksheet in the workbook is a summary worksheet and simply
displays data that's already on the other worksheets - so I figure I can
just point to the cells in the other worksheets with a formula to get those
values.

Here's what I'm using as formulas:

strLastTest = "=" & wksName & "!A" & lr
strAverage = "=" & wksName & "!B" & lr
strStdDev = "=" & wksName & "!C" & lr
(lr is a variable that holds the last row value)

Here's how I'm trying to insert the formulas:

xlapp.Workbooks(strXlsFile).Worksheets("Summary").Cells(i + 1, 2).Formula =
strLastTest
xlapp.Workbooks(strXlsFile).Worksheets("Summary").Cells(i + 1, 3).Formula =
strAverage
xlapp.Workbooks(strXlsFile).Worksheets("Summary").Cells(i + 1, 4).Formula =
strStdDev
(This code is in a loop so the i + 1 just adds the next summary data in the
next row)

I keep getting: Error Number1004: Application-defined or object-defined
error

I'm not sure if there's something wrong with my code (probably) or if the
problem is due to the fact that "wksName & "!B & lr" (and the others) is a
cell that gets its value from a formula. Could this cause the problem? Am
I referencing the cells on the other worksheets correctly?

Thanks in advance.

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.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"deko" <deko@deko.com> wrote in message
news:cTp8e.1$Xb4.0@newssvr21.news.prodigy.com...
> I'm kind of green with Excel - this may well be an elementary issue...
>
> I add a series of worksheets to a new workbook with automation from
Access.
> But the first worksheet in the workbook is a summary worksheet and simply
> displays data that's already on the other worksheets - so I figure I can
> just point to the cells in the other worksheets with a formula to get
those
> values.
>
> Here's what I'm using as formulas:
>
> strLastTest = "=" & wksName & "!A" & lr
> strAverage = "=" & wksName & "!B" & lr
> strStdDev = "=" & wksName & "!C" & lr
> (lr is a variable that holds the last row value)
>
> Here's how I'm trying to insert the formulas:
>
> xlapp.Workbooks(strXlsFile).Worksheets("Summary").Cells(i + 1, 2).Formula
=
> strLastTest
> xlapp.Workbooks(strXlsFile).Worksheets("Summary").Cells(i + 1, 3).Formula
=
> strAverage
> xlapp.Workbooks(strXlsFile).Worksheets("Summary").Cells(i + 1, 4).Formula
=
> strStdDev
> (This code is in a loop so the i + 1 just adds the next summary data in
the
> next row)
>
> I keep getting: Error Number1004: Application-defined or object-defined
> error
>
> I'm not sure if there's something wrong with my code (probably) or if the
> problem is due to the fact that "wksName & "!B & lr" (and the others) is a
> cell that gets its value from a formula. Could this cause the problem?
Am
> I referencing the cells on the other worksheets correctly?
>
> Thanks in advance.
>
>

> 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.HorizontalA
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).Worksheets
_
(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).Worksheets
_
(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.HorizontalAlign
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

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

> 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.

Thanks for the tip. I've tried to take every precaution to avoid a
lingering instance of Excel after Access quits. I don't use any with/end
with or object variables - except when creating a new chart or series:

Dim objChart as Object
Set objChart = xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).ChartObjects.Add _
(Left:=100, Top:=24, Width:=650, Height:=500).Chart

Dim objSeries as Object
Set objSeries = xlapp.Workbooks _
(strXlsFile).Worksheets(sn(i)).ChartObjects _
(1).Chart.SeriesCollection.NewSeries
....
On Error Resume Next
xlapp.Quit
Set xlapp = Nothing
Set sn = Nothing
Set db = Nothing
Set objSeries = Nothing
Set objChart = Nothing
Exit Function

Then, just to be sure, I call CleanUp("Excel.exe")

Public Function CleanUp(procName As String)
On Error Resume Next
Dim objProcList As Object
Dim objWMI As Object
Dim objProc As Object
'create WMI object instance
Set objWMI = GetObject("winmgmts:")
If Not IsNull(objWMI) Then
'create object collection of Win32 processes
Set objProcList = objWMI.InstancesOf("win32_process")
For Each objProc In objProcList 'iterate through enumerated
collection
If UCase(objProc.Name) = UCase(procName) Then
objProc.Terminate (0)
End If
Next
End If
Set objProcList = Nothing
Set objWMI = Nothing
End Function

You set objects to nothing in the WRONG sequence.

objSeries holds a reference to xlApp,
thus should be set to nothing first,
xlApp (being highest in the hierarchy) s/b last

I'd NEVER terminate an instance like that.

If the user was working in Excel when your code started he will not
appreciate you crashing his work.
However If you are sure nobody works on that console, then fine




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


deko wrote :

> > 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.
>
> Thanks for the tip. I've tried to take every precaution to avoid a
> lingering instance of Excel after Access quits. I don't use any
> with/end with or object variables - except when creating a new chart
> or series:
>
> Dim objChart as Object
> Set objChart = xlapp.Workbooks _
> (strXlsFile).Worksheets(sn(i)).ChartObjects.Add _
> (Left:=100, Top:=24, Width:=650, Height:=500).Chart
>
> Dim objSeries as Object
> Set objSeries = xlapp.Workbooks _
> (strXlsFile).Worksheets(sn(i)).ChartObjects _
> (1).Chart.SeriesCollection.NewSeries
> ...
> On Error Resume Next
> xlapp.Quit
> Set xlapp = Nothing
> Set sn = Nothing
> Set db = Nothing
> Set objSeries = Nothing
> Set objChart = Nothing
> Exit Function
>
> Then, just to be sure, I call CleanUp("Excel.exe")
>
> Public Function CleanUp(procName As String)
> On Error Resume Next
> Dim objProcList As Object
> Dim objWMI As Object
> Dim objProc As Object
> 'create WMI object instance
> Set objWMI = GetObject("winmgmts:")
> If Not IsNull(objWMI) Then
> 'create object collection of Win32 processes
> Set objProcList = objWMI.InstancesOf("win32_process")
> For Each objProc In objProcList 'iterate through enumerated
> collection
> If UCase(objProc.Name) = UCase(procName) Then
> objProc.Terminate (0)
> End If
> Next
> End If
> Set objProcList = Nothing
> Set objWMI = Nothing
> End Function

"deko" <deko@deko.com> wrote in message
news:ovr8e.7$Xb4.6@newssvr21.news.prodigy.com...
>
> 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)
>

One problem with this is that you are now setting the cell to a value, not a
formula, so if the underlying values get updated, your summary sheet does
not.

> One problem with this is that you are now setting the cell to a value, not
a
> formula, so if the underlying values get updated, your summary sheet does
> not.

As long as it's accurate, that's all that matters. No data gets input -
they're just reports with graphs. The whole thing could be done in Access,
but the company doesn't allow Access on employee's workstations. So there's
an "operator" that runs Access to generate the worksheets, then they get
emailed around. These folks are still on NT ...

"deko" <deko@deko.com> wrote in message
news:cLz8e.103$Xb4.93@newssvr21.news.prodigy.com...
> > One problem with this is that you are now setting the cell to a value,
not
> a
> > formula, so if the underlying values get updated, your summary sheet
does
> > not.
>
> As long as it's accurate, that's all that matters. No data gets input -
> they're just reports with graphs. The whole thing could be done in
Access,
> but the company doesn't allow Access on employee's workstations.

Okay ... I think you have mentioned that before.

> So there's
> an "operator" that runs Access to generate the worksheets, then they get
> emailed around. These folks are still on NT ...

Nothing wrong with NT, we still use it at work. XP has no big advantages
that I can see, the security holes are as big and as many, the stability may
be a bit better, but NT is not bad. The main disadvantage is there products
are appearing that demand XP now, so even we have plans to upgrade.