After many months in the making and an extensive beta test we are pleased to release our latest addition to our line of Microsoft Office productivity add-ins.
As its name implies, PowerMerge is a “mail merge” utility for PowerPoint. It is used to create presentations from a template and data contained in a workbook created with Excel.
Learn more | Download an evaluation version | Purchase PowerMerge
I have been blogging about PowerMerge lately but, where is it?
In a nutshell, the program was finished about a month ago and it seems to work quite nicely (see my previous post to see what it is capable to do). There are just two small issues pending:
Anyway, if you are interested, drop me a line and I will provide you with a link to download the product. I am sure you will like it.
Edited December 9, 2009: PowerMerge has been launched:
Learn more | Download an evaluation version | Purchase PowerMerge
This is the continuation of the Case Study showing how to use PowerMerge and QueryCell to automate the creation of personalized presentations using PowerPoint and data contained in Excel.
First of all, I create a template for the presentation that will be sent along with the email. This presentation will contain our common corporate presentation material, things like who we are and contact details, but we want to really focus on the things that really interest our partners:
This year we are going to participate in nine events. The marketing department has already filled in a spreadsheet that contains the most important information about each event, including a picture with the event logo and a plan of the exhibition center, with our booth location clearly marked.
I will present the event information in two slides:
The first slide contains four text variables: Event, Date, Centre and Location which will be substituted with the information contained in the Events workbook. It has also an image field which will be substituted with the logo for each event.
There is also a strange looking field:
[IF|REGION=[REGION]]
This is a little trick. I have a field named Region (note that fields in PowerMerge are case insensitive) on the data sheet. PowerMerge will replace the value of the Region field for each event so that, for example, in the first case, it will be replaced by:
[IF|REGION=North East]
And that is a “conditional” field. On my final merge, only partners in the North East region will receive information about this particular event.
The second event slide contains details about the physical location of our booth on each event:
I have used another little trick of PowerMerge. I want the first and second slides for each event to be “grouped” together. Therefore, on the first slide, I have checked the “Keep with Next” option.
Now, I have to merge my template presentation with the Events data sheet.
PowerMerge has two modes of operation:
In this case, I need to make a “Combined merge”. After a few seconds, I get my new presentation containing all the information of the nine events:
As you can see, both slides corresponding to each event have been grouped together, just like I wanted.
Remember the conditional field trick I told you before? PowerMerge has made the substitution, as can be seen below:
Now that the events part has been solved, I will create one slide for each of our new releases. With more than 60 new books this season, doing it manually would take me the better part of one day. With PowerMerge it will take less than a minute.
As before, I have already the books information stored in a workbook, named, Books:
And I have a slide in my template presentation that uses these fields:
This is a piece of cake. As before, I have a number of text fields, an image field to show the cover of each book and I use a similar trick for the conditional field. This time I am doing it a little differently. Each of the libraries can either carry or not each category, so I will have to create a field for each possible category.
It turns out that PowerMerge has two kind of conditional fields. The first type is the one we saw before, where a record has to have a particular value for the slide to be included.
The second kind of conditional field omits the value. When PowerMerge sees a conditional field, like [IF|FIELD], it will check for each record if the value for the field is empty or not. If it is empty, the slide will not be added to the merged presentation.
I have to make another “Combined Merge” to create a slide for each book. The result is shown below:
Wow! I am already loving PowerMerge.
Allan and Wilson books is going to be presenting information about their new titles to distributors and book stores all around the country. Read how PowerMerge and QueryCell help them do it quicker, easier and with less mistakes.
My name is Jenny Malhouse and I’m the general IT manager at Allan and Wilson books. When I say IT Manager I mean manager, developer, administrator, innovator and dogsbody. Since we are a small ( but growing ) company everyone has to pitch in where they can. I find myself handling most of the computer issues that crop up.
Over a few months our editors are going to be traveling around the country presenting our new releases to resellers and book stores. I’ve been asked to email out our New Books This Season PowerPoint presentation in advance.
I’ve been given a list of email addresses but it’s not going to be that simple!
We only want to send the presentation to partners in cities that are going to be hosting an editor event. If we sent the PowerPoint presentation to the rural stores that our editors are not going to get time to visit we might cause unnecessary offence.
Some of our partners are only going to be interested in certain categories of our range. There is no point in sending the slides on romantic fiction to the military history book store in Boise Idaho.
The list of Partners, Events and Categories are all in separate Excel worksheets, I could try and sort it all out by hand but luckily I’ve just installed QueryCell, an Excel Add-In that lets me use SQL inside Excel, it’s really going to speed things up.
I will need a list of all the partners who are in a region that will be hosting an event and the categories they are interested in.
Once again I could manually look through the data, copying and pasting information as I find it but it’s going to be a lot simpler to just use a simple SQL query and QueryCell.
A simple SQL query returns the email addresses of all the partners near an event and their categories of interest
The query we ran :
SELECT distinct
a.NAME,
A.Region,
B.Category
FROM
a, b, c
WHERE
a.NAME = B.NAME AND
a.REGION = C.Region
Great, now I have a list of the partners near events and the book categories they are interested in!
Since all my querying has taken place inside Excel using QueryCell I still have the all the standard Excel functionality available at the click of my mouse.
I am going to use an Excel Pivot table to reorient my data and make it easier to deal with.
Instead of having all my book Categories listed in a column I now have them as a single row, with a count of 1 indicating that the listed partner is interested in that category.
With a small amout of manual clean up and formating my table of data is ready to be used to generate customized powerpoint presentation.
The data is now ready for PowerMerge to create my customized emails!
Before I jump into PowerMerge and produce my customized power point presentations I am going to do one more thing in Excel.
Janette our editorial communications coach has asked for a list of all the partners that will be receiving a presentation and are interested in Inspirational or Mystery books.
She just needs a list of their names so she can give them a call and pass on some special information from our suppliers.
I could look through the table of data I have just produced manually but using querycell I can get that information easily in seconds.
I check the ‘Mark Results’ checkbox in QueryCell. This tells QueryCell that I do not want all the data matching my query returned, instead I simply want the matching rows colored.
I then write a very simple query to select only those rows where the Inspirational column or the Mystery column contains a 1.
Immediately I see the rows I am interested in. It’s now a snap to select those names and email them to Janette.
Now I can return to generating my PowerPoint presentations.
See next post
On my previous post, New interface for Office 2003 add-ins, I commented that I was excited by the prospect of using the exact same UI in PowerPoint 2007 and previous versions. In fact, I have been able to implement this new concept in my upcoming add-in, PowerMerge in just a couple of days. Great for me!
But wait, "the devil is in the details" they say. And I have discovered that it is not as easy as it seemed at first.
My new interface works like a charm if my add-in is the only one using the advanced task panes feature. But, as I said, I plan to implement this UI in all my add-ins. So what happens if PowerPoint, or Word, are running two or more add-ins that use this "ribbon"?
At the top, you can see the nicely formatted "ribbon" that closely resembles the true ribbon of PowerPoint 2007.
At the bottom, there is how this same "ribbon" would look if a second add-in tries to place its own "ribbon". A header is drawn on top of the task pane to let the user select the appropriate "ribbon" to work with. Unfortunately the task pane height is not modified and the top of the "ribbon" is effectively hidden by the header.
The effect is not nice. So, I have to work out a solution. Time to contact Add-in-Express support again.