IBM i Access For Windows Data Transfer and Excel 2013 and beyond

Resolving The Problem

This document includes problems that have been reported when using the Excel data transfer add-in starting with Excel 2013. Never versions of Excel may also experience the same problem. IBM development has chosen not to rewrite the System i Access for Windows 7.1 data transfer add-in so that it will conform to the changes starting in Excel 2013. Therefore the data transfer add-in is not supported when using Excel 2013 and beyond.

Later releases of Office 365 use Office 2013, so we expect you will see the same results with Office 365.
IBM also does not support Office 365 which has been upgraded to Office 2013 and beyond.

The strategic direction is to use an alternative to IBM i Access for Windows data transfer, the new Access Client Solutions (ACS). This client can be run in addition to, or instead of, the IBM i Access for Windows client. It includes a data transfer feature that will transfer the data to or from the most recently opened Excel spreadsheet. More information on this client can be obtained from the product home page: http://www-03.ibm.com/systems/power/software/i/access/solutions.html

Following are problems which have been reported when using the data transfer add-in starting with Excel 2013. Some circumventions are also provided although there may be other options within Excel 2013 and beyond that we are not aware of.

PROBLEM: The data transfer add-in for Excel will not work in anything but the first spreadsheet opened in an instance of Excel 2013 and beyond.

The problem can be circumvented but there are no plans to rewrite the data transfer add-in to conform with the way Excel 2013 and beyond is handling windows. Following is a circumvention which can be used instead of simply not opening more than one spreadsheet at a time.

The option is available to launch Excel differently by adding a /X parameter to the start of the program. This will cause additional launches of Excel to start their own instance (their own process, not simply more threads in the same process). You can do this by finding the program Excel.EXE and creating a shortcut for it and adding the /X after the existing shortcut target.

For example, if Excel.exe is found in the path "C:\Program Files\Microsoft Office\Office15\EXCEL.EXE", you can create a shortcut to Excel.exe on your desktop and then modify the properties of the shortcut so that the target is now: "C:\Program Files\Microsoft Office\Office15\EXCEL.EXE" /X

Notice that when launched this way, you will see multiple Excel applications on your toolbar and in the task manager. In order to be able to run a transfer in anything but the first spreadsheet of the first instance of Excel, you will need to launch a new instance of Excel (in other words, simply clicking on File New is not going to get the job done, you have to start a new instance of Excel using the shortcut you created on your desktop).

Another alternative to the data transfer add-in is to query data using ODBC or OLE DB. This will work to retrieve data from IBM i, but is not a substitute for transfer to IBM i. Microsoft's documentation should be consulted for the details on the various ways to do this. The options can be accessed from the Data ribbon in Excel 2013. You can create a data connection using OLE DB or ODBC or create a query through Microsoft Query which will make an ODBC connection and allow you to use the graphical query creation tool to define the query. These options have the advantage of embedding the query and connection information in the spreadsheet. This way you can simply refresh the data to run the query again and have updated data.


PROBLEM: The data transfer add-in does not work if the spreadsheet was opened in a protected view.

This problem is essentially the same as the one above, when a spreadsheet is opened in a protected view, the user has to click an option to enable editing. When that happens, Excel appears to open a second copy of the spreadsheet and close the first one. The Add-ins will be visible but tracing proves that when they are clicked on, Excel does not invoke our Add-in.

This can be circumvented two ways. The first is the simply close the spreadsheet after clicking the option to enable editing without modifying the sheet in any way then immediately open it again. The second time it is opened, the file will not be opened in protected mode (note this may be a defect in Excel, one would think it should be opening in protected mode).

The other option to circumvent this is to disable the use of protected views. To do that perform the following steps in Excel: Click on File - Options - Trust Center - Trust Center button - Protected View - uncheck the first 3 options from the right side.


PROBLEM: The data transfer add-in no longer works after closing Excel 2013 (and beyond) and reopening a new instance of Excel.

When this problem occurs, the data transfer add-in icons are still available in Excel, but clicking on the icons do nothing. We have found that this problem occurs if the user on the PC is signed on using a roaming profile. If the user signs on with a local account the add-in then works when closing and reopening Excel.


PROBLEM: The data transfer add-in does not show up in Excel 2013 and beyond even though it shows it is installed when viewing the add-in manager.

If you browse and add the transfer add-in again, it works until you shut down excel. When you start Excel again, the add-in is missing again. Running the repair feature (Control Panel, Programs and Features, right-click on Microsoft Office, select Change and then select Repair in the resulting dialog and press OK to run the repair) corrected the issue.