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

D

deko

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

Bob Phillips

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)
 
D

deko

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.
Click to expand...

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
 
K

keepITcool

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.
Click to expand...

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
Click to expand...
 
B

Bob Phillips

deko said:
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)
Click to expand...

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

deko

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.
Click to expand...

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

deko

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.
Click to expand...

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
 
K

keepITcool

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 :
 
B

Bob Phillips

deko said:
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.
Click to expand...

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 ...
Click to expand...

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

deko

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
Click to expand...

I didn't realize the order made a difference. Thanks for the tip
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
Click to expand...

Yeah, it's a big hammer. But somewhere in the help file, in very small
type, it DOES say to close Excel before running this... Actually, the hammer
only runs if the function does not complete successfully (returns False).
If the function returns True, then I rely on xlapp.Quit to close the
instance. The risk of leaving open Excel instances is too great - it
butters up not only my app but others...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Application.Min - how to ignore zero value? 7
How to set conditional format with vba? 12
Run time error Method 'Range' of Object '_Worksheet' Failed 5
Custom Fill in Listbox 2
Tried and true copy code lines fail me here 7
How to populate Excel Range from Access RecordSet? 10
Formatting and sorting cells with VBA 7

Top