Working with a Range object

[LF]

Hello,

In a C++ COM add-in for Excel I need to do something with all the cells on a
worksheet. I saw that I can use Worksheet.UsedRange. However, this is a
read-only property. Does this mean that the range itself is read-only, or
the cells that are covered by this range (I hope the cells are writable)?

Then, assuming I've got a Range object, how do I iterate all the cells in it
an do my processing on each cell?

Regards and thanks,
Levente

 

[NickHK]

The UsedRange property is read-only, but the cells in it may be writable,
depending on protection/locked settings.

Dim Cell as range
for each cell in Activesheet.usedrange
debug.print cell.address
next

NickHK

 

[LF]

Nick,

Thanks fro confirming that the celss may be writable. However, this VB
sample is of no use for me. I need to iterate the cells using C++. I have an
Excel::_Worksheet interface pointer. Can you please explain what the VB
for_each gets translated into in this case? Thanks.

Best regards,
Levente

 

[NickHK]

You'd have to ask in a C++ group what that would be.
Or use
Dim i As Long

With ActiveSheet.UsedRange
For i = 1 To .Cells.Count
MsgBox .Item(i).Address
Next
End With

NickHK

 

[LF]

Nick,

The Workshhet->Cells property returns a Range object. And although
Range->Count returns the number of cells (I guess), I cannot simply Index
using Range->GetItem() because this methods wants a rox and a column index.
And I do not know from where to where are the cells included in the range.
Not to mention that a range object could contain multiple areas, each one
for an isle of cells.

Thus the question remains: how can I reliably iterate all the cells in a
Range object?

Regards and thanks,
Levente

 

[NickHK]

You can test the .Areas.Count property of the range.
But the UsedRange is always one 1 area although another may not be.
How you do this in C++ is up to you :

Private Sub CommandButton1_Click()
Dim r As Range
Dim i As Long
Dim j As Long
'Create a range of multiple areas
Set r = Union(Range("A1:B2"), Range("D4:F6"))

With r.Areas
For j = 1 To .Count
With .Item(j)
For i = 1 To .Cells.Count
With .Item(i)
.Select
MsgBox .Address
End With
Next
End With
Next
End With

End Sub

NickHK

 

[XL-Dennis]

Levente,

Iterating through range(s) can be rather time consuming and Excel offer
difference approaches depending on the purpose. What do You want to achieve
by iterating through all cells?

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[LF]

i need to adjust the formulas after copying the entire content of a workbook
from one xls file to another. appartently, the formulas of the cells that
were reffering to other sheets are messed up after a copy (using
Worksheet->Copy). i need to work it around.

 

[Peter T]

If by "messed-up" you mean after copying a sheet to another workbook you get
links to yet other sheets in the original workbook - but you want these to
refer to some sheet in the new workbook. You might be able to rectify
manually with

Edit > Replace (Ctrl-h)

Replace [originalBook.xls] with nothing, then perhaps replace the sheet name
that follows as appropriate.

Regards
Peter T

 

[LF]

OK, I managed to get it to work. Thanks.

Levente,

Iterating through range(s) can be rather time consuming and Excel offer
difference approaches depending on the purpose. What do You want to
achieve
by iterating through all cells?

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/

 

[LF]

Peter,

Yes, this is what I am referring to. But I need to do this programatically.
I managed to do it for cells. I do not know yet how to handle chart object.
Anybody knows how to access the data of the charts?

Regards,
Levente

If by "messed-up" you mean after copying a sheet to another workbook you
get
links to yet other sheets in the original workbook - but you want these to
refer to some sheet in the new workbook. You might be able to rectify
manually with

Edit > Replace (Ctrl-h)

Replace [originalBook.xls] with nothing, then perhaps replace the sheet
name
that follows as appropriate.

Regards
Peter T

i need to adjust the formulas after copying the entire content of a workbook
from one xls file to another. appartently, the formulas of the cells that
were reffering to other sheets are messed up after a copy (using
Worksheet->Copy). i need to work it around.

 

[Peter T]

Hi Levente,

Yes, this is what I am referring to. But I need to do this programatically.
I managed to do it for cells.

The manual Replace method can be done programmatically.

I do not know yet how to handle chart object.
Anybody knows how to access the data of the charts?

It might be simple or extremely difficult.

If you have similarly located data in the new wb, ie same sheet names and
source addresses, all you'd need to do is loop each series in each chart. In
sr.Formula replace "[LinkedBook.xls]" with "". Be aware Titles might be
linked though can be replaced with text, links in DataLabels is more tricky.

