Set XL range into Wrod bookmark as a table?

[Ed from AZ]

With Word and Excel 2003 - I am trying to automate setting a range of
Excel cells into a Word bookmark as a table. I used the code below.
The code ran all the way through - but absolutely nothing showed up in
Word!!

Help??!?

Ed


Sub XLtoWord()

Dim wd As Object 'Word.Application
Dim doc As Object 'Word.Document

Dim wkb As Workbook 'This workbook
Dim wks As Worksheet 'This workbook
Dim rng As Range 'This workbook

Dim x As Long

Set wkb = ActiveWorkbook
Set wks = wkb.Worksheets("PB Life for BW")
x = wks.Range("A100").End(xlUp).Row

Set wd = CreateObject("Word.Application")
Set doc = wd.Documents.Add
wd.Visible = True
doc.Paragraphs(1).Range.InsertAfter vbCrLf

doc.Bookmarks.Add Name:="XL1", Range:=doc.Paragraphs(1).Range
doc.Bookmarks.Add Name:="XL2", Range:=doc.Paragraphs(2).Range

Set rng = wks.Range("A2:H30")
doc.Bookmarks("XL1").Select
Selection = rng

Set rng = wks.Range("A31:H" & x)
doc.Bookmarks("XL2").Select
Selection = rng

Set doc = Nothing
Set wd = Nothing

End Sub

 

[Jay Freedman]

With Word and Excel 2003 - I am trying to automate setting a range of
Excel cells into a Word bookmark as a table. I used the code below.
The code ran all the way through - but absolutely nothing showed up in
Word!!

Help??!?

Ed


Sub XLtoWord()

Dim wd As Object 'Word.Application
Dim doc As Object 'Word.Document

Dim wkb As Workbook 'This workbook
Dim wks As Worksheet 'This workbook
Dim rng As Range 'This workbook

Dim x As Long

Set wkb = ActiveWorkbook
Set wks = wkb.Worksheets("PB Life for BW")
x = wks.Range("A100").End(xlUp).Row

Set wd = CreateObject("Word.Application")
Set doc = wd.Documents.Add
wd.Visible = True
doc.Paragraphs(1).Range.InsertAfter vbCrLf

doc.Bookmarks.Add Name:="XL1", Range:=doc.Paragraphs(1).Range
doc.Bookmarks.Add Name:="XL2", Range:=doc.Paragraphs(2).Range

Set rng = wks.Range("A2:H30")
doc.Bookmarks("XL1").Select
Selection = rng

Set rng = wks.Range("A31:H" & x)
doc.Bookmarks("XL2").Select
Selection = rng

Set doc = Nothing
Set wd = Nothing

End Sub

Hi Ed,

This one's kind of gnarly -- all sorts of problems. The main one, the reason you
got no results, is that the statements

Selection = rng

are meaningless, or at best they don't do what you think. Since the "Selection"
isn't qualified in any way, it refers to the Selection in Excel, not in Word.

In any case, I don't think it's possible to do what you want just by assigning
some range or object in Excel to a range in Word; they don't contain the same
kinds of data. Although you can transfer the FormattedText member of one Word
range into the FormattedText member of another Word range, you can't do that
from Excel to Word.

However, the Windows clipboard has the smarts needed to convert in both
directions. The modified version of your code below works for me.

One other thing: If you go into the Tools menu of the VBA editor, click
References, and check the box for the Microsoft Word Object Library, you can use
the Word.Application, Word.Document, and Word.Range objects (see
http://www.word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm).

Once you get the copy/paste working, you find that setting the bookmarks to
include the entire paragraph causes a problem. When the Excel data replaces the
XL1 bookmark, the bookmark itself is deleted and the XL2 bookmark winds up in
the first cell of the table. Then the second set of data gets pasted in _before_
the first set. :-( The cure is to collapse the Word range used to create each of
the bookmarks.

Sub XLtoWord()

Dim wd As Word.Application
Dim doc As Word.Document

Dim wkb As Workbook 'This workbook
Dim wks As Worksheet 'This workbook
Dim rng As Excel.Range 'This workbook
Dim wdRng As Word.Range

Dim x As Long

Set wkb = ActiveWorkbook
Set wks = wkb.Worksheets("PB Life for BW")
x = wks.Range("A100").End(xlUp).Row

Set wd = CreateObject("Word.Application")
Set doc = wd.Documents.Add
wd.Visible = True
doc.Paragraphs(1).Range.InsertAfter vbCrLf

Set wdRng = doc.Paragraphs(1).Range
wdRng.Collapse wdCollapseStart
doc.Bookmarks.Add Name:="XL1", Range:=wdRng

Set wdRng = doc.Paragraphs(2).Range
wdRng.Collapse wdCollapseStart
doc.Bookmarks.Add Name:="XL2", Range:=wdRng

Set rng = wks.Range("A2:H30")
rng.Copy
doc.Bookmarks("XL1").Range.Paste

Set rng = wks.Range("A31:H" & x)
rng.Copy
doc.Bookmarks("XL2").Range.Paste

Set doc = Nothing
Set wd = Nothing

End Sub

 

[Ed from AZ]

Jay, that was fantastic!! I forgot about qualifying the Selection.
And the bookmark range would have puzzled me for days!

Thank you so much.
Ed