Copy from Excel to Word

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maperalia - 22 Oct 2005 02:59 GMT

I have a program (see bellow) that I can’t make it work.
Basically, the program copy excel cells, open specific word file, however,
it not paste it the cells into the word document.
Could you please help me with this matter?

Thanks in advance.
Maperalia.

Option Explicit

Public Sub CopyExcelToWord()

CopyCellsFromExcel
OpenAWordFile
PasteIntoWord

End Sub

Sub CopyCellsFromExcel()

'***********COPY CELLS IN EXCEL****************************
Range("A1:I66").Select
Selection.Copy
Application.CutCopyMode = False
Range("A1:I1").Select
'**********************************************************

End Sub

Sub OpenAWordFile()
Dim wordApp As Object
Dim fNameAndPath As String

'***********OPEN THE MICROSOFT WORD FILE****************************
fNameAndPath = "C:\Test\Sample.doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True
'**********************************************************

End Sub
Sub PasteIntoWord()
Dim wdPasteOLEObject As String
Dim wdInLine As String

'***********PASTE THE EXCEL CELLS INTO WORD
FILE****************************
Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject,
Placement:=wdInLine, DisplayAsIcon:=False
'**********************************************************

End Sub

Reply to this Message

Helmut Weber - 22 Oct 2005 13:58 GMT

Hi Maperalia,

difficult to give a specific advice,
as this seems to be a beginner's attempt,
with

First you should know about this:
http://word.mvps.org/faqs/interdev/EarlyvsLateBinding.htm

http://word.mvps.org/faqs/interdev/ControlWordFromXL.htm

Here is an example:

Public Sub CopyExcelToWord()
' reference to Word library set
' tools references
' Word already running

Dim oWrd As Word.Application ' object Word
Dim oDoc As Word.Document ' object word Document

Set oWrd = GetObject(, "Word.application")
Set oDoc = oWrd.Documents.Open("c:\test\sample.doc")
oWrd.Visible = True
Range("A1:B10").Select
Selection.Copy
With oWrd
.Selection.PasteSpecial _
Link:=True, _
DataType:=wdPasteOLEObject, _
Placement:=wdInLine, _
DisplayAsIcon:=False
End With
End Sub

Especially these lines don't make sense:

> Dim wdPasteOLEObject As String
> Dim wdInLine As String

Here we got Word constants (long numbers),
which Excel doesn't know in case of late binding, see above.
To dim them as long would help in case of late binding,
but you would have to assign a value to them in addition,
and the right value, too.

HTH

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

maperalia - 23 Oct 2005 20:47 GMT

Helmut;
Thank you very much for your advice. I have read the information you have
sent me and it is indeed very valuable. Besides I run the program and it is
running wonderful!!!!!!!.
Just one last question, can I target the information (copy from excel) to
specific column and row in Microsoft Word?

Best regards.
Maperalia

> Hi Maperalia,
>
[quoted text clipped - 43 lines]
>
> HTH

Reply to this Message

Helmut Weber - 23 Oct 2005 21:14 GMT

Hi Maperalia,

>Just one last question, can I target the information (copy from excel) to
>specific column and row in Microsoft Word?

if you want what is in the excel cells
to appear in cells in a word table, then
a somewhat different approach would be necessary, IMHO.
That is accessing the cells content, not the cell as such,
and no copying required at all.

oWrd.Visible = True
[snip]
For r = 1 To 4
For c = 1 To 4
oDoc.Tables(1).Cell(r, c).Range.Text = _
ActiveWorkbook.ActiveSheet.Cells(r, c).Value
Next
Next
End sub

HTH

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

Helmut Weber - 23 Oct 2005 21:31 GMT

... plus of course

Dim r As Long ' row
Dim c As Long ' column

HTH

Helmut Weber

Reply to this Message

maperalia - 24 Oct 2005 01:52 GMT

Helmut;
Thanks for your quick response.
I run the program with the changes you adviced me and I got the following
error message:

Run-time error '5941'
The requested member of the collection does not exist

Then after I click debug it is highligthing at :
oDoc.Tables(1).Cell(r, c).Range.Text = _
ActiveWorkbook.ActiveSheet.Cells(r, c).Value

Could you please tell me how to fix this problem besides can I add the page
number that I want to send the information in word?

Best regards.
Maperalia

> .... plus of course
>
[quoted text clipped - 4 lines]
>
> Helmut Weber

