On my consulting business, I do a lot of data sharing between Excel and InDesign.

I know that InDesign tables have very powerful formatting options, but my reports usually contain dozens of interconnected tables that have to be created in Excel (because they contain financial calculations). Excel does provide with enough formatting options to create great looking tables that integrate nicely with the layout of my reports, while InDesign, of course, lacks any calculation power.

Download now Metafile eXpert, or continue reading to know the whole story about effectively working with Excel tables in InDesign.

Placing Excel tables in InDesign. The traditional way

InDesign provides two "modes" of handling the clipboard as shown below. The options are available (in Windows systems) in Edit > Preferences > Clipboard handling …

Paste Text Only

You really should not copy and paste tables created in Excel to InDesign using the Text Only mode. If you don’t follow my advice and do it, the table is converted to text, with each row in a line, and columns separated by tabs. This is hardly useful.

It is necessary to completely reformat the table in InDesign, and merged cells usually create problems. But the worse news is, whenever the original data changes (and believe me, it will), you have to copy, paste and reformat it again from scratch. Definitely a no-no.

Paste All Information

Ok, so what happens if you try the first option, ‘Paste All information’. That looks more promising.

In this case, what InDesign does is to try and convert the Excel table into an InDesign table, thriving to  retain all formatting information it can.

As seen above, if you take the precaution of hiding gridlines in Excel, the result is no so bad.

[BTW, I always hide the gridlines anyway, because I find they are distracting when working with Excel, so it is not a big deal for me.]

InDesign really does a great job of converting the Excel table into a InDesign table. There are still a few glitches, like the width and height of cells not being maintained, the vertical alignment being odd and, again, problems with merged cells. So, even if the result is fairly good, you will have to manually tweak and adjust the conversion. And this means that you will have to reformat tables again whenever the underlying information changes. Not really a viable solution.

Using the metafile clipboard format

The problem lies in that, even though Excel pastes to the clipboard the data in mutiple formats, InDesign only uses two of these formats: The internal Excel clipboard format to create an InDesign table, or the text only format, to create a tab delimited text object.

But there is a third way which involves using the metafile format. What is a metafile? Is a vector file format that has been used in Windows, at least since Windows 3.0. Being a vector format means that it has maximum quality. And it provides a really good rendition of the original formatting of the spreadsheet because it is the format that is used by Excel itself to print a spreadsheet on some printers.

The funny thing (so to speak) is that InDesign does understand metafiles (in fact, most applications do) but it ignores it unless it is the only available format.

Excel provides a little known trick to remove all other clipboard formats and publish only a metafile. How do you do it? To put it mildly, it is not terribly intuitive in Excel 2007. The following screen shot shows how to do it:

• Select the range you want to copy.
• Click on the little arrow below the Paste button
• Select the last option ‘As Picture’.
• A new dialog will be shown with a few options.

You will get better results selecting the ‘As shown when printed’  option. You may have noticed that Excel is not a perfect WYSIWIG ("What you see is what you get") application. Selecting this option means that Excel will effectively compose the metafile as if it were printing the selected range.

Usually, it doesn’t really matter which Appearance option you select.

The Format options are more important. As you can see, when using the ‘As printed’ option you can’t choose a Format, because Excel forces the Picture format for you (remember I told you Excel uses metafiles to print?). But in case you decide to use ‘As shown on screen’, you have to absolutely make sure that you stick to the Picture format (in case you are wondering, picture is a MS synonym for metafile).

You don’t want to use a bitmap. Believe me.

It is fairly easy to create a macro in Excel to replicate all these clicks and selections and copy the selected cells as metafile to the clipboard to paste it afterwards in InDesign. In fact that is what I did in the past, with good results.

Updating metafile tables

What happens when the table changes? Easy, for each table yo need to update, you repeat the process in Excel, then turn to InDesign, delete the old table, paste it again, and back to Excel. Easy … but tedious.

[NOTE: Of course, if you are a brave person, you can try to link the table in InDesign. I am not brave enough myself and have been avoiding using linking since the very day Microsoft invented it, but you might be different]

Introducing Metafile eXpert. A better way to handle Excel tables in InDesign

To streamline the process, a couple of years ago I created a personal quick and dirty utility to capture the metafile format posted by Excel to the clipboard (remember that Excel always publishes metafiles when copying to the clipboard) and save it to disk. Then, I’d "place" the metafile in InDesign, as I would any other external "asset".

