Transcript Lets Learn Medicare!

Microsoft Office
Automation Using
Pika Data
Overview
Word – Pika Training
© August 2006, Pro Seniors, Inc.
The Parts of the Process
Part 1 – Create an MS Query
(pulls the necessary data records from Pika)
Part 2 – Create a Word Mail Merge Main Doc
(this is the doc that the data will be merged into)
Part 3 – Attach the Query to the Merge Doc
(this will merge the Pika data into the main doc)
Part 4 – Create a Network Global Word Template
(allows network-wide simple access to main doc)
Part 5 – Automate Process With Simple Macros
(makes the entire process seamless)
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Part 1: MS Query
1) Query was designed to work with Excel, so


First create and save the query using Excel
Once saved, from Word we attach the Query as
the Merge Data Doc to the Main Doc
2) MS Query supports MySQL formulas including
And, Or, & If . . Then . . Else statements
Word – Pika Training
© August 2006, Pro Seniors, Inc.
See: www.proseniors.org
Medicare Review
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Part 2: Word Mail Merge
1) Word Mail Merge has 2 parts:


Main document
Data document
2) The Main Doc can be any document type



Letter
Labels
List
Word – Pika Training

Envelopes

Pleadings

Directory
© August 2006, Pro Seniors, Inc.
Part 2: Word Mail Merge
3) The Data Doc can be a




Word doc
Excel spreadsheet
Outlook contacts
Database
 Access
 SQL or MySQL (Pika)
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Part 2: Word Mail Merge
4) Main Doc has fields where the data is merged
5) These fields can be

Data fields
 <<LastName>>

Word fields
 << If … Then … Else >>
 << Skip Record If >>
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Part 3: Attach Query To Merge Doc
1) Click Open Data Source Button on Mail
Merge Tool Bar
2) Browse and find Query
Word – Pika Training
© August 2006, Pro Seniors, Inc.
See: www.proseniors.org
Medicare Review
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Part 4: Create a Network Global
Word Template
1) Create a Word Global Template
2) Create a custom Word Menu drop-down and
save it in the global template
3) Save the global template on the terminal server
in:
C:\Program Files\Microsoft Office\
OFFICE11\STARTUP
4) Now all network users will have the custom
Word drop-down menu
Word – Pika Training
© August 2006, Pro Seniors, Inc.
See: www.proseniors.org
Medicare Review
Word – Pika Training
© August 2006, Pro Seniors, Inc.
See: www.proseniors.org
Medicare Review
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Part 5: Use Simple Macros to
Automate the Process
1) Open the Word Global Template
2) Create a simple Macro to access a copy of the
mail merge doc
3) Store the Macro in the global template
4) Assign the Macro to your custom Word dropdown menu
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Part 5: Use Simple Macros to
Automate the Process
5) Simple macro to open copy of main merge doc:
Documents.Add Template:= _
“C:\Word Pika Training\Admin Menu.dot", _
NewTemplate:=False, DocumentType:=0
6) Create macros by recording keystrokes
Word – Pika Training
© August 2006, Pro Seniors, Inc.
See: www.proseniors.org
Medicare Review
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Helpful Hints
1) Link to Microsoft Office Online – Excel for help
with accessing and configuring MS Query:

http://office.microsoft.com/enus/assistance/hp051902761033.aspx
2) Link to MySQL 5.1 Reference Manual to find
formulas to use with MS Query:

http://dev.mysql.com/doc/refman/5.1/en/index.html
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Helpful Hints
3) While in MS Query, press F1 or click the Help
link to access the MS Query manual.
4) Always save your query as its own document in
order to access it later from Word.
5) Save the Excel doc, the Query and the Word
main merge doc using the same name, in the
same folder for ease of reference later.
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Helpful Hints
6) Have users lower Macro Security Settings so
they can run your macros and add-ins.
7) Fix Windows Registry for each user to avoid
the “Opening this will run SQL command” popup warning in Word.
8) Fix Windows Registry to avoid same warning in
Excel when bringing Pika data from Excel to
Word.
Word – Pika Training
© August 2006, Pro Seniors, Inc.
Helpful Hints
9) Use Alt + F8 to access the Microsoft Visual
Basic window.
10) Then use F1 to access Visual Basic Help which
has lots of examples and references.
11) This is a link to Microsoft Visual Basic
Developer Center which has learning tools and
references: http://msdn.microsoft.com/vbasic/.
Word – Pika Training
© August 2006, Pro Seniors, Inc.