Pasting Tables from Excel to Word

[Hari Prasadh]

Hi,

I have to paste lot of tabulated data from excel to word. I have programmed
in Excel and pasted the code below.

Basically I look for the Word "Table " in Excel and copy the range till the
next instance of the word "Table "

Then I go to Winword and paste it. After pasting the present table I have to
do following 2 additional things in Word:-
A) I have to go to the end of the document - The excel code -->
SDocument.Range.EndOf Unit:=wdStory, Extend:=wdExtend --> doesnt make the
cursor go to the end of the word document (Neither do I get any error). What
would be the correct code to make cursor go to end of Word document?

B) I have to then insert a Page Break after the present table- The Excel
code --> SDocument.Range.InsertBreak Type:=wdSectionBreakNextPage -->
strangely clears the freshly pasted data. I have also tried the code
SDocument.Range.InsertBreak Type:=wdSectionBreakNextPage but this created a
page break ABOVE the freshly pasted data. How to create a page break after
the presently pasted data?

After doing the above the control goes back to excel and the process keeps
on repeating till there are no more words.

I have one more issue which is after pasting the Column Widths of table in
Word are thoroughly different as compared to what I have in Excel. The whole
table shoots off the right side of word margin. I have close to 26 columns
in excel and before pasting I reduce the widths and then copy but even then
in word it gets expanded. What is the syntax for controlling the column
width of a freshly pasted table?

Thanks a lot,
Hari
India

Option Explicit
Dim TableCount As Integer
Public SWinword As Word.Application
Public SDocument As Word.Document

Sub ExcelRangeProcess()

Dim TableStartRow As Long
Dim TableEndRow As Long
Dim EndColumnLetter As String
Dim MaxNumberofTable As Integer
Dim LastRow As Long
Dim CheckFortable As Boolean

LastRow = Range("A65536").End(xlUp).Row

