Copy from Excel to Word

[maperalia]

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

 

[Helmut Weber]

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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[maperalia]

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

 

[Helmut Weber]

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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[Helmut Weber]

.... plus of course

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

HTH

Helmut Weber

 

[maperalia]

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

 

[Helmut Weber]

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

 

[maperalia]

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

 

[Helmut Weber]

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.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[maperalia]

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.

 

[Helmut Weber]

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

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[maperalia]

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

Best regards.
Maperalia