-
copy data from excel to word without mail merge
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 [vba]'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
[/vba]
'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
Last edited by ferret; 03-15-2011 at 09:52 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.
[VBA]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
[/VBA]
-
Wahay!
Sir, you are a genius! Thank You! Works perfectly
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules