VBA (Loop and formating troubles) Excel to Word paste and print

Question

I'll be as brief as I can to not make it overcomplicated. I need to copy name and last name from Excel, paste it into Word template and print. I have a huge excel file which I need to copy two columns from each row ( ie E31:F31,E40:F40) into a bookmark in Word and then print it.

I need to loop my script to go either from row X to Y or X number of times. The excel database is not well formated. I managed to apply paste&print to custom number of cells, but I get the "document it used do you want to open a temp copy" error probably because I try to do it all at once.

Text copied shows up in Word with big gaps between two words (what came from columen E and F), how do I fix it?

Sub Copy_Excel_Cell_to_Word_Form()

Dim wdApp As Object 'Word.Application
Dim wdDoc As Object 'Word.Document

For i = 31 To 60

    'Open new instance of Microsoft Word
    Set wdApp = CreateObject("Word.Application")
    'Make application visible
    'wdApp.Visible = False
    'Open the word document
    Set wdDoc = wdApp.Documents.Open("\\example.doc")
    'Copy value
    Worksheets(1).Range("E" & i, "F" & i).Copy
    'Paste to word document
    wdDoc.Bookmarks("WORKER").Range.PasteAndFormat (wdFormatPlainText)
    wdDoc.PrintOut

Next

End Sub

I think you should use VBA to pull the data into a different Excel file (just the data you want, not all of it), clean it up as necessary, then use that file as a basis for Word's mail merge. What do you think of that? — Dick Kusleika
You first point or question is confusing. Can you provide more information? — Seth
@Dick Yes you are right, I didn't know this tool. But on the other hand, I'm almost there with the VBA code :D. — user3158006
@Seth Sure. I need to loop "copy,paste,print,go to next row and repeat' for eithes x number of loops or from row x to y instead of just doing all the matching ranges at once, which gives me "this document is locked" error — user3158006

Seth Seth · Accepted Answer · 2014-01-03T22:55:29

For your second point, try this:

Declare a string variable called workerName. Instead of Worksheets(1).Range("E" & i, "F" & i).Copy use

workerName = Worksheets(1).Cells(i,5).Value 'e = 5
workerName = workerName & " " & Worksheets(1).Cells(i,6).Value & vbCr 'f = 6

And instead of use wdDoc.Bookmarks("WORKER").Range.PasteAndFormat (wdFormatPlainText) use

wdDoc.Bookmarks("WORKER").Range.Text = workerName

This should clear up the big gaps.

VBA (Loop and formating troubles) Excel to Word paste and print

1 Answers