Working with a Range object
 

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

Working with a Range object
 

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

Working with a Range object
 

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

Working with a Range object
 

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

Working with a Range object
 

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

Working with a Range object
 

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

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

Working with a Range object
 

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/


"NickHK" wrote:

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

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

Working with a Range object
 

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.

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


"NickHK" wrote:

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

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

Working with a Range object
 

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

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


"NickHK" wrote:

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

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

Working with a Range object
 

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

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


"NickHK" wrote:

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

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

Working with a Range object
 

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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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.

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


"NickHK" wrote:

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

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

Working with a Range object
 

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


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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

Working with a Range object
 

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


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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

Working with a Range object
 

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.

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

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


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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

Working with a Range object
 

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

LF wrote:
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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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.

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


"NickHK" wrote:

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

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