PDA

View Full Version : [SOLVED] copy from excel 2010 to word 2010



newbie83
01-27-2015, 06:35 AM
Hi All,

i have a little problem i can not get me head around, i am trying to copy the contents from "CallsTaken" to "Forms" then from "Forms" to a word document starting at paragraph 16

i have produced some code which is below, it does everything it should from copying the activecell row from "CallsTaken" and pasting into the selected cells in "Forms" then copying the data in "Forms" opening the specified word Doc, however, it does not paste the data to word. the data is there as if you right click then click paste it pastes the data where it needs to be.

can anyone shed any light on this please....... P.S i also get a object required warning

code

Sub Copy_Content()

'Copy content of selected row in CallsTaken and paste into Form

Worksheets("CallsTaken").Select
ActiveCell.Resize(, 13).Copy

Worksheets("Form").Select
Range("F8:F20").PasteSpecial Transpose:=True

'Copy contents of sheet 2 "Form to a Word Document

Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim WordPara As Word.Paragraph
Dim ExlRange As Excel.Range

'create new instance of word
Set WordApp = New Word.Application

'Make the Word window visaable
WordApp.Visible = True
WordApp.Activate

'maximise word window
WordApp.WindowState = wdWindowStateMaximize

'open existing word document
Set WordDoc = WordApp.Documents.Open("C:\Users\DELL User\Documents\0800WordDoc.docx")

'Select data to be copied and store in ExlRange
Set ExlRange = Worksheets("Form").Range("D8").Resize(13, 3).Copy


WordDoc.Paragraphs.Add (16)
WordDoc.Paragraphs(WordDoc.Paragraphs.Count).Range.Text = ExlRange.Value
ExlRange.PasteSpecial xlPasteAll



Set WordApp = Nothing
Set WordDoc = Nothing
Set ExlRange = Nothing

End Sub

gmayor
01-27-2015, 07:04 AM
You have been mixing your Excel and Word commands :)
The following should be closer to what you want. In the absence of your worksheet I will assume your Excel ranges are correct.



Sub Copy_Content()
Dim WordApp As Object
Dim WordDoc As Object
Dim WordPara As Object
Dim oRng As Object
Dim i As Long
Dim ExlRange As Excel.Range
'Copy content of selected row in CallsTaken and paste into Form

Worksheets("CallsTaken").Select
ActiveCell.Resize(, 13).Copy

Worksheets("Form").Select
Range("F8:F20").PasteSpecial Transpose:=True

'Copy contents of sheet 2 "Form to a Word Document



On Error Resume Next
Set WordApp = GetObject(, "Word.Application")
If Err Then
Set WordApp = CreateObject("Word.Application")
End If
On Error GoTo 0

'Make the Word window visaable
WordApp.Visible = True
WordApp.Activate

'maximise word window
WordApp.WindowState = 0 '0=Normal:1=Maximized:2=Minimized

'open existing word document
Set WordDoc = WordApp.Documents.Open("C:\Users\DELL User\Documents\0800WordDoc.docx")
'Select data to be copied and store in ExlRange
Set ExlRange = Worksheets("Form").Range("D8").Resize(13, 3)
ExlRange.Copy
Set oRng = WordDoc.Range
oRng.collapse 0
For i = 1 To 16
oRng.Text = oRng.Text & vbCr
Next i
oRng.collapse 0
oRng.PasteSpecial Link:=False, DataType:=10
'ExlRange.PasteSpecial?

Set WordApp = Nothing
Set WordDoc = Nothing
Set oRng = Nothing
Set ExlRange = Nothing

End Sub

newbie83
01-27-2015, 07:32 AM
You are a genius, thank you :)

snb
01-27-2015, 07:38 AM
sub M_snb()
with getobject("C:\Users\DELL User\Documents\0800WordDoc.docx")
.content.insertafter join(application.transpose(Worksheets("Form").Range("D8:D20")),vbcr)
.windows(1).visible=true
end with
end sub