Troubleshooting Excel and BarTender

Overview

Note:
If you recently updated Windows and are experiencing Error Message #6670, please see this article: Error Message #6670 when using Microsoft Excel

BarTender is able to connect to any type of database, including Excel, that uses an ODBC or OLEDB connection.  ODBC and OLEDB connections allow applications to connect to databases and query them for information by using a database connection driver.  

When BarTender initiates a connection to an Excel file, it tells the driver to search for the file in a particular directory.  If the driver has permission to access the file, it will then attempt to open it.  Once the file is open, the driver will send the Excel table data back to BarTender, where you can use it as data for your BarTender document.

 

 

Gather Error Messages

The first step to troubleshooting any issue is to check for useful error messages that may pinpoint the course of action needed to resolve the issue.

 

To check for error messages:

Retrace your steps to reproduce the error message, such as:

  • Open the BarTender document that is experiencing an issue.
  • From the BarTender File menu, select Database Connection Setup.
  • From the BarTender File menu, select Print > Print Preview.

We recommend taking a screenshot of any error message and saving a copy for reference.

 

 

Check if Excel File is Open / Permissions

You may not be able to connect to the Excel file because another user on the network has it open and, if you’re using a revision control system, checked out. Alternatively, the file may be locked by Windows Read and/or Write permissions for the file itself, or for the folder where the file is stored. To resolve these issues, request that the other user close the file and check it in, or obtain proper Windows permissions to access the file.

 

Symptoms

You get the following error message:

  • Record Set: <name> encountered a Connection error.

    Message: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Solution:

Check whether or not the Excel file is in use by another user.

  1. Open the Excel file directly in MS Excel.
  2. If the file is checked out by another user, You will see the message "File in Use" by: "<User>". <User> is the Windows user account that currently has the file open.
  3. Contact the user and verify that they close the Excel file.
  4. Close and reopen BarTender.
  5. Re-create the connection to the Excel file using the Database Connection Setup wizard.

 

Check Windows permissions for the Excel file and verify that you have read access.

  1. Browse to the Excel file in File Explorer or Windows Explorer.
  2. Right-click on the Excel file and select Properties to open the corresponding dialog.
  3. In the Properties dialog, select the Security tab.
  4. Add your user account and make sure it has Read permissions checked. You may need to contact your IT administrator to accomplish this.
  5. Close and reopen BarTender.
  6. Re-create the connection to the Excel file using the Database Connection Setup dialog.

 

 

Install the Latest Database Connection Driver from Microsoft

You will be unable to connect to the current Excel formats (.XLSX) if you have an older driver installed. To resolve this issue, download and install the latest database connection driver from Microsoft. (See the first symptom, below)

You will be unable to connect to an Excel file if Microsoft Office is not installed. To resolve this issue, download and install the latest database connection driver from Microsoft. (See the second symptom, below)

Note:  If an older driver is installed it may be possible to use "Excel 97-2003 Workbook (*.XLS)" and get back into production right away.

  1. Open the Excel file.
  2. From the Excel File menu, select Save As.
  3. Change the Type to "Excel 97-2003 Workbook (*.xls)".
  4. Save the file.

 

Symptoms:

You get one of the following error messages:

  • External table is not in the expected format.
  • Microsoft Excel Driver Not Found
    In order to connect to Microsoft Excel files, you must have an appropriate OLEDB driver installed on the system.  This is included with Microsoft Office, or can be downloaded separately (search for Microsoft Access Database Engine).

Solution:

Download and install the AccessDatabaseEngine2010

  1. Download AccessDatabaseEngine.exe from Microsoft.
  2. Run AccessDatabaseEngine.exe.
  3. Click through the installation wizard.
  4. Close and reopen BarTender.
  5. Re-create the connection to the Excel file using the Database Connection Setup wizard.

 

 

Match BarTender and Microsoft Office Architecture (32 / 64 bit)

Software can have different architecture (32 bit or 64 bit). If you are receiving this error and Microsoft Office is already installed, it means the architecture of BarTender and Microsoft Office do not match.  Reinstalling BarTender to match the architecture to Microsoft Office should resolve this issue.  

 

Symptoms

You get the following error message:

  • Microsoft Excel Driver Not Found
    In order to connect to Microsoft Excel files, you must have an appropriate OLEDB driver installed on the system.  This is included with Microsoft Office, or can be downloaded separately (search for Microsoft Access Database Engine).

Solution:

Download the correct installer

  1. Open BarTender.
  2. From the Help menu, select About.
  3. Note whether architecture is 32-bit or 64-bit
    ie Version: 11.0.1.3056 64-bit.
  4. Download the BarTender installer that matches your Microsoft Office architecture:
    1. Download BarTender 32-bit
    2. Download BarTender 64-bit

 

Uninstall BarTender 

Installing a different architecture of BarTender on top of your current installation may cause issues. We recommend that you uninstall the incompatible version of BarTender before installing the correct version.

  1. From the Windows Control Panel, navigate to Uninstall or change a program.
  2. Select BarTender and click Uninstall.

 

Reinstall BarTender

  1. Run the BarTender installer you just downloaded.
  2. Click through the installation wizard.
  3. Close and reopen BarTender.
  4. Re-create the connection to the Excel file using the Database Connection Setup wizard.

For more information on BarTender architecture, refer to the 32-bit and 64-bit BarTender white paper. In some cases it may not be possible to match the architecture for BarTender and Microsoft Office. If this is the case, please contact Technical Support for more information.

 

 

Use a Different File Type / Database Connection

While this isn't a "fix" for using Excel, it may provide a workaround that will resolve the issue without any further troubleshooting.  

BarTender supports a native connection to text files, so switching from Excel files to text files will circumvent any issues present in the Excel database connection.

An Excel file can easily be converted to a text file delimited format (such as a .CSV) and used as the database instead.

 

Solution: 

Save the Excel file as a text file

  1. Open the Excel file outside of BarTender.
  2. From the Excel File menu, select Save As.
  3. Change the file type to a text file option such as .CSV.
  4. Save the file.

 

Connect BarTender to the text database

  1. Open the BarTender document.
  2. From the BarTender File menu, select Database Connection Setup.
  3. In the Database Setup wizard, select the Excel file.
  4. Click the red "X" to delete the connection to the Excel file.
  5. Select Add a new Record Set (database connection).
  6. Use the Database Connection Setup wizard to connect to the .CSV file
Tip: For instructions on using the Database Connection Setup wizard, refer to this short video.

 

 

Still Not Working ... Contact Technical Support

If the issue persists please open a ticket with Technical Support and include the following information to help us provide a quick response:

  • The Product Key Code or Support Number (BarTenderHelp > About)
  • A complete description of the problem
  • Steps you have already taken to resolve the issue
  • Your BarTender document (.BTW file)
  • The Excel file you want to connect to the BarTender document (.XLS or .XLSX)

 

 

Was this article helpful?
1 out of 2 found this helpful
Have more questions? Submit a request

Comments

Please sign in to leave a comment.