Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy And Paste From Excel To Word
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 titled Tecumseh, I want to use ctrl+down in column A to go to the last entry, copy that cell and paste it into word at a bookmark titled WorkOrder. I then want to go right 2 cells to column C and copy and paste that cell into another bookmark titled ServiceOrder in word. Finally go to column B and copy/paste that cell in yet another bookmark titled Oncor in word! If possible please post a similar code for what is required that I could play around with a little. I received this code from Developpers Dex, however it doesn't seem to work for me. All it does is create a pop up that refers to the word document that I want opened. ======================================= 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 ========================================== Thanks in advance for all those who took the time to read this! Thanks, Kris www.questofages.org |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy And Paste From Excel To Word
Try changing this portion:
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 to: 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 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 titled Tecumseh, I want to use ctrl+down in column A to go to the last entry, copy that cell and paste it into word at a bookmark titled WorkOrder. I then want to go right 2 cells to column C and copy and paste that cell into another bookmark titled ServiceOrder in word. Finally go to column B and copy/paste that cell in yet another bookmark titled Oncor in word! If possible please post a similar code for what is required that I could play around with a little. I received this code from Developpers Dex, however it doesn't seem to work for me. All it does is create a pop up that refers to the word document that I want opened. ======================================= 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 ========================================== Thanks in advance for all those who took the time to read this! Thanks, Kris www.questofages.org -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy And Paste From Excel To Word
Dave,
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy And Paste From Excel To Word
Dave Peterson wrote in message ...
this is the copy|paste stuff: .Selection.GoTo What:=wdGoToBookmark, Name:="OnCor" .Selection.TypeText Text:=ws.cells(r,2).value Inside the VBE: tools|references scroll down the list looking for: Microsoft Word xx.x Object Library But Debra responded to your other post, too. Kris Taylor wrote: Dave, 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 Hi Dave, I realized that I was looking in the wrong program for the reference. I was looking in Excel instead of Word. Anyways, that was already implemented from prior macro creation. I also didn't realize that Developper's Dex has some sort of link with Google and I apologize for double posting. I run my own forums and I know what kind of a hassle that can be :) Anyways, Debra's code still hasn't worked for me. Please review my post in the other thread if you can add anything further! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy And Paste From Excel To Word
I replied--it worked ok for me.
Kris Taylor wrote: Dave Peterson wrote in message ... this is the copy|paste stuff: .Selection.GoTo What:=wdGoToBookmark, Name:="OnCor" .Selection.TypeText Text:=ws.cells(r,2).value Inside the VBE: tools|references scroll down the list looking for: Microsoft Word xx.x Object Library But Debra responded to your other post, too. Kris Taylor wrote: Dave, 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 Hi Dave, I realized that I was looking in the wrong program for the reference. I was looking in Excel instead of Word. Anyways, that was already implemented from prior macro creation. I also didn't realize that Developper's Dex has some sort of link with Google and I apologize for double posting. I run my own forums and I know what kind of a hassle that can be :) Anyways, Debra's code still hasn't worked for me. Please review my post in the other thread if you can add anything further! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste into Excel and Word | Excel Discussion (Misc queries) | |||
copy & paste from Excel to Word | Excel Discussion (Misc queries) | |||
Won't let me copy and paste from Excel to Word | Excel Discussion (Misc queries) | |||
COPY & PASTE WORD Doc into EXCEL | Excel Discussion (Misc queries) | |||
copy excel paste to word | Excel Discussion (Misc queries) |