Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming
|
|||
|
|||
Set XL range into Wrod bookmark as a table?
On Fri, 29 Feb 2008 10:55:09 -0800 (PST), Ed from AZ
wrote: 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/InterD...teBinding.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 -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#2
Posted to microsoft.public.word.vba.general,microsoft.public.excel.programming
|
|||
|
|||
Set XL range into Wrod bookmark as a table?
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*pm, Jay Freedman wrote: On Fri, 29 Feb 2008 10:55:09 -0800 (PST), Ed from AZ wrote: 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 (seehttp://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 -- Regards, Jay Freedman Microsoft Word MVP * * * *FAQ: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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing to a table in wrod from excel | Excel Programming | |||
Copy/Paste Excel Cell Range to Word Bookmark | Excel Programming | |||
How to use VBA read entries from a Wrod file into Excel | Excel Programming | |||
how do I create Business Cards with Office Wrod 2003? | Excel Discussion (Misc queries) | |||
Programmatically detect wrod-wrap? | Excel Programming |