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!

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
'======================================

Kris Taylor wrote:
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!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

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



Debra Dalgleish wrote:

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
'======================================

Kris Taylor wrote:
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!

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

--

Dave Peterson

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

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

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



Debra Dalgleish wrote:

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
'======================================

Kris Taylor wrote:

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!

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks for the replies thus far, however this doesn't seem to work. All
I get is a pop up in excel stating the name of the document that I'm
trying to open.

Ideas?

Kris
www.questofages.org



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

In the Visual Basic Editor, choose ToolsReferences
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.

Kris Taylor wrote:
Thanks for the replies thus far, however this doesn't seem to work. All
I get is a pop up in excel stating the name of the document that I'm
trying to open.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Dalgleish wrote in message ...
In the Visual Basic Editor, choose ToolsReferences
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.

Kris Taylor wrote:
Thanks for the replies thus far, however this doesn't seem to work. All
I get is a pop up in excel stating the name of the document that I'm
trying to open.


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

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? (InsertBookmark)

Kris Taylor wrote:
Debra Dalgleish wrote in message ...

In the Visual Basic Editor, choose ToolsReferences
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.

Kris Taylor wrote:

Thanks for the replies thus far, however this doesn't seem to work. All
I get is a pop up in excel stating the name of the document that I'm
trying to open.



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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

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.



Kris Taylor wrote:

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

--

Dave Peterson

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

Remove the line: MsgBox doc.Name

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

Kris Taylor wrote:
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.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra Dalgleish wrote in message ...
Remove the line: MsgBox doc.Name

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

Kris Taylor wrote:
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.

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

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)


Kris Taylor wrote:
Debra Dalgleish wrote in message ...

Remove the line: MsgBox doc.Name

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

Kris Taylor wrote:

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.


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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra! You did it! Everything now works great!

Thank you so very much! This comes greatly appreciated!

Kris Taylor
www.questofages.org

You're welcome! Thanks for letting me know that it worked.

Kris Taylor wrote:
Debra! You did it! Everything now works great!

Thank you so very much! This comes greatly appreciated!

Kris Taylor
www.questofages.org


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html