pasting charts to new bookmarks in Word

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
 
J

Jon Peltier

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
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top