Copy to a Word Table

[Fev]

Hi
I am trying to write some vba code in Excel 2007 to paste a range into
a Word (either 2003 or 2007) table. My code so far, selects the
correct table and selects the top (blank) row in the table. I cannot
get the next part correct. I need to:
1. Insert the correct number of rows in the Word table to accommodate
the Excel data. I have used
noRows = Selection.Rows.Count
2. Select those rows that have been inserted and paste the data to
these rows. When doing this manually it seems to produce the best
results, rather than paste as a nested table etc.
Any suggestions of the correct code would be greeatly appreciated.
Thanks
Heather

 

[Fev]

I'm not an expert in Word VBA so I think part of the answer you need to
get from the word expert.  I agree with the method you are using.  Make
sure you copy and then use PasteSpecial into word using the correct word
property values in pastespecial.  to get the property value number go to
Word VBA help or add the reference to the excel VBA by using the menu
option in VBA Tools : Refernce : Microsoft Word XX.0 Object Library.

You can get the Word property names by going to object browser (VBA
menu View Object Browser) and typing into the binoculars box
pastespecial.  When adding the Refernce to word the list of values are
different. Irecently help somebody do something similar to Power Point
and using the standard excel values gave errors in 2007 and didn't give
errors in 2003.

In word select the exact size of the range youi are copying from excel.
In excel you only have to select the first location, in word it is
better to select the entire range.  I have had problems pasting into
word if I only select the first cell.  sometimes word puts all the excel
rows into one row of the word table.

Word is very tricky in VBA to select different areas.  I've tried a lot
of times and spend hours doing something I can do in excel in a couple
of seconds.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=171622

Microsoft Office Help

Hi I can do the programming in Word in VBA, that is quite easy, the
problem is that I need to write the code in Excel VBA to push the data
through to the Word table. Hence my positng on the Excel VBA forum.
The Word code would be along the lines of:
Selection.GoTo What:=wdGoToBookmark, Name:="CommTable"
Selection.InsertRowsAbove noRows
Selection.MoveDown Unit:=wdLine, Count:=noRows, Extend:=wdExtend
Selection.Paste
But to address the word table from Excel is where I'm stuck.
My code so far in Excel is:

Sub WordTable()
Dim i As Integer
Dim appWd As Word.Application
Dim mydoc As Document

ActiveDocument.Tables(2).Rows(1).Select

Thanks for the suggestions, but I still don't get what I need to
replace Selection. (I think that is the problem) with in Excel to get
this to work.
Thanks
Heather

 

[michdenis]

Hi,

You must add a reference to your projectVBA
"Microsoft Word xx.x Objects Librairy"

a way of doing :

'---------------------------------------
Sub test()
Dim Rg As Range
Dim Wd As Word.Application
Dim Dc As Document, C As Column
Dim T As Table, P As Row
Dim A As Integer, B As Integer
Dim Bb As Border

'Defined range to copy
With Worksheets("Sheets1")
Set Rg = .Range("A15")
End With

Set Wd = CreateObject("Word.Application")
Wd.Visible = True
Set Dc = Wd.Documents.Add

Set T = Dc.Tables.Add(Range:=Dc.Range, _
NumRows:=Rg.Rows.Count, _
NumColumns:=Rg.Columns.Count)

For A = 1 To Rg.Rows.Count
For B = 1 To Rg.Columns.Count
T.Cell(A, B).Range = Rg(A, B)
Next
Next

'To apply borders if necessary
With T
For Each C In .Range.Columns
C.Borders(wdBorderHorizontal).Visible = True
Next
For Each P In .Range.Rows
P.Borders(wdBorderVertical).Visible = True
Next
For A = -4 To -1
.Range.Borders(A) = True
Next
End With

End Sub
'---------------------------------------



"Fev" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Hi
I am trying to write some vba code in Excel 2007 to paste a range into
a Word (either 2003 or 2007) table. My code so far, selects the
correct table and selects the top (blank) row in the table. I cannot
get the next part correct. I need to:
1. Insert the correct number of rows in the Word table to accommodate
the Excel data. I have used
noRows = Selection.Rows.Count
2. Select those rows that have been inserted and paste the data to
these rows. When doing this manually it seems to produce the best
results, rather than paste as a nested table etc.
Any suggestions of the correct code would be greeatly appreciated.
Thanks
Heather

 

[macropod]

Hi Heather,

Here's a quick demo of how you can populate a table with data. One thing your draft code is missing is the column count - you'll
need that.