If this is not viable, if interested I have an addin that replaces source
data with named arrays (no links to cells at all) and/or "re-source" the
chart data to a new cell range (eg from linked book to chart-book). Contact
below.

Regards,
Peter T
pmbthornton gmail com

Peter,

Yes, this is what I am referring to. But I need to do this programatically.
I managed to do it for cells. I do not know yet how to handle chart object.
Anybody knows how to access the data of the charts?

Regards,
Levente

If by "messed-up" you mean after copying a sheet to another workbook you
get
links to yet other sheets in the original workbook - but you want these to
refer to some sheet in the new workbook. You might be able to rectify
manually with

Edit > Replace (Ctrl-h)

Replace [originalBook.xls] with nothing, then perhaps replace the sheet
name
that follows as appropriate.

Regards
Peter T

i need to adjust the formulas after copying the entire content of a workbook
from one xls file to another. appartently, the formulas of the cells that
were reffering to other sheets are messed up after a copy (using
Worksheet->Copy). i need to work it around.

<snip>

 

[LF]

Peter,

If you have similarly located data in the new wb, ie same sheet names and
source addresses, all you'd need to do is loop each series in each chart.
In
sr.Formula replace "[LinkedBook.xls]" with "".

Yes, my two workbooks are exactly the same (contain the same number of
sheets, namesdthe same, filled with the same values). I managed to iterate
all series in a chart and replace the reference to the source workbok (I am
copying from a source workbook to a destination, remember?) with "". I am
doing this for all embedded charts on sheets and also for chart sheets. This
works fine.

Be aware Titles might be
linked though can be replaced with text, links in DataLabels is more
tricky.

This I do not understand. Can you please explain? Do I have to update
anything else, except the series? I wan to make sure that the sheets I
copied over using Worksheet.Copy() and Chart.Copy() (the latter for chart
sheets) are no longer containing any reference to the source workbook.

Thanks a lot for the help,
Levente

Peter,

Yes, this is what I am referring to. But I need to do this programatically.
I managed to do it for cells. I do not know yet how to handle chart object.
Anybody knows how to access the data of the charts?

Regards,
Levente

If by "messed-up" you mean after copying a sheet to another workbook
you
get
links to yet other sheets in the original workbook - but you want these to
refer to some sheet in the new workbook. You might be able to rectify
manually with

Edit > Replace (Ctrl-h)

Replace [originalBook.xls] with nothing, then perhaps replace the sheet
name
that follows as appropriate.

Regards
Peter T

i need to adjust the formulas after copying the entire content of a
workbook
from one xls file to another. appartently, the formulas of the cells that
were reffering to other sheets are messed up after a copy (using
Worksheet->Copy). i need to work it around.

<snip>

 

[Peter T]

Be aware Titles might be

This I do not understand. Can you please explain? Do I have to update
anything else, except the series? I wan to make sure that the sheets I
copied over using Worksheet.Copy() and Chart.Copy() (the latter for chart
sheets) are no longer containing any reference to the source workbook.

Chart Title, up to 4 Axis titles and textboxes could have links if say user
had selected the title and in the Input bar typed "=" followed by a cell
reference. If '.hastitle' simply replace text with text. Datalabels can be
similarly linked but these are much more difficult to handle. Typically most
charts would have not such links.

Sounds like you've found and removed all your links but you could check with
something like this -

vLinks = myBook.LinkSources
If IsArray(vLinks) Then
For Each vlk In vLinks
Debug.Print vlk
Next
Debug.Print "no links"
End If

Regards,
Peter T

Peter,

If you have similarly located data in the new wb, ie same sheet names and
source addresses, all you'd need to do is loop each series in each chart.
In
sr.Formula replace "[LinkedBook.xls]" with "".

Yes, my two workbooks are exactly the same (contain the same number of
sheets, namesdthe same, filled with the same values). I managed to iterate
all series in a chart and replace the reference to the source workbok (I am
copying from a source workbook to a destination, remember?) with "". I am
doing this for all embedded charts on sheets and also for chart sheets. This
works fine.

Be aware Titles might be
linked though can be replaced with text, links in DataLabels is more
tricky.

This I do not understand. Can you please explain? Do I have to update
anything else, except the series? I wan to make sure that the sheets I
copied over using Worksheet.Copy() and Chart.Copy() (the latter for chart
sheets) are no longer containing any reference to the source workbook.

Thanks a lot for the help,
Levente

