COM-Based Microsoft Excel Data Transfer Add-In for V6R1 and Beyond

Technote (troubleshooting)


Problem(Abstract)

This document explains how to add and how to remove the COM-based Microsoft Excel data transfer add-in.

Resolving the problem

Starting with V6R1 System i Access for Windows, the Excel data transfer add-in no longer uses Excel SDK. The newer Excel 2007 limits are not supported in the old SDK. The V6R1/V7R1 Excel data transfer add-in uses the Component Object Model (COM) instead of SDK. COM is the interoperability standard for Windows-based applications and allows users to upload data from many Excel instances simultaneously.

When installing the COM-based Excel data transfer add-in, CWBTFCAI.DLL is used instead of CWBTFXLA.XLL.

Following are instructions for installing the COM-based Excel data transfer add-in. Data transfer can identify which Excel version is installed and set the corresponding limits.

Excel 2002/2003 Loading the COM-based Add-in

1. On the View menu, select Toolbars -> Customize...:

This picture shows the menu options used to load the COM-based add-in.


2. Click the Commands tab and under Categories, click Tools.

This picture shows the options available when selecting the customize option in the previous picture.


3. Under the Commands tab, click COM Add-Ins... and drag your selection to the Excel toolbar:

This picture shows the COM Add-in option.


4. Click COM Add-Ins on the toolbar and select Data Transfer Excel Add-in:

This picture shows the COM Add-in Data Transfer Excel Add-in.

5. If the Data Transfer Excel Add-in does not appear in the COM Add-ins list, do the following:

a) Click Add (see previous screen).
b) Browse to the folder where you installed System i Access for Windows (default path C:\Program Files\IBM\Client Access\Shared).
c) Double-click the Shared folder.
d) Double-click cwbtfcai.dll.


Excel 2002/2003 Removing COM-Based Add-In

To unload the data transfer add-in (keeps name in the list), clear the box for the Data Transfer Excel Add-in and select OK.

To remove the data transfer add-in (removed from list), select the box for the Data Transfer Excel Add-in and select Remove:

This picture shows how to remove the COM add-in Data Transfer Excel Add-in.


Excel 2007/Excel 2010: Load COM-Based Add-in

Note: Data transfer is a 32-bit application and will not work with the 64-bit version of Excel 2010 until 64-bit support was added starting with i Access for Windows V7R1 service pack SI42424. No Excel data transfer add-in 64-bit support is planned for earlier versions of the client.

Also, Excel 2013 is not supported. To view the support statement and some circumventions when using Excel 2013 see technote N1019966 IBM i Access for Windows Data Transfer and Excel 2013.

1. Select the Quick Access toolbar in the upper left corner of the Excel session when using Excel 2007:

This picture shows the Excel 2007 Quick Access toolbar.

When using Excel 2010, click on File and then Options to take you to the Excel Options window where you can select Add-ins (shown in point 3 below).

2. Select Excel Options when using Excel 2007:

This picture shows the Excel Options button that will show the Add-ins option.

3. Select Add-Ins:

This picture shows the Add-ins option that can be selected for the list on the left.

4. From the Manage list, select COM Add-ins and then select Go...:

This picture shows the COM Add-ins option under the Manage drop down options.

5. Select Data Transfer Excel Add-in:

This picture shows the COM Add-ins Data Transfer Excel Add-in.

6. If Data Transfer Excel Add-in is not listed, do the following:

a) Select Add (see previous screen).
b) Browse to the folder where you installed System i Access for Windows.
c) Double-click the Shared folder.
d) Double-click cwbtfcai.dll.

Note: When using a Windows 64-bit operating system such as Windows 7 64-bit, the path by default will be \Program Files(x86)\IBM\Client Access. If using Office 2007 or Office 2010 32-bit, select the Shared directory under Client Access. If using Office 2010 64-bit, select the Shared64 directory. Then select cwbtfcai.dll. A minimum of System i Access V7R1 with service pack SI42424 is needed if using Office 2010 64-bit.

Browsing for the data transfer add-in per item b) above:

This picture shows the Windows path to the Data Transfer Add-in if it is not in the previous list.

7. The data transfer add-in download and upload options will then be shown under the Add-ins drop down menu or tab in Excel 2007/2010.

This picture shows where the add-in icons reside in Excel.


Unloading the COM based Excel Add-in

To unload the data transfer add-in from Excel do the following:

1. Select the Quick Access toolbar.
2. Select Excel Options.
3. Select Add-ins.
4. In the Manage list, select COM Add-ins and click Go.
5. Clear the checkbox associated with Data Transfer Excel Add-in.
6. Select OK.

This picture shows how to unload the COM Add-ins Data Transfer Excel Add-in.


Removing the COM-Based Excel Add-in

1. Select the Quick Access toolbar.
2. Select Excel Options.
3. Select Add-ins.
4. In the Manage list, select COM Add-ins and select Go.
5. Highlight the checked Data Transfer Excel Add-in.
6. Select the Remove button.

This picture shows how to remove the COM Add-ins Data Transfer Excel Add-in.

Historical Number

495332479

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

IBM i
Data Access

Software version:

6.1.0, 7.1.0

Operating system(s):

IBM i

Reference #:

N1013533

Modified date:

2013-04-22

Translate my page

Machine Translation

Content navigation