Opening Excell From Word (Word XP)

Topic

New Reply

I am creating a macro where I generate a table, but I ultimately want to drop the contents into an excell template. How can I open Excel from the macro?

-Jody

Reply | Quote

Replies

Dim oXLApp as Object
Set oXLApp=CreateObject(“Excel.Application”)
‘do some stuff
oXLApp.Quit
Set oXLApp=Nothing

Reply | Quote

Controlling one application from another in code is called Automation.

In order to do things with Excel from Word, you must first set a reference to the Excel object library.
In the Visual Basic Editor, select Tools | References…
Scroll down until you see Microsoft Excel 10.0 Object Library (the 10.0 is the version for Office XP; for Office 2000, it’s 9.0 and for Office 97, it’s 8.0)
Check the corresponding box and click OK.
Next, you must create an Excel application object. This will start an invisible copy of Excel. You can use Excel VBA, but you must prefix Excel instructions by the name of an Excel object. The outline of the code for this is:

Sub TestExcel()

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet

On Error Goto ErrHandler

‘ Create Excel, Workbook and Worksheet objects
Set xlApp = CreateObject(“Excel.Application”)
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets(1)

‘ Example code to transfer something from Word to Excel
ActiveDocument.Tables(1).Copy
xlWs.Paste Destination:=xlWs.Cells(1, 1)
xlWb.SaveAs FileName:=”C:Test.xls”

ExitHandler:
‘ Mopping up
On Error Resume Next
xlWb.Close SaveChanges:=False
xlApp.Quit
Set xlWS = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
Exit Sub

ErrHandler:
‘ Report error
MsgBox Err.Description
Resume ExitHandler

End Sub

Error handling is extremely important in Automation. If you don’t clean up after you, Excel will keep on running invisible if an error occurs.

It is often a good idea to work out the Excel code to be used in Excel itself, because its easier to debug there. When you know what to do, use this to write the Word macro.

Reply | Quote

Thank-You!

Reply | Quote