Reply to this Message

Helmut Weber - 24 Oct 2005 08:24 GMT

Hi,

my code takes for granted, that there is
a table in your Word-document with a least
4 rows and 4 columns and that the information
from the excel-cells(1,1) to (4,4) shall be
inserted there. As you spoke of rows and
columns first, I assumed that there is a table...

Of course you can create a table programmatically
on a specific page, but I wouldn't recommened it.

Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

Reply to this Message

maperalia - 24 Oct 2005 17:06 GMT

Helmut;
Thanks for the information again. It is working perfectly!!!.

Regarding the target it into a specific page. The reason I need to do that
is because I have to copy and paste in the following way:

Copy from Excel        Paste into Word
Sheet    Cells        Page    At    Lane    Column
1    A1:I40    go to    3    1"    1    1
2    B4    go to    6    5.1"    12    24
3    B5    go to    6    5.1"    12    54

I want to do it in that way because I have noticed that I have missed
several times to write the information in the page 6. Besides, I have been
doing it manually, this take me to a lot of time consuming to locate the
information from excel into word at the right location and then fix it.

I do not need you make the whole program. I wonder if you can give me the
statement to go to the right “Page, At, Lane and Column”. I will figure it
out the rest.

Thanks again for your support.
Best regards

Maperalia

> Hi,
>
[quoted text clipped - 12 lines]
> "red.sys" & chr(64) & "t-online.de"
> Word 2002, Windows 2000

Reply to this Message

Helmut Weber - 24 Oct 2005 17:45 GMT

Hi Maperalia,

now it's getting really complicated.

I still do not know, whether you want
an object of type excel range in the word doc,
or the values from excel cells in a word table,
or the values from excel cells just somewhere
in you doc, where somewhere would be defines
by page, line, and character number.

You mean "line" instead of "lane"?

In principle, you can put the value of any
Excel cell anywhere in a Word document.
The question is, how to define where?

If you want to put the string that represents a value
from an excel cell in a word document on page 3
in line 5 on the character position 20 then:

Dim s As String
' s is supposed to be the value of an excel cell
Dim l As Long ' line
Dim c As Long ' character in a line

s = "132"
With Selection
.GoTo _
What:=wdGoToPage, _
which:=wdGoToAbsolute, _
Count:=3
.MoveDown Count:=4
' now in line5
.MoveRight Count:=20
' now before character 20
.TypeText s
End With

But this is awkward all the way.

How about adding bookmarks to your doc,
and inserting what you get from excel
at the bookmarks.

I think the whole layout
of the solution's attempt might need rethinking.

Don't give up.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

>Helmut;
>Thanks for the information again. It is working perfectly!!!.
[quoted text clipped - 38 lines]
>> "red.sys" & chr(64) & "t-online.de"
>> Word 2002, Windows 2000

Reply to this Message

maperalia - 24 Oct 2005 21:51 GMT

Helmut;
Thanks for the code. I ran it but unfortunately I got the following error:

Run-time error '438"
Object does not support this proper of method

Then after I click debug it is highligthing at
" .GoTo _
What:=wdGoToPage, which:=wdGoToAbsolute, Count:=3"

Could you please tell how to fix it?. I read the help option but does not
leaded me to solve this problem.

Best regards.
Maperalia.

> Hi Maperalia,
>
[quoted text clipped - 88 lines]
> >> "red.sys" & chr(64) & "t-online.de"
> >> Word 2002, Windows 2000

Reply to this Message

Helmut Weber - 25 Oct 2005 12:21 GMT

Hi Maperalia,

the code works perfectly here and now.

>Run-time error '438"
>Object does not support this proper of method
>
>Then after I click debug it is highligthing at
>" .GoTo _
> What:=wdGoToPage, which:=wdGoToAbsolute, Count:=3"

The most important part of your code is missing,
the question is, what code is there before ".goto".
I guess it's a typo.

Try something quite simple like this at first:

With Selection
.GoTo _
What:=wdGoToPage, _
which:=wdGoToAbsolute, _
Count:=3
End With

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

maperalia - 25 Oct 2005 22:56 GMT

Helmut;
I really appreciate you helping me with this matter.

Best regards.
Maperalia

> Hi Maperalia,
>
[quoted text clipped - 19 lines]
> Count:=3
> End With

Reply to this Message

Rate this thread: