There are so many things you can do. In your excel workbook, you need to add
a sheet named 'Template' and run this code:
In the Visual Basic Editor, set a reference to Word. click Tools >
references > Microsoft Word 10.0 (for Word 2002; 11.0 for newer versions).
Sub ControlWord()
' You must pick Microsoft Word 8.0 from Tools>References
' in the VB editor to execute Word commands.
' See VB Help topic "Controlling One Microsoft Office Application from
Another"
' for more information.
' Originally published by
www.MrExcel.com 2/28/1999
Dim appWD As Word.Application
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application.8")
appWD.Visible = True
Sheets("Data").Select
'Find the last row with data in the database
FinalRow = Range("A9999").End(xlUp).Row
For i = 2 To FinalRow
Sheets("Data").Select
' Copy the name to cell C4
Range("A" & i).Copy Destination:=Sheets("Template").Range("A1:J1")
' Copy data columns, transpose and paste in C10:C13
Range("B" & i & ":J" & i).Copy
Sheets("Template").Select
Range("C10").PasteSpecial Transpose:=True
' Copy the data for the new document to the clipboard
Range("A1:F15").Copy
' Tell Word to create a new document
appWD.Documents.Add
' Tell Word to paste the contents of the clipboard into the new
document
appWD.Selection.Paste
' Save the new document with a sequential file name
appWD.ActiveDocument.SaveAs Filename:="File" & i
' Close this new word document
appWD.ActiveDocument.Close
Next i
' Close the Word application
appWD.Quit
End Sub
You can also try this:
Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X
object library
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Creating new document..."
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add
For Each ws In ActiveWorkbook.Worksheets
Application.StatusBar = "Copying data from " & ws.Name & "..."
ws.UsedRange.Copy ' or edit to the range you want to copy
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
Application.CutCopyMode = False
wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
' insert page break after all worksheets except the last one
If Not ws.Name = Worksheets(Worksheets.Count).Name Then
With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
.InsertParagraphBefore
.Collapse Direction:=wdCollapseEnd
.InsertBreak Type:=wdPageBreak
End With
End If
Next ws
Set ws = Nothing
Application.StatusBar = "Cleaning up..."
' apply normal view
With wdApp.ActiveWindow
If .View.SplitSpecial = wdPaneNone Then
.ActivePane.View.Type = wdNormalView
Else
.View.Type = wdNormalView
End If
End With
Set wdDoc = Nothing
wdApp.Visible = True
Set wdApp = Nothing
Application.StatusBar = False
End Sub
If that doesn't work for you, take a look at this:
http://word.mvps.org/faqs/InterDev/ControlWordFromXL.htm
If that doesn't work, look here:
http://word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm
Regards,
Ryan---
