Copy/Paste From Excel To Word Macro

K

Kris Taylor

Hi All!

I have deciphered the code to open a word document from excel, however I
am unaware of how to copy and paste different cells at a time.

Here is a general run down of what I wish for the macro to do.

First, in excel on the active worksheet, I want to use ctrl+down in
column A to go to the last entry, copy that cell and paste it into a
certain place in an existing word document. I then want to go right 2
cells to column C and copy and paste that cell into another area in
word. Finally go to column B and copy/paste that cell in yet another
area in word!

If possible please post a similar code for what is required that I could
play around with a little.

Thanks in advance for all those who took the time to read this!

Thanks,

Kris
www.questofages.org



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
D

Debra Dalgleish

If you have numbered bookmarks in the Word document, you could use a
macro similar to the following:

'===========================
Sub CopyToWord()
Dim ws As Worksheet
Dim r As Long
Dim i As Integer
Dim WdApp As Object
Dim str As String
Dim strFile As String
Dim doc As Object
Set ws = Sheets("Sheet1")
r = ws.Cells(Rows.Count, 1).End(xlUp).Row
strFile = "C:\Data\Test.doc"
On Error Resume Next
Set WdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Err.Clear
Set WdApp = CreateObject("Word.Application")
End If

WdApp.Documents.Open Filename:="strFile", _
ConfirmConversions:=False, ReadOnly:=False
Set doc = WdApp.activedocument
MsgBox doc.Name
WdApp.Visible = True
For i = 1 To 3
str = ws.Cells(r, i).Value
With WdApp
.Selection.GoTo What:=wdGoToBookmark, Name:="bkmk" & i
.Selection.TypeText Text:=str
End With
Next i
doc.Close SaveChanges:=wdSaveChanges
Set WdApp = Nothing

End Sub
'======================================
 
D

Dave Peterson

I bet this line didn't want the double quotes:

WdApp.Documents.Open Filename:="strFile", _
maybe...
WdApp.Documents.Open Filename:=strFile, _
 
D

Debra Dalgleish

Thanks! Yeah, that's what I meant.
(Never change the code at the last minute)

Dave said:
I bet this line didn't want the double quotes:

WdApp.Documents.Open Filename:="strFile", _
maybe...
WdApp.Documents.Open Filename:=strFile, _
Click to expand...
 
D

Debra Dalgleish

In the Visual Basic Editor, choose Tools>References
Find Microsoft Word x.0 Object Library, and check it

Also, check that the bookmarks in the Word document are named bkmk1,
bkmk2, etc., or change the code to match your naming.
 
K

Kris Taylor

Debra Dalgleish said:
In the Visual Basic Editor, choose Tools>References
Find Microsoft Word x.0 Object Library, and check it

Also, check that the bookmarks in the Word document are named bkmk1,
bkmk2, etc., or change the code to match your naming.
Click to expand...


Debra,

Thanks for the reply!!!

Sadly,I'm still having problems though...

I'm using Excel and Word 97, and I can't seem to find the required
reference. This is what I think the problem is. I do not have a
Microsoft Word reference anywhere in that reference list.

Also, it seems that there isn't a copy/paste feature in the code of
this macro. I could be wrong.

Anyways, I look forward to your future support!

Thanks in advance,

Kris Taylor
www.questofages.org
 
D

Debra Dalgleish

Add the following two lines at the top of the module:

Const wdGoToBookmark = -1
Const wdSaveChanges = -1

then try to run the code.

The code doesn't copy and paste. It takes the value from a cell, and
stores it in a variable named str.
The text from the str variable is inserted at the bookmark in your Word
document.
Do you have bookmarks in the Word document? (Insert>Bookmark)
 
K

Kris Taylor

Hi Debra,

For some reason, the code I added has not changed anything. Also, I
do have bookmarks implemented. They are Oncor, Workorder and
Serviceorder. I also have the reference working.

My current code looks as follows:

====================================
Sub CopyToWord()
Const wdGoToBookmark = -1
Const wdSaveChanges = -1
Dim ws As Worksheet
Dim r As Long
Dim i As Integer
Dim WdApp As Object
Dim str As String
Dim strFile As String
Dim doc As Object
Set ws = Sheets("Tecumseh")
r = ws.Cells(Rows.Count, 1).End(xlUp).Row
strFile = "C:\Routing Slip.doc"
On Error Resume Next
Set WdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Err.Clear
Set WdApp = CreateObject("Word.Application")
End If

WdApp.Documents.Open FileName:=strFile, _
ConfirmConversions:=False, ReadOnly:=False
Set doc = WdApp.ActiveDocument
MsgBox doc.Name
WdApp.Visible = True
With WdApp

.Selection.GoTo What:=wdGoToBookmark, Name:="WorkOrder"
.Selection.TypeText Text:=ws.Cells(r, 1).Value

.Selection.GoTo What:=wdGoToBookmark, Name:="ServiceOrder"
.Selection.TypeText Text:=ws.Cells(r, 3).Value

.Selection.GoTo What:=wdGoToBookmark, Name:="OnCor"
.Selection.TypeText Text:=ws.Cells(r, 2).Value

End With

doc.Close SaveChanges:=wdSaveChanges
Set WdApp = Nothing

End Sub

Thanks for all your time and effort thus far!

Kris Taylor
www.questofages.org
 
D

Dave Peterson

I'm not Debra, but I ran your code and it worked fine for me.

Can you step through it and verify that things are ok at each step.

Click within the subroutine and start hitting F8's.

then when Word becomes visible, just maximize that and see what happens at each
bookmark.
 
K

Kris Taylor

Well, I believe that the problem is opening the Word file. The macro
will open Word, but not the targeted file. I just tried linking to a
different file in a different location and I get the same error. Word
is opened, however isntead of opening the file, a message box pops up
in Excel with the name of the document in question writen in it.

Thoughts?

Thanks,

Kris Taylor
www.questofages.org
 
D

Debra Dalgleish

Remove the line: MsgBox doc.Name

Did you step through the code, as Dave suggested?
That would help determine where the problem is occurring.
 
K

Kris Taylor

Debra Dalgleish said:
Remove the line: MsgBox doc.Name

Did you step through the code, as Dave suggested?
That would help determine where the problem is occurring.
Click to expand...

Ok, here's what I've come up with thus far. I took out MsgBox
doc.Name
as well as doc.Close SaveChanges:=wdSaveChanges

The macro seems to go to the various bookmarks however nothing is
imported from the excel file. I do not see any physical selection
from the excel file, however I am unsure if I am supposed to actually
see anything.

Thoughts?

Thanks for your help thus far.

Kris Taylor
www.questofages.org
 
D

Debra Dalgleish

Since you're not using the str variable to insert the values, use the
CStr function in the code:

.Selection.GoTo What:=wdGoToBookmark, Name:="WorkOrder"
.Selection.TypeText Text:=CStr(ws.Cells(r, 1).Value)

.Selection.GoTo What:=wdGoToBookmark, Name:="ServiceOrder"
.Selection.TypeText Text:=CStr(ws.Cells(r, 3).Value)

.Selection.GoTo What:=wdGoToBookmark, Name:="OnCor"
.Selection.TypeText Text:=CStr(ws.Cells(r, 2).Value)
 
Top