'In my case EndColumnLetter is Z.
EndColumnLetter = InputBox("Enter the last Column LETTER in your PRESENT
Banner", "Banner Column")


In my case number of tables would be around 150.
MaxNumberofTable = InputBox("Enter the Number of Tables in your PRESENT
Banner")

Range("A1" & ":" & EndColumnLetter & LastRow).Select

Selection.Find(what:="TABLE ", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

TableStartRow = ActiveCell.Row + 10

For TableCount = 1 To MaxNumberofTable

Range("A" & TableStartRow & ":" & EndColumnLetter & LastRow).Select

Selection.Find(what:="TABLE ", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate

If Left(ActiveCell.Value, 6) = "TABLE " Then
CheckFortable = True
Else
CheckFortable = False
End If


While CheckFortable <> True

Range("A" & ActiveCell.Row + 1 & ":" & EndColumnLetter & LastRow).Select

Selection.Find(what:="TABLE ", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=True, SearchFormat:=False).Activate

If Left(ActiveCell.Value, 6) = "TABLE " Then CheckFortable = True

Wend


TableEndRow = ActiveCell.Row - 6

Range("A" & TableStartRow & ":" & EndColumnLetter & TableEndRow).Select
Selection.Copy
Call TransferToWord

TableStartRow = TableEndRow + 16

Next TableCount

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Calling
WORD
application'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub TransferToWord()

If TableCount = 1 Then

Set SWinword = CreateObject("Word.application")
SWinword.Visible = msoTrue
Set SDocument = SWinword.Documents.Add

Else

Set SWinword = GetObject(, "Word.application")

End If


SDocument.Range.PasteAndFormat (wdPasteDefault)

SDocument.Range.EndOf Unit:=wdStory, Extend:=wdExtend

SDocument.Range.InsertBreak Type:=wdSectionBreakNextPage

End Sub

 

[Ed]

Hari:
(A) Try SDocument.Bookmarks("\EndOfDoc").Select
(B) If all you want is a new page and not a new section, try
Selection.InsertBreak Type:=wdPageBreak
(I used Selection rather than Range with the assumption that your insertion
point is at the end of the document.)

The column width behavior is hard to deal with because Word wants to
automatically format a lot of your table stuff for you. You can turn it off
when a table is created, but that's after the fact when you paste. I can
see two possible considerations.

If you do not need the table in Word to be an actual table, then you can
paste the Excel table in as a Picture (see Word VBA Help for PasteSpecial,
or in the Excel code you can use the CopyPicture method instead of Copy and
then Paste in Word). This inserts the Excel range as a graphic, which means
it can not be edited nor can it be searched for text. It will, however, fit
your page and retain the look of your Excel file.

If, however, you do need the Word table to be text rather than graphic, then
you are going to need to make sure your Word document is as much the same
size as your Excel page. Do a Print Preview in Excel - what are your page
margins? are you landscape or portrait? do you have a scaling factor or
percentage? what about your font and size? Also, Word adds two characters
in every cell as a marker - these can add an extra bit of width.

HTH
Ed

 

[Hari Prasadh]

Ed,

Thanks a lot for your post. Bookmarks("\EndOfDoc"). has been very helpful.

Since am coding in excel to control Word - Selection.InsertBreak
Type:=wdPageBreak - yields an error, because it is probably trying to mimic
this action in Excel itself. If I change it to
Sdocument.Selection.InsertBreak Type:=wdPageBreak - then I get run-time
error '438' - object doesnt support this property or method. I also tried -
SDocument.Range.InsertBreak Type:=wdPageBreak - but this causes the freshly
pasted table to disappear (and a new page does get inserted). What is the
correct syntax for inserting a page so that existing data doesnt get lost.

Also, I dont understand a to what is the difference between selection and
range in Word. In word If I record a macro for let's say the action of
pressing enter I get code as - Selection.TypeParagraph - Now, if in excel I
set a reference to Word and write - Sdocument. - then after the period the
intelli sense doesnt offer Selection but offers Select only. I thought may
be when coding through another environment Range may be the equivalent of
Selection. But using - Sdocument.Range. - Type paragraph is not a option
offered by intelli sense.

Why is that when coding in excel and trying to control word, the word
syntaxes dont work when pasted directly from word VBA to excel VBA (after
appending Sdocument. in the starting of each code)?? I have noticed the same
thing when I write code in excel to control PPT or vice-versa

Regards my problem of controlling column widths I think I have probably
found a workaround for the same. I will save each table of Excel (by putting
borders in it) as a HTML file then open the same in word and this seems to
have the same column widths as in excel. Im trying to automate this process
presently and hope to do it.

Thanks a lot,
Hari
India

 

[Ed]

Hari:

One of the difficulties of controlling one program through another program's
VBA is getting the references set correctly. In an Excel macro which
controls Word, "Range" could refer to either an Excel or a Word range,
depending on how it's declared. "Dim oRange As Range" in an Excel macro is
going be assumed to be an Excel range. To make it a range in your Word
document, it must be declared as "Dim oRange As Word.Range".

Unfortunately, I am not all that expert, and usually have to stumble through
my mistakes to catch these things. And occasionally I inflict these
mistakes on others, too! Sorry. Apparently, Selection *almost always*
assumes itself to belong to the parent application - in this case, Excel.

That's not a bad thing, really. Range is much preferred over Selection.
Selection refers to what the insertion point has selected. If a few letters
or an entire paragraph is selected, then that is "Selection". Any actions
taken on the Selection object will be performed on whatever is selected.
When the Selection is only a single insertion point, some actions are not
available, but that is still Selection.

Range is good because the insertion point doesn't move. It helps speed
things up considerably. And it is much easier to "put a handle on" through
another program, allowing you to avoid "automatic assumptions" by the parent
program.

Word has a built-in range called "Content". So if we declare a Word range
and set it to the document's Content range, we can use Range methods and
properties to do what we need. Collapse will take us to either end of the
range, depending on which direction we choose. Then one of the Insert
methods can be used.

Try this:
' Range is specific to the program
Dim oRange as Word.Range
' String is a string is a string
Dim strMsg As String
strMsg = "Hello!"

' "doc" is used to refer to the
' Word document object you are using
Set oRange = doc.Content
' oRange equals the entire scope of the doc
oRange .Collapse wdCollapseEnd
' oRange now equals only the
' insertion point at the end of the doc
oRange .InsertBreak Type:=wdPageBreak
' Since we've increased the content of the doc,
' we need to reset oRange to encompass it all
Set oRange = doc.Content
oRange .Collapse wdCollapseEnd
oRange .InsertAfter strMsg

You should have a new page at the end of "doc" with "Hello!" on it.

Regarding the differing syntaxes of Word, Excel, PPT, and other VBA
codings - yes, they all have different "dialects", as it were. Just as
people from different parts of a country often have different names for
objects and actions, the VBA of the various programs call things
differently.

HTH
Ed

 

[Hari Prasadh]

Ed,

Im extremely grateful for the trouble you have taken to explain stuff about
word. I would be going through your demonstration code in detail tomorrow.

Please note I have been able to get over the trouble of converting a code in
word -- Selection.TypeText Text:="$" -- to an equivalent code in excel to
control word as -- SDocument.ActiveWindow.Selection.TypeText Text:="$" .
Basically rather than using sdocument.selection I see that using
Sdocument.ActiveWindow.Selection overcomes all the compile errors etc.

Thanks a lot,
Hari
India

 

[Ed]

Hari:

I'm glad you found a workaround. I would advise, though, to learn to set
and use ranges when crossing applications - things work so much better.

Also, I will not be back online until Monday morning, USA West Coast time,
to respond to anything else. I do have this thread watched, and will catch
up then if you desire to post anything else.

Cheers!
Ed