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" wrote in message
...
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)).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.HorizontalA
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).Worksheets
_
(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).Worksheets
_
(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.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(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

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

"deko" wrote in message
...

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)


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

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" wrote in message
.. .
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.