using excel macro to activate word document

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

I have a bunch of data in Excel that I would like to copy
and paste into a Word document to create different
tables. I have written a macro in Word to format these
tables once the excel data is copied over.

Is there a way I can use an Excel macro to automatically
select a block of cells, copy and paste into Word, and
activate the Word macro?

Thanks,

Amy
 
If the Word macro is saved as "Macro1" in a template named MyTemplate.dot
and the range I want to copy over is A1:B2, the following would work (in
addition to editing to the range you really want, correct the path info for
templates on your PC)

Sub XLtoWord()
Range("A1:B2").Select
Selection.Copy

Dim bStarted As Boolean
Dim oApp As Object
Dim strPath As String

strPath = "C:\Documents and Settings\your ID\"
strPath = strPath & "Application Data\Microsoft\Templates\"

On Error Resume Next
Set oApp = GetObject(, "Word.Application")

If Err <> 0 Then
bStarted = True
Set oApp = CreateObject("Word.Application")
End If

oApp.Activate
oApp.Visible = True
oApp.Documents.Add Template:=strPath & "MyTemplate.dot"
oApp.Selection.Paste

oApp.Run "Macro1"

If bStarted Then
oApp.Quit
End If

Set oApp = Nothing
End Sub


Steve
 
Back
Top