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
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.
As I have mentioned before, I was intending to replicate the look and feel of the ribbon interface in previous versions of Word and PowerPoint.
After working on this idea, I think I have got a good solution to implement the idea as can be seen in the screen shot below:
The upper half is the ribbon tab of PowerMerge, as seen on PowerPoint 2007.
The bottom half is my new "custom" ribbon tab, as it looks on PowerPoint 2003 (and older versions).
As you can see, the effect is not perfect, but sufficient for my purposes, especially since the PowerPoint 2003 users can’t make a direct comparison between my ribbon and Microsoft’s ribbon.
Now that I think about it, it is funny because people hated the ribbon so much that a product was created to bring back the menus and toolbars of Office 2003 to 2007 while I am doing exactly the opposite.
To be frank, in the particular case of PowerMerge, probably I could make it work using just the toolbar. But PowerTOC will become much more usable when I implement my new custom ribbon system.
I can confirm that the problem reported in my previous posts has been definitely solved. This means that I will be able to use task panes in all my add-ins, maintaining the compatibility with previous versions (specially 2003, which is still the version used mostly by my clients).
I am thinking also in replicating the ribbon bar interface used by my add-ins in Office 2003 using a task pane.
I personally love the ribbon interface because it allows to group "task" related functions in a very logical and convenient way. Unfortunately I have not been able to take advantage of its full potential because that would have made my add-ins not compatible with previous versions.
For example, I don’t like the ribbon tab used in PowerTOC (shown below). I think that it can be improved substantially to make the application much more intuitive to use.
Compare it with the ribbon of my latest add-in, which is about to enter beta testing, PowerMerge:
And I am sure that I can still do better once I start taking advantage of the possibilities of the ribbon interface.
We have released today officially PowerForms, our new PowerPoint add-in. PowerForms is in fact FormsAssistant for PowerPoint.
With the release of PowerForms we will be able to start fulfilling our plans regarding our Office productivity add-ins line. I explain what is our current vision in the next post.
Meanwhile, enjoy our new product!
Last week it was brought to my attention that my add-ins do not behave correctly on multi-monitor systems. So I had to quickly setup a multi-monitor test system. I tried MaxiVista which is an amazing software product that can magically convert the screen of any computer on your LAN as an additional monitor.
I have already solved the problem and we will release a new version of PowerTOC and FormsAssistant in the next few days.
A typical scenario would be placing a laptop side by side to your desktop and using it as a second monitor. No extra hardware is needed.
Be warned, having two monitors is addictive!
Last week I posted Favorite features of Control Runner (1) explaining how I have been using the ‘Keep on Top’ hot key of Control Runner to work on two different applications at the same time. This post has become obsolete. I am using MaxiVista now to achieve a much better effect.
Today I have been browsing the site of MaxiVista and I have discovered a feature of the advanced version which is a must for serious PowerPoint presenters.
The idea is that you open on your PC your presentation in normal view and setup MaxiVista to send the slide show to another computer, connected to the projector. That way you can see your notes while you are doing the presentation and your audience only sees the slides. Brilliant!