Recently I have been investigating the clipboard in more detail for an upcoming add-in I am working with, (the name is WordClip and it is a boilerplate repository for Word). As a result I have greatly enhanced my little metafile utility and decided to release it to the world as a freeware utility. If you work with Excel tables in InDesign you absolutely must get it. And the price is quite reasonable!

You can see below a screen shot of Metafile eXpert.

What’s the deal with Metafile eXpert?

• First of all, it keeps record of your projects and the location of your "assets" folder for each.
• It "monitors" the clipboard. That means that you can copy a bunch of Excel tables without having to move back and forth between Excel, Metafile eXpert and InDesign.
• You don’t have to create a macro to replicate the "trick" I have presented above. The good old Ctrl+C key combination in Excel will get the work done.
• Placing metafiles inside InDesign means that you don’t have to delete and paste again the tables back in InDesign. When you return to your publication, InDesign detects that the metafiles have changed on disk and you can update them all with a few clicks!
• You get a full preview of the tables to ensure that they look like you want, right inside Metafile eXpert. And, by the way, Metafile eXpert, is a truly WYSIWIG application. The preview shows the metafiles exactly as they will print.

So, what are you waiting for?

Please Contact me if you find Metafile eXpert useful. And be sure to let me know your suggestions to make it even more productive.

Where are my metafiles?

Metafile eXpert saves metafiles using the Enhanced Meta Files format, with the extension EMF. When you open the Place dialog you wont see the metafiles, because Adobe has not included EMF as one of the extensions in the default "Importable files" option. Therefore you have to select the "Windows Enhanced Meta Files" in the Place dialog.

This is the third installment of this series. On Part I, I rumbled about the craziness of not providing good technical support, while on Part II and III, I am providing concrete samples of what I consider to be top notch support.

The case of the non-closing PowerPoint application

About a month ago, I discovered that Add-in-Express 2009 has introduced a very cool new feature. The ability to create windows like the tasks panes introduced on Office 2007 in older versions of Office. They call this feature "advanced task panes".

Many of my users are still using Word and PowerPoint 2003. This has prevented me taking full advantage of what I believe is a great interface for add-ins. In the future, most of my add-ins are going to include at least one of these advanced task panes.

For example, below is shown the user interface of my new PowerMerge addin, showing a task pane with the fields used on the presentation.

Right after implementing my very first advanced task pane, I compile and run the add-in and it shows beautifully on PowerPoint. The only problem is that PowerPoint refuses to close. It simply hides itself and continues running in the background.

What technical support was supposed to be

This is no good, so I send an email to Add-in-Express technical support. I recall that at some point in the past, I discovered a bug in their implementation of Office 2007 task panes in PowerPoint, so I ask them if they have checked their new advanced task panes with PowerPoint.

I receive their answer: "Yes, they have checked and they work". And a suggestion: "Maybe there is a conflict with another add-in installed on my system. Can I please check?".

I do check, and indeed there are two add-ins that seem to "cause" the problem. One is the PowerPoint recording add-in of TechSmith’s Camtasia Studio and the other is the add-in for a text-to-speech application. I report that information to them.

The support guy at Add-in-Express downloads the trial of the text-to-speech application and discovers that the add-in is a "feature" of the commercial version. So, he contacts me to ask if I can contact the vendor to explain my problem and ask for a "trial" version of the add-in for them to make their tests.

I, therefore, contact the vendor of the text-to-speech application. In a couple of days, I receive the following answer:

[Name supressed] addin toolbars are stable. You can try again.
Sorry, we have no trail (sic) version of full version.

I won’t comment on this message. But, I’d like to say that I am actively looking for an alternative text-to-speech solution, and I regret the day I purchased that one.

Since the text-to-speech route seems to be a road block, I contact TechSmith to tell them the problem and know what might cause it. The support guy there tells me that he’s spoken with a developer who suspects that the problem is related to their add-in hooking into some of PowerPoint events (as most PowerPoint add-ins do). So, I pass on the information, hoping that it is useful.

I don’t know if Add-in-Express developers have carefully studied their code, or they have installed Camtasia Studio in a test system and debugged PowerPoint. Whatever method they have used, yesterday I received the following message from them:

We have fixed this issue. The fix will be available in the next release.

So, in summary, in the month or so that has taken to sort out this thing, Add-in-Express support staff:

