MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copy from Excel, then open and run a macro in Word template


Posted by Klaas on January 25, 2001 9:01 PM

I would like to put a button on a Excel(2000) sheet which if pushed will:

1) Copy the range F1 - F1460 (from the open sheet)

2) Open a Word template SSS.DOT which is stored in \Windows\Application Data\Microsoft\Templates\Graphology

3) Run the macro "SSS_Report", which is already in this Word template document.

What VB code do I use?

Regards
Klaas Hogenesch
klaas@paradise.net.nz


Posted by Dan on January 27, 2001 5:41 AM

Klaas,

If you place a command button on your worksheet and put this code in its click event you should get the results you want.


Private Sub CommandButton1_Click()
Dim WordApp As Object
ActiveSheet.Range("f1:f1460").Copy
Set WordApp = CreateObject("Word.Application")
WordApp.Documents.Add "C:\Windows\Application Data\Microsoft\Templates\Graphology\sss.dot"
WordApp.Visible = True
WordApp.Run "SSSReport"
End Sub