Automating Tasks in Microsoft Office

1
Automating Tasks in Microsoft Office

• Steve Silberman
• Mercury Interactive
• steves_at_mercury.co.il

2
Introduction

• Save Time and Effort using Office
• Save aggravation
• Whats a macro?

3
Good and Bad News

• MS Office requires a lot of dull work
• It can be automated
• Requires programming
• Program language is easy
• Things you program are NOT easy
• Help on line
• Help impossible to use unless you already know
  what youre looking for.

4
More Good News and Bad News

• You can record a macro automatically w/o
  understanding anything
• The recorded macro almost never does exactly what
  you want

5
Help is at Hand

• By recording a macro, you can find out what to
  look for in help.
• Then use help to get the work done.

6
Whats in a Macro

• Nouns and Adjectives
• Verbs
• Grammar

7
Nouns and Adjectives A Rose by Any Other Name

• An Object is a Thing with a scary name
• A Property is an Characteristic with a scary
  name
• The Value of a Property is an an Adjective or
  Quantity with a scary name
• A Method is a Verb with a scary name
• A Collection is a Collection with an ordinary
  name

8
Objects Have Objects
Objects contain objects MyHouse.Kitchen.Sink MyAi
rplane.Galley.Sink
9
Objects Have Properties
Objects can be described MyHouse.Kitchen.Length2
.5 MyHouse.Kitchen.Sink.MaterielStainless
10
Objects Have Methods
MyAirplane.Fly MyHouse.Kitchen.Sponga MyAirplane.K
itchen.Sink.Scrub MyHouse.Kitchen.Sink.Scrub Acti
veDocument.PrintOut ActiveDocument.Save
11
What do we do?

• Record macro
• Use recorded macro as starting point.
• Warning well have to do a little programming

12
Collections

• Find collection in help
• For EachNext
• For Each MyObject in MyCollection
• (do stuff, maybe exit for)
• Next MyObject
• -------------------------------------------------
• Well, thats perfectly clear then. What say we
  all go home and start writing macros

13
Format Warning

• Theres a new style Warning
• Convert all existing Warning to the new style

14
Format Warning Continued

• Bad idea, look at the weather report.
• How about asking for confirmation?

15
Format Warning In Boxes

• And what if you want to convert the word
  Warning only if its in a text box?
• Remember collections?
• For Each MyObject in MyCollection
• (do stuff, maybe exit for)
• Next MyObject
• The question is, what collection are the text
  boxes in?

16
Format Warning In Boxes 2

• To find out your nouns, either record a macro
  that makes a text box, or look up TextBox in the
  help.

17
Format Warning In Boxes 3

• Exploring the help, we learn that shapes have
  properties
• Script
• TextFrame.TextRange.Text
• Type msoTextBox

18
Format Warning In Boxes 4

• If we look for TextFrame,
• we also pick up a code fragment
• For Each s In ActiveDocument.Shapes
• With s.TextFrame
• If .HasText Then _ MsgBox.TextRange.Text
• End With
• Next

19
Format Warning In Boxes Errors
We also saw that there is a Script
property. Lets see what it looks like, maybe we
can use it. Add the line Debug.Print
s.Script What happens when we run it?
20
Format Warning In Boxes Errors Continued

• This is easy, just comment it out.
• But what about a situation where you want to see
  something if it exists?
• What about a situation where you want to do
  something if theres a error?
• On Error Resume Next
• On Error GoTo (label)
• On Error GoTo 0

21
What Did We Do?

• Recorded macro to get started
• Used recorded macro as starting point to look in
  help.
• Polished with error handling

22
Revision Tracking

• Very useful when developing a document,
  especially in a team
• There are lots of changes and youre only
  interested in text changes
• Your mission, should you decide to accept it, is
  to create a macro that accepts everything but
  insertions and deletions.

23
First, Record a macro

• Sub AcceptOneChange()
• '
• ' AcceptOneChange Macro
• ' Macro recorded ?6/5/02 by Steve
• '
• Selection.NextRevision (True)
• Selection.Range.Revisions.AcceptAll
• Selection.NextRevision (True)
• End Sub
• Well, that helps, doesnt it?
• What do we look for help on?

24
Into Help

• Revisions Ctl-F1
• Revisions property
• Revisions Collection

25
From the help
MsgBox ActiveDocument.Revisions.Count The
following example accepts all the revisions in
the selection. For Each myRev In
Selection.Range.Revisions myRev.Accept
Next myRev
26
So Now We know
We can step through all the revisions and accept
them with Dim myRev For Each myRev In
ActiveDocument.Revisions myRev.Accept Next
myRev Thats good, but not what we want.
27
Selecting A Kind of Revision

• Revision Object
• Properties -gt Type
• Revision

28
Selecting Revisions

• So we can write our If Statement
• If aRev.Type ltgt wdRevisionInsert and aRev.Type
  ltgt wdRevisionDelete Then
• aRev.Accept
• End If

29
Finish

• Add error handling
• Test it
• All done
• Make a button if you want (you already know how)

30
Can You Do It Yourself at Home?

• Not easily.
• Try simple macros
• Try harder one
• When you have to do something ten times, automate
  it
• The more you use it, the easier it gets.

31
When?

• In my experience, automating a task takes 3 to 20
  times longer than doing it by hand.
• Automate a task if
• Youre going to do it A LOT
• You want other people to do it the same way every
  time
• Its very error prone
• Its very complicated
• Its REALLY annoying
• Youre going to do a presentation on VBA to some
  technical communicators

32
Now You Have Everything You Need

• NO WAY, RAY
• Theres a lot more.
• Its all in the help
• Explore, experiment, learn
• And most of all,
• HAVE FUN!