• Have taken full responsibility for the problem.
• Provided a useful suggestion, hours after receiving the first notice of the problem.
• Downloaded the evaluation version of the text-to-speech program, only to discover that the add-in was a "feature" of the commercial version.
• Finally, solved the problem as soon as they found a way to reproduce it.

All of that without ever "scalating" the issue or suggesting that the problem was not their fault since it only occurred when other third party components were present on the system.

Thank you very much, again, Add-in-Express.

Update: Today I have received a notice from Add-in-Express saying that they have a new version available that solves the problem. Hurrah!

This is a continuation of my previous post. The first one was a little negative, so I’d like to present the other face of this issue. There are indeed software companies that thrive to provide a good customer support.

My products use a number of third party components. Of these, there are two that are extremely important to me:

1. The protection system. I am using Oreans WinLicense to protect all my applications and add-ins.
2. The office add-in framework. I am using Add-in-Express as the basis to create all the add-ins.

These two components have two things in common: A great portion of the "hard to track bugs" found on my products are caused by, or related to, these two components. On the other hand, the support provided by these two companies is absolutely top-notch.

I don’t know if there are other products that would perform the same functions for me and are better, or cheaper. I don’t care. Their support is so good that I won’t look for an alternative solution to those two backbone functions. Period.

The Oreans history

I have had many support needs for them. This is not strange because their protection system is likely to cause all kind of conflicts with running applications -as an example, for security reasons, my applications are not allowed to run when a debugger is detected- or cause my executables to be flagged as a virus.

Last week, I received a bug report from a customer that had just purchased PowerTOC. I hate when someone purchases a license, and immediately tells me that the product won’t work. I really feel very bad about it.

To make things worse, the problem was totally new to me. PowerPoint will crash on startup and disable PowerTOC on the next run. This is a serious issue.

I offered a remote assistance session to the customer, which he accepted. Fortunately, just before starting the remote session, the customer discovered that the "culprit" was McAfee HIPS. Disabling it solved the problem.

Time to contact Oreans, I thought. I sent them a message and, in a few hours I received a very detailed answer with a possible solution to this problem. I have followed their advice, and it seems to work.

The Add-in-Express history

This is what originally made me write this series, and will be the topic of another post. Please keep tuned.

From time to time I participate in a discussion with fellow independent software developers about customer support. Over the years, I have come to understand that there are basically three approaches:

1. Those that hate it. Plain and simply.
2. Those who think that it is just another cost of doing business.
3. Those that believe that it is the single most effective marketing device available.

I’ll start declaring that I belong at heart at the third category.

These are strange times indeed

A friend of mine said once that we are living in a time in which you have to fight for the obvious, be it Global Warming, the true causes of the recent financial crisis or corporate strategy.

I am mentioning it, because I am at a complete loss when I see how most big companies are externalizing customer support. It is funny, if it wasn’t really another sad symptom of the current craziness of the world we live in.

One of the first business strategy lessons you learn is that the one who owns the client owns the business.

Yet, those big corporations pay external consultants a bunch of money to be told that they could save a few cents by externalizing customer support.

Of course, then they pay a different kind of consultants another bunch of money to create all sorts of annoying customer surveys to try to regain some knowledge about what their clients think or want. A complete waste of time and resources.

I believe that this creates a huge opportunity for new and innovative companies that still understand that a business is just a way of make money out of solving someone else problems.

A little tale

I have received lately two messages from a user that is totally at a loss about how to use FormsAssistant. The messages are:

First message:

I’ve having a mental block on getting the fill-in functions to work.  Could you please complete an example for me?  I think one simple example could help.

How do I code a field to populate with data (my name).  And, how do I code the clip so that my name will appear?

Second message:

Just to clarify -

If I wanted to use the name entered in "Dear [Name] again later in the letter, would I have to go to that next field and select the name from the available clips?  So, it doesn’t have the capability to populate all [Name] fields with the name / data inserted in the "Dear [Name] field?

Well, it is clear to me that this particular person is not understanding at all how does FormsAssistant work. He must be dumb, doesn’t he?

No, he is not dumb at all. What these messages really mean is that I have not done a good job of explaining FormsAssistant. What is obvious to me, does not mean anything to a person that, as you can see on the two quoted messages, is an intelligent person that does a really good job of expressing himself. So, I have explained FormsAssistant in terms of what I do understand, but have not been able to communicate these ideas with him.

What I have done is to answer politely to both messages, trying to explain better to him what FormsAssistant does.

