Automating Word and PowerPoint from #Excel with a Worksheet UDF by David Hager


I have been following Mark’s recent posts at about automation from Excel.

That got me thinking about the use of user-defined functions in automating/instatiating other applications.

As it turns out, I first demonstrated the ability of user-defined functions to be used in automating an app (in this case Mappoint) in 2005 in this article on Dick Kuseika’s web site.

In the comments of this article, Jan Karel Pieterse ( ) showed that the same thing could be done with Microsoft Word. I am using his example here to show that it does work.

Here is the code:

Function WriteResultToWord(stest As String)

Dim oWdObj As New Word.Application

Application.Volatile False

oWdObj.Visible = True


oWdObj.ActiveDocument.Paragraphs.First.Range.InsertAfter ” ” & stest

End Function

The result of entering this formula in cell E2 (=WriteResultToWord(D1)) is to open Word and insert the text into the blank document, shown in the following figure.


The next step was to find out if this technique would work with any other application. To test this on Power Point, I used this great example from Chandoo’s site.

I simply changed the Sub routine to a Function, with little modification (see code in the example file).

So, entering =AddChartsPowerPoint() in a cell opens Power Point and adds two charts (see below).


In this article I shared the technique of automation using a UDF. I am sure that you will extend these ideas in your own work.

Here is the example file.


2 thoughts on “Automating Word and PowerPoint from #Excel with a Worksheet UDF by David Hager

  1. Pingback: #Excel Super Links #85 – shared by David Hager | Excel For You

  2. Pingback: #Excel: Generate a List of Antonyms Using an User-Defined Function (UDF) | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s