Sub TableDemo()
Dim IntRows, IntCols As Integer
Dim i, j As Integer
IntRows = 5
IntCols = 6
With ActiveDocument
.Tables.Add Range:=.Bookmarks("CommTable").Range, NumRows:=IntRows, NumColumns:=IntCols
With .Bookmarks("CommTable").Range.Tables(1)
For i = 1 To IntRows
For j = 1 To IntCols
.Cell(i, j).Range.Text = "Row: " & i & ", Column: " & j
Next
Next
End With
End With
End Sub

For what you're trying to do, I don't believe you need to copy the Excel data; just replicate it. In the above demo, I've supplied
both the row count and the column count and I've used these to populate the cells in place of the Excel data (this is, after all,
just a demo). Note that there is no need to select anything - simply point the code to the 'CommTable' bookmark's range.

Of course, if you're going to use a copy/paste operation, you could create the table (after copying the Excel data) via the
PasteSpecial method in Word:

Sub PasteDemo()
ActiveDocument.Bookmarks("CommTable").Range.PasteSpecial Link:=False, _
DataType:=wdPasteRTF, Placement:=wdInLine, DisplayAsIcon:=False
End Sub


--
Cheers
macropod
[Microsoft MVP - Word]

I'm not an expert in Word VBA so I think part of the answer you need to
get from the word expert. I agree with the method you are using. Make
sure you copy and then use PasteSpecial into word using the correct word
property values in pastespecial. to get the property value number go to
Word VBA help or add the reference to the excel VBA by using the menu
option in VBA Tools : Refernce : Microsoft Word XX.0 Object Library.

You can get the Word property names by going to object browser (VBA
menu View Object Browser) and typing into the binoculars box
pastespecial. When adding the Refernce to word the list of values are
different. Irecently help somebody do something similar to Power Point
and using the standard excel values gave errors in 2007 and didn't give
errors in 2003.

In word select the exact size of the range youi are copying from excel.
In excel you only have to select the first location, in word it is
better to select the entire range. I have had problems pasting into
word if I only select the first cell. sometimes word puts all the excel
rows into one row of the word table.

Word is very tricky in VBA to select different areas. I've tried a lot
of times and spend hours doing something I can do in excel in a couple
of seconds.

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=171622

Microsoft Office Help

Hi I can do the programming in Word in VBA, that is quite easy, the
problem is that I need to write the code in Excel VBA to push the data
through to the Word table. Hence my positng on the Excel VBA forum.
The Word code would be along the lines of:
Selection.GoTo What:=wdGoToBookmark, Name:="CommTable"
Selection.InsertRowsAbove noRows
Selection.MoveDown Unit:=wdLine, Count:=noRows, Extend:=wdExtend
Selection.Paste
But to address the word table from Excel is where I'm stuck.
My code so far in Excel is:

Sub WordTable()
Dim i As Integer
Dim appWd As Word.Application
Dim mydoc As Document

ActiveDocument.Tables(2).Rows(1).Select

Thanks for the suggestions, but I still don't get what I need to
replace Selection. (I think that is the problem) with in Excel to get
this to work.
Thanks
Heather

 

[macropod]

Hi joel,

word usually requires selection

Not so - not by a longshot!! Using selections is almost always avoidable and the preferred method is to work with ranges. All you
need to do is to specify them correctly, just as you do in Excel.


--
Cheers
macropod
[Microsoft MVP - Word]

Excel is much easy to work with than Word. In excel you don't need to
select the areas. becauwse you are owrking with two documents and word
usually requires selection I recommend defining the excel area before
opening up the word document. try this code


Sub WordTable()

Dim i As Integer
Dim appWd As Word.Application
Dim mydoc As document

FName = "c:\temp\joel.doc"

With ActiveWorkbook.Sheets("Sheet2")
noRows = .Range("A" & Rows.Count).End(xlUp).Row
noCols = .Cells(1, Columns.Count).End(xlToLeft).Column
Set CopyRange = .Range(.Range("A1"), .Cells(noRows, noCols))
End With

Set appWd = GetObject(FName)
appWd.visible = true
Set Mydocument = appWd.document

Selection.GoTo What:=wdGoToBookmark, Name:="CommTable"
Selection.InsertRowsAbove noRows
Selection.MoveDown Unit:=wdLine, Count:=noRows, Extend:=wdExtend
CopyRange.Copy
Selection.Paste


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=171622

Microsoft Office Help

 

[macropod]

Hi joel,

It's just a matter of properly defining which references you're using where. After all, depending on where you're at with your code,
Selection could refer to either an Excel range or a Word range and you need to be clear as to which Selection you're referring to.