microsoft.public.word.vba.general

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


On Feb 29, 6:23=A0pm, Jay Freedman wrote:
> On Fri, 29 Feb 2008 10:55:09 -0800 (PST), Ed from AZ m>
> wrote:
>
>
>
>
>
> >With Word and Excel 2003 - I am trying to automate setting a range of
> >Excel cells into a Word bookmark as a table. =A0I 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 =A0 =A0'Word.Application
> >Dim doc As Object =A0 'Word.Document
>
> >Dim wkb As Workbook =A0'This workbook
> >Dim wks As Worksheet 'This workbook
> >Dim rng As Range =A0 =A0 'This workbook
>
> >Dim x As Long
>
> >Set wkb =3D ActiveWorkbook
> >Set wks =3D wkb.Worksheets("PB Life for BW")
> >x =3D wks.Range("A100").End(xlUp).Row
>
> >Set wd =3D CreateObject("Word.Application")
> >Set doc =3D wd.Documents.Add
> >wd.Visible =3D True
> >doc.Paragraphs(1).Range.InsertAfter vbCrLf
>
> >doc.Bookmarks.Add Name:=3D"XL1", Range:=3Ddoc.Paragraphs(1).Range
> >doc.Bookmarks.Add Name:=3D"XL2", Range:=3Ddoc.Paragraphs(2).Range
>
> >Set rng =3D wks.Range("A2:H30")
> >doc.Bookmarks("XL1").Select
> >Selection =3D rng
>
> >Set rng =3D wks.Range("A31:H" & x)
> >doc.Bookmarks("XL2").Select
> >Selection =3D rng
>
> >Set doc =3D Nothing
> >Set wd =3D Nothing
>
> >End Sub
>
> Hi Ed,
>
> This one's kind of gnarly -- all sorts of problems. The main one, the reas=
on you
> got no results, is that the statements
>
> =A0 Selection =3D rng
>
> are meaningless, or at best they don't do what you think. Since the "Selec=
tion"
> isn't qualified in any way, it refers to the Selection in Excel, not in Wo=
rd.
>
> In any case, I don't think it's possible to do what you want just by assig=
ning
> some range or object in Excel to a range in Word; they don't contain the s=
ame
> kinds of data. Although you can transfer the FormattedText member of one W=
ord
> range into the FormattedText member of another Word range, you can't do th=
at
> 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 c=
an use
> the Word.Application, Word.Document, and Word.Range objects (seehttp://www=
.word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm).
>
> Once you get the copy/paste working, you find that setting the bookmarks t=
o
> include the entire paragraph causes a problem. When the Excel data replace=
s 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 _b=
efore_
> the first set. :-( The cure is to collapse the Word range used to create e=
ach of
> the bookmarks.
>
> Sub XLtoWord()
>
> Dim wd As Word.Application
> Dim doc As Word.Document
>
> Dim wkb As Workbook =A0'This workbook
> Dim wks As Worksheet 'This workbook
> Dim rng As Excel.Range =A0 =A0 'This workbook
> Dim wdRng As Word.Range
>
> Dim x As Long
>
> Set wkb =3D ActiveWorkbook
> Set wks =3D wkb.Worksheets("PB Life for BW")
> x =3D wks.Range("A100").End(xlUp).Row
>
> Set wd =3D CreateObject("Word.Application")
> Set doc =3D wd.Documents.Add
> wd.Visible =3D True
> doc.Paragraphs(1).Range.InsertAfter vbCrLf
>
> Set wdRng =3D doc.Paragraphs(1).Range
> wdRng.Collapse wdCollapseStart
> doc.Bookmarks.Add Name:=3D"XL1", Range:=3DwdRng
>
> Set wdRng =3D doc.Paragraphs(2).Range
> wdRng.Collapse wdCollapseStart
> doc.Bookmarks.Add Name:=3D"XL2", Range:=3DwdRng
>
> Set rng =3D wks.Range("A2:H30")
> rng.Copy
> doc.Bookmarks("XL1").Range.Paste
>
> Set rng =3D wks.Range("A31:H" & x)
> rng.Copy
> doc.Bookmarks("XL2").Range.Paste
>
> Set doc =3D Nothing
> Set wd =3D Nothing
>
> End Sub
>
> --
> Regards,
> Jay Freedman
> Microsoft Word MVP =A0 =A0 =A0 =A0FAQ:http://word.mvps.org
> Email cannot be acknowledged; please post all follow-ups to the newsgroup =
so all may benefit.- Hide quoted text -
>
> - Show quoted text -