Using "Cells" causes Excel to not close properly

  • Thread starter Thread starter RzB
  • Start date Start date
R

RzB

I have read may posts about Excel application not
closing properly, and have been very careful to make
sure that my application uses fully qualified references
to Excel objects. That has worked fine so far....
However I would like to be able to use the Cells
property to refer to certain ranges. If I uses Cells
the Excel file will not close properly.

For example if I use this ....

Private m_rngCurrentRange As Excel.Range
Set m_rngCurrentRange = m_objXlSht.Range("C3").

m_rngCurrentRange.Range("B2", "C5").HorizontalAlignment = xlCenter

all is well.... However if I use....

m_rngCurrentRange.Range(Cells(2,2), Cells((3,5) ).HorizontalAlignment =
xlCenter

Then excel does not close properly...

How do I overcome this... I don't want to have to program
column letters at text!!!

Many thanks,
Roy
 
Hi Roy
[...]
m_rngCurrentRange.Range("B2", "C5").HorizontalAlignment = xlCenter
all is well.... However if I use....
m_rngCurrentRange.Range(Cells(2,2), Cells((3,5) ).HorizontalAlignment =
xlCenter
Then excel does not close properly...
Click to expand...
[...]
Click to expand...

Have you tried referencing a single cell and then resizing the
referenced range instead? E.g. something like the following?

m_rngCurrentRange.Cells(2,2).Resize(4,2).HorizontalAlignment = xlCenter

NB: The way you used the Cells-thing is actually not fully qualified.
A fully qualified version would need Cells not to stand alone, but
to include something like m_rngCurrentRange.Parent.Cells(...)
(where the The m_rngCurrentRange.Parent-part refers to the sheet)
or m_objXlSht.Cells(...).

Another NB: Cells(3,5) does not refer to "C5" but to "E3" ;-)


Cheers,

Martin
 
I imagine that would be because you have not qualified the references to the
excel Cells object. When you say: Cells(whatever) from within Excel VBA,
Excell automatically provides the default Activeworksheet reference (or
whatever it is - I don't have Excel here to check). So the Cells(whatever)
will actually default to ActiveWorksheet.Cells(whatever). But when you
automate Excel from Access, you can not expect that to happen. So, your
Cells() references have not been properly qualified. Try qualifying them
properly as: obExcel.ActiveWorksheet.... (or whatever it needs to be).

HTH,
TC
 
Martin & TC,
Many thanks for your help. Martin's Resize option
seems to have worked a treat.

This the exact code I'm using...

m_rngCurrentRange.Cells(2, 4) _
.Resize((lngLineCount), (lngNumOfMonths))_
.HorizontalAlignment = xlCenter

I need to investigate a little more because this
is not fully qualified (or is it in this case) and
it works OK...

Many thanks,
Roy
 
Hi Roy
m_rngCurrentRange.Cells(2, 4) _
.Resize((lngLineCount), (lngNumOfMonths))_
.HorizontalAlignment = xlCenter
Click to expand...

Try not to mix up range objects and the Cells-property,
since the Cells-property works relative to its range
object's upper left cell. In your case, if you initialized
your range object m_rngCurrentRange to e.g. cell
C3, using Cells(2,4) returns G5. So be careful!

For an absolute cell-reference (relative to A1), use
the Cells-property of your sheet-object or of the
parent of the range-object (parent returns a
reference to the sheet):

m_objXlSht.Cells(2, 4) _
.Resize(lngLineCount, lngNumOfMonths) _
.HorizontalAlignment = xlCenter

or:

m_rngCurrentRange.Parent.Cells(2, 4) _
.Resize(lngLineCount, lngNumOfMonths) _
.HorizontalAlignment = xlCenter


NB: the above examples *are* actually fully qualified.


Cheers,

Martin
 
But that line of code >is< fully qualified. There is no: "blank dot
something" syntax within it.

Cheers,
TC
 
Back
Top