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

 

I have been following Mark’s recent posts at https://exceloffthegrid.com/ about automation from Excel.

https://exceloffthegrid.com/controlling-word-from-excel-using-vba/

https://exceloffthegrid.com/controlling-powerpoint-from-excel-using-vba/

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.

http://dailydoseofexcel.com/archives/2005/04/25/automating-mappoint/

In the comments of this article, Jan Karel Pieterse (http://www.jkp-ads.com/ ) 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.Documents.Add

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.

Automate1

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.

http://chandoo.org/wp/2011/08/03/create-powerpoint-presentations-using-excel-vba/

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).

Automate2

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.

AutomateUDF

Advertisements

One thought 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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s