K
Kate
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
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