PDA

View Full Version : copy data from excel to word without mail merge



ferret
03-15-2011, 09:41 AM
Hi all, thanks for the help you have given me so far as a guest...much appreciated. I'm still on the steep learning curve for vba and am trying to take a step further.

I have an excel spreadsheet with data in small range of cells (c2:d7) [I have tried keeping all range references the same but still have the problem]. I want to transfer that data into an existing word document keeping it's 'table' layout, and save the document using a new file name and location.

So far, my journey has taught me how to open the word document. I have looked at a few of the scripts you have already posted to eg paste the cells as a inline object, or cross reference from named cells to bookmarks. I can see the logic being followed but my scripts are falling over at the same place... I open the document, grab the data from the spreadsheet and get a 5941 error when I try to insert it into word.

I have ensured that there is a reference to the Microsoft Office 14 word object library... but I don't understand about 'objects'
For instance I have tried this code from another thread 'Open the word document
Set wdApp = CreateObject("Word.Application")
wdApp.Documents.Open openthisfile
wdApp.Visible = True

'copy cells
Worksheets(1).Range("c2:d7").Select
Selection.Copy
'insert cells

wdApp.ActiveDocument.InlineShapes(1).Activate
Range("A1:B6").Select
ActiveSheet.Paste
Application.CutCopyMode = False


'openthisfile' is a string variable which sets the filepath and it opens ok
it falls over 4 lines from the end;

wdApp.ActiveDocument.InlineShapes(1).Activate

which is highlighted with the error when I debug...

Your help will be much appreciated.

Cheers

Kenneth Hobs
03-15-2011, 11:26 AM
Why are you selecting an inlineshape? What if it does not exist?

Here is an example. Obviously, you need to change the bookmark and doc names.
Sub CopytoWord()
'Requires Tools > References > Microsoft Word 11.0 Object Library
Dim wdApp As Word.Application
Dim wddoc As Word.Document
Dim doc As String

doc = "x:\msword\MyFile.doc"
If Dir(doc) = "" Then
MsgBox "Error, file does not exist." & vbLf & doc, vbCritical, "File is Missing"
Exit Sub
End If

On Error GoTo errorHandler

Set wdApp = New Word.Application

With wdApp
'Add makes a copy like from a template even though it may be a DOC file.
Set wddoc = .Documents.Add(Template:=doc)
'Set wdDoc = .Documents.Open(doc)
.Visible = True
End With

With wddoc.Bookmarks
.Item("testbm").Range.InsertAfter Worksheets("Sheet1").Range("A1").Value
End With

errorExit:
Set wddoc = Nothing
Set wdApp = Nothing

Exit Sub

errorHandler:
MsgBox "Unexpected error: " & Err.Number & vbLf & Err.Description
Resume errorExit

End Sub

ferret
03-15-2011, 11:40 AM
Sir, you are a genius! Thank You! Works perfectly :bow: