Hello, I have charts in Excel that I'm pasting into a Word
template that has preexisting bookmarks.
The bookmarks are named for the section of the report they are in.

For example, in section E, they are name E1, E2, E3, etc.

Several charts exist for each bookmark; e.g., E1a, E1b, E1c.
These bookmarks do NOT exist in the template.

What I'm doing is locating bookmark E1, and then inserting a new
bookmark above it for each of the charts that are subject to the
bookmark. This is the code:


For Each s In xlbook.Application.Charts
s.Activate
'extract name of Word bookmark from chart name
strBkmk = Left(s.Name, Len(s.Name) - 3) 'this would
be the E1 or E2 etc
s.ChartArea.Copy

If .ActiveDocument.Bookmarks.Exists(strBkmk) Then
With .Selection
.Goto What:=wdGoToBookmark, Name:=strBkmk
'move up to the next paragraph mark, pre-set
in the template, and insert another
.MoveUp Unit:=wdLine, Count:=1
.TypeParagraph
.MoveUp Unit:=wdLine, Count:=1
End With
'insert a new bookmark for that parameter's
production category
.ActiveDocument.Bookmarks.Add s.Name 'this
would be E1a or E1b etc.
.Selection.Collapse (wdCollapseStart)
'paste chart at bookmark, special, to save space
.Selection.PasteAndFormat (wdChartPicture)
Else
MsgBox "Bookmark for " & strBkmk & " does not
exist." & vbCr _
& "Fatal error: exiting program."
.Visible = True
GoTo xl_quit
End If
Next s


That's the snippet. What's happening is, it works fine
sometimes, and then other times one of the newly-created
bookmarks (E2c, perhaps) will end up at the end of the section
rather than grouped with the other E2 bookmarks.

This is what I'll end up with:

Section E
E1a
E1b
E1c
E2a
E2b
E3a
E3b
E3c
E2c

This causes me to have to manually cut the pasted chart from its
wrong placement and insert it with the group to which it belongs.
What the ##$$%$%. There is no rhyme or reason as to why this
happens. I've stepped through the code and can never catch it
misbehaving in the act.

Does anyone have any insight on this aberrant behavior??

Thank you,
Kate

In some cases your MoveUp may be moving in front of the last pasted chart,
so the next chart is going in out of order. This manipulation of bookmarks
can cause issues. Instead of moving up one line, what about moving left one
character?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
Hello, I have charts in Excel that I'm pasting into a Word template that
has preexisting bookmarks.
The bookmarks are named for the section of the report they are in.

For example, in section E, they are name E1, E2, E3, etc.

Several charts exist for each bookmark; e.g., E1a, E1b, E1c. These
bookmarks do NOT exist in the template.

What I'm doing is locating bookmark E1, and then inserting a new bookmark
above it for each of the charts that are subject to the bookmark. This is
the code:


For Each s In xlbook.Application.Charts
s.Activate
'extract name of Word bookmark from chart name
strBkmk = Left(s.Name, Len(s.Name) - 3) 'this would be the E1
or E2 etc
s.ChartArea.Copy

If .ActiveDocument.Bookmarks.Exists(strBkmk) Then
With .Selection
.Goto What:=wdGoToBookmark, Name:=strBkmk
'move up to the next paragraph mark, pre-set in the
template, and insert another
.MoveUp Unit:=wdLine, Count:=1
.TypeParagraph
.MoveUp Unit:=wdLine, Count:=1
End With
'insert a new bookmark for that parameter's production
category
.ActiveDocument.Bookmarks.Add s.Name 'this would be E1a or
E1b etc.
.Selection.Collapse (wdCollapseStart)
'paste chart at bookmark, special, to save space
.Selection.PasteAndFormat (wdChartPicture)
Else
MsgBox "Bookmark for " & strBkmk & " does not exist." &
vbCr _
& "Fatal error: exiting program."
.Visible = True
GoTo xl_quit
End If
Next s


That's the snippet. What's happening is, it works fine sometimes, and
then other times one of the newly-created bookmarks (E2c, perhaps) will
end up at the end of the section rather than grouped with the other E2
bookmarks.

This is what I'll end up with:

Section E
E1a
E1b
E1c
E2a
E2b
E3a
E3b
E3c
E2c

This causes me to have to manually cut the pasted chart from its wrong
placement and insert it with the group to which it belongs. What the
##$$%$%. There is no rhyme or reason as to why this happens. I've
stepped through the code and can never catch it misbehaving in the act.

Does anyone have any insight on this aberrant behavior??

Thank you,
Kate

Damn, you're good! Right before using moveleft, I collapsed the
selection to
the start, so that I wouldn't insert the new bookmark inside of
the old one.
I ran an entire report, and it seems to work!

I love you! -Kate

Jon Peltier wrote:
In some cases your MoveUp may be moving in front of the last pasted chart,
so the next chart is going in out of order. This manipulation of bookmarks
can cause issues. Instead of moving up one line, what about moving left one
character?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Kate" wrote in message
...
Hello, I have charts in Excel that I'm pasting into a Word template that
has preexisting bookmarks.
The bookmarks are named for the section of the report they are in.

For example, in section E, they are name E1, E2, E3, etc.

Several charts exist for each bookmark; e.g., E1a, E1b, E1c. These
bookmarks do NOT exist in the template.

What I'm doing is locating bookmark E1, and then inserting a new bookmark
above it for each of the charts that are subject to the bookmark. This is
the code:


For Each s In xlbook.Application.Charts
s.Activate
'extract name of Word bookmark from chart name
strBkmk = Left(s.Name, Len(s.Name) - 3) 'this would be the E1
or E2 etc
s.ChartArea.Copy

If .ActiveDocument.Bookmarks.Exists(strBkmk) Then
With .Selection
.Goto What:=wdGoToBookmark, Name:=strBkmk
'move up to the next paragraph mark, pre-set in the
template, and insert another
.MoveUp Unit:=wdLine, Count:=1
.TypeParagraph
.MoveUp Unit:=wdLine, Count:=1
End With
'insert a new bookmark for that parameter's production
category
.ActiveDocument.Bookmarks.Add s.Name 'this would be E1a or
E1b etc.
.Selection.Collapse (wdCollapseStart)
'paste chart at bookmark, special, to save space
.Selection.PasteAndFormat (wdChartPicture)
Else
MsgBox "Bookmark for " & strBkmk & " does not exist." &
vbCr _
& "Fatal error: exiting program."
.Visible = True
GoTo xl_quit
End If
Next s


That's the snippet. What's happening is, it works fine sometimes, and
then other times one of the newly-created bookmarks (E2c, perhaps) will
end up at the end of the section rather than grouped with the other E2
bookmarks.

This is what I'll end up with:

Section E
E1a
E1b
E1c
E2a
E2b
E3a
E3b
E3c
E2c

This causes me to have to manually cut the pasted chart from its wrong
placement and insert it with the group to which it belongs. What the
##$$%$%. There is no rhyme or reason as to why this happens. I've
stepped through the code and can never catch it misbehaving in the act.

Does anyone have any insight on this aberrant behavior??

Thank you,
Kate