Peter,

Yes, this is what I am referring to. But I need to do this programatically.
I managed to do it for cells. I do not know yet how to handle chart object.
Anybody knows how to access the data of the charts?

Regards,
Levente

If by "messed-up" you mean after copying a sheet to another workbook
you
get
links to yet other sheets in the original workbook - but you want

these
to

refer to some sheet in the new workbook. You might be able to rectify
manually with

Edit > Replace (Ctrl-h)

Replace [originalBook.xls] with nothing, then perhaps replace the sheet
name
that follows as appropriate.

Regards
Peter T

i need to adjust the formulas after copying the entire content of a
workbook
from one xls file to another. appartently, the formulas of the cells that
were reffering to other sheets are messed up after a copy (using
Worksheet->Copy). i need to work it around.

<snip>

 

[Tosco]

Use Tools - Macro - Record new macro to record a macro while creating a
cart.
The macro will not be useful to create more charts, but will be useful
to you to understand the object model and what methods/properties to
use. Run it step by step and will be clearer.
It is confusing the way Excel works with Shapes and Charts, but you can
make it.
I don't have experience with C++, but I create graphs in VBA once in a
while.
Let me know if you need help.
Stefano

Peter,

Yes, this is what I am referring to. But I need to do this programatically.
I managed to do it for cells. I do not know yet how to handle chart object.
Anybody knows how to access the data of the charts?

Regards,
Levente

If by "messed-up" you mean after copying a sheet to another workbook you
get
links to yet other sheets in the original workbook - but you want these to
refer to some sheet in the new workbook. You might be able to rectify
manually with

Edit > Replace (Ctrl-h)

Replace [originalBook.xls] with nothing, then perhaps replace the sheet
name
that follows as appropriate.

Regards
Peter T

i need to adjust the formulas after copying the entire content of a workbook
from one xls file to another. appartently, the formulas of the cells that
were reffering to other sheets are messed up after a copy (using
Worksheet->Copy). i need to work it around.

Levente,

Iterating through range(s) can be rather time consuming and Excel offer
difference approaches depending on the purpose. What do You want to
achieve
by iterating through all cells?

---------------
With kind regards,
Dennis
Weekly Blog .NET & Excel: http://xldennis.wordpress.com/
My English site: http://www.excelkb.com/default.aspx
My Swedish site: http://www.xldennis.com/


:

You can test the .Areas.Count property of the range.
But the UsedRange is always one 1 area although another may not be.
How you do this in C++ is up to you :

Private Sub CommandButton1_Click()
Dim r As Range
Dim i As Long
Dim j As Long
'Create a range of multiple areas
Set r = Union(Range("A1:B2"), Range("D4:F6"))

With r.Areas
For j = 1 To .Count
With .Item(j)
For i = 1 To .Cells.Count
With .Item(i)
.Select
MsgBox .Address
End With
Next
End With
Next
End With

End Sub

NickHK

Nick,

The Workshhet->Cells property returns a Range object. And although
Range->Count returns the number of cells (I guess), I cannot simply
Index
using Range->GetItem() because this methods wants a rox and a column
index.
And I do not know from where to where are the cells included in the
range.
Not to mention that a range object could contain multiple areas,
each
one
for an isle of cells.

Thus the question remains: how can I reliably iterate all the cells in
a
Range object?

Regards and thanks,
Levente


You'd have to ask in a C++ group what that would be.
Or use
Dim i As Long

With ActiveSheet.UsedRange
For i = 1 To .Cells.Count
MsgBox .Item(i).Address
Next
End With

NickHK

Nick,

Thanks fro confirming that the celss may be writable. However, this
VB
sample is of no use for me. I need to iterate the cells using
C++. I
have
an
Excel::_Worksheet interface pointer. Can you please explain what the
VB
for_each gets translated into in this case? Thanks.

Best regards,
Levente


The UsedRange property is read-only, but the cells in it may be
writable,
depending on protection/locked settings.

Dim Cell as range
for each cell in Activesheet.usedrange
debug.print cell.address
next

NickHK

Hello,

In a C++ COM add-in for Excel I need to do something with all the
cells
on
a
worksheet. I saw that I can use Worksheet.UsedRange. However,
this
is
a
read-only property. Does this mean that the range itself is
read-only,
or
the cells that are covered by this range (I hope the cells are
writable)?

Then, assuming I've got a Range object, how do I iterate all the
cells
in
it
an do my processing on each cell?

Regards and thanks,
Levente