David’s Tech Tip – Make BarTender and Excel Behave

BarTender softwareIf you read Labeling News regularly, you’ll have noticed that I’m a fan of BarTender, the label design and printing software. As well as the fact the product is great (if you buy printed labels from Winco, we’ll have made them using this software), I also like dealing with the company.

Having said that, every now and again BarTender drives me crazy. Curiously, nearly all the issues I have are related to one thing – connecting to Excel.

Let me say, I fully understand that Excel isn’t a proper database and I’d have much more success using a “real” database program, like Access or SQLServer. Here’s the deal though, I’m not a database guy and I have no idea how to properly use these programs. Most of our customers are the same, it is easy for them to send us label data in Excel.

So what are the problems?

First, there are many times (especially recently) when BarTender just refuses to connect to a spreadsheet at all – it happy serves up all kinds of errors at different stages of the connection process.

The other problem I have is that when I change the attached spreadsheet in one of my labels to a new one, the link doesn’t seem to be formed correctly and I have to manually click every field to remap it to the data source. When you consider that an MSL label can have 50 or 60 data fields, that is not a good use of time.

To look for solutions to these problems, I headed over to the User Forums at the Seagull Scientific site. There is a lot of great information there and I quickly discovered that I’m not the only person to have struggled with these problems and there are solutions offered.

For the failing to connect to the spreadsheet problem, these are the suggestions offered.

  1. Make sure the Excel file is closed when trying to make the connection. OK, we all know that one but it is worth checking.
  2. If the spreadsheet was made in Excel 2007 or 10, save in .xls format, not .xlsx. I’m not sure if this one is a real fix, I had the same problems using both formats. Actually, I have most problems of all on a PC that is opening .xlsx files in Excel 2003 in Microsoft’s converter.
  3. Copy the whole spreadsheet and paste into Notepad. Recopy from Notepad and past into a new Excel file. I’ve never tried this one, but the feedback on the forum is that it can solve the problem.
  4. Convert the spreadsheet to a CSV file. This fix works 100% but adds more work to the customers to be able to print their labels.

So trying all of these should get BarTender and Excel to talk together, but it would be nice if it just worked.

For the second issue; getting the fields to properly connect to the new spreadsheet, things are easier. I’ve always tended to delete the old database and use the wizard to connect the new one.

Not so fast, says Gene H, from Seagull Support. Don’t delete the connection, just change the path to point to the new spreadsheet – like this:

  • Open the label format
  • Go to File > Database Connection Setup
  • Go to the Options tab of your current connection
  • Click the Setup Button
  • Put the path to your new file in the Data Source box

The label should then work for you without re-linking the label objects (assuming your fields names have not changed).

I’ve tried this a few times and so far it works fine. Thanks for that, Gene!

If you use BarTender for your label design and printing, there is a ton of good info in the Seagull Forum. Well worth checking out.

Not using BarTender? Hey I can help you with that!

Share Button

Comments

  1. art cantillep says:

    can i do that source redirection programmatically instead of manually?

  2. David Holliday says:

    Arturo – I’m sure you can. I’m not a programmer so I tend to stick to very simple solutions.

  3. This article is even more relevant now that a subscription to Office365 Business no longer includes Access. You HAVE to work in Excel, now.

  4. David Holliday says:

    Yes, and now have to contend with all that 32 vs 64 nonsense.

Speak Your Mind

*