What I would love to do, is to take the phone and talk to this guy to learn why I have made such a bad job of explaining FormsAssistant on the web site, the user interface and the help file.

And, of course, to thank him personally for expending his valuable time to let me know that I am not the brilliant programmer I sometimes think I am.

You know, on this business, when someone does not understand what your program does in about 10 seconds, a click on the back button of the browser loses you a sale.

I have uploaded a new version of FormsAssistant that includes the following three changes to the product:

• Removed the auto-update system: The auto-update feature has been removed from the product.
• Process and replace fields on headers and footers: A new option has been added to enable using fields inside headers and footers of documents.
• Process and replace fields on text boxes: A new option has been added to enable using fields inside text boxes contained in documents.

Note that the last two features have been added as an option because processing headers, footers and text boxes results in a noticeable delay in the processing of documents.

Please visit the download page to get the new version.

What’s next

This will be the last 1.x version of FormsAssistant. I have just started the development of version 2. Most of the new features have been already implemented and tested, so the new version development will mostly consist in integrating these features on the main application. Therefore I expect beginning the beta test sometime during the next month of September.

I have finally uploaded Control Runner v4.0. Although the program has been finished for at least two months now, several issues (not related to Control Runner per se) have prevented me from releasing it earlier.

For that reason, I want to apologize to all my users that have been contacting me lately wanting to know what was happening.

I have to say that I have been using fairly stable 4.0 versions since the start of the year, so I have almost forgotten how was Control Runner 3.4, but for those of you that have not participated in the beta test, I am sure you are going to love the new version.

How to upgrade

I have decided to provide a free upgrade to all users that purchased the product after 1/1/2008 since I originally planned to release version 4 at the beginning of this year.

Users that purchased Control Runner before that date will have a 60% discount

In the next few days, the new customer portal will be live. This will be the preferred way to get the free upgrade keys or the link to the special upgrade price. Meanwhile, please Contact me to get instructions.

Again, thank you very much for your patience.

The preliminary specification for forms libraries is beginning to take shape. On this post I will present some ideas that .

1. The three different modes of access

FormsAssistant will handle three types of libraries:

• Personal libraries are stored on a user’s computer and are not meant to be shared.
• Shared libraries are stored on a network drive and are supposed to be shared by an entire organization (or at least some users).
• Remote libraries are a mixture of the two. There is a master copy of the library that is stored on a network drive, but local copies of the database are kept on the local disk.

Internally, all three types of libraries are the same, the only difference is the way FormsAssistant accesses them.

Personal and Remote libraries are accessed locally in read mode (i.e. browse and open the forms contained in them). Personal libraries are also accessed locally in write mode (i.e. modify, add or delete forms; restructure categories, etc.).

Shared libraries are accessed remotely for all modes, read and write while Remote libraries are only accessed remotely in write mode and also when checking for updates and update mode.

2. Performance considerations

Personal and Remote libraries are stored locally, and therefore their performance is optimal. Shared libraries performance will, of course, depend on the network performance.

Our suggestion for organizations using shared repositories of forms is to use primarily Shared libraries and only revert to Remote libraries if the browsing forms performance is unacceptable. A typical reason is when the Remote library is not stored in the local network (see below).

3. Sharing considerations

Personal libraries can be shared between different users, but they have to manually copy and update them as needed (of course this process can be automated using an external program that takes care of copying libraries from one system to another).

Shared and Remote libraries are shared by default. FormsAssistant will handle everything for you.

4. Libraries on the cloud

Users of libraries do not need to be physically connected to the same LAN to be able to access them. FormsAssistant will happily work with Web folders to access truly Remote libraries.

In the future, we are planning to offer a hosted solution that will enable you to host your libraries in a centralized location and make them accessible to users all around the globe.

I have just uploaded a new version of FormsAssistant that solves a problem reported by one user. Apparently, on 1.7.5 the multiline editor of the Main window was causing an error.

Please download the new version and install it over your current version of FormsAssistant. It is not necessary to uninstall it previously.

I was interviewed last year at the ESWC Conference held in Berlin in November 2008. You can hear the whole interview at Shareware Radio.

My English accent is horrible, but I believe it can be understood. Enjoy the interview!

Last week, I finally set my new web site live. I have been busy fine tuning it, fixing everything that was broken, but finally it seems to be working alright.

This is how the new site looks:

And this is how it was before:

What do you think? Do you like it better the new one?