Resolving The Problem

Starting in V6R1, the Excel data transfer add-in is now a COM (Component Object Model) based tool rather than using the older Excel SDK (Software Developers Kit). The add-in does not get added automatically into Excel during the installation of the System i Access client as seen in older versions of iSeries Access. Rochester Support Center technote N1013533 COM-Based Microsoft Excel Data Transfer Add-In for V6R1 and Beyond describes how to add the new COM-based add-in to various versions of Excel. This process can be automated fairly simply with a VB script to add the appropriate registry entries. This vbscript can be run after the installation of System i Access to ensure that the add-in is available without having to launch Excel and manually add the add-in. The script below is also available on our public FTP server: ftp://public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/AddInScript.zip

Because VB scripts do not need to be compiled, the following text can be saved to a file with a .vbs extension and used directly:

'License and Disclaimer
'
'This material contains IBM copyrighted sample programming source code.
'IBM grants you a nonexclusive license to use, execute, display,  reproduce,
'distribute and prepare derivative works of this sample code.  The sample code
'has not been thoroughly tested under all conditions.  IBM, therefore, does not
'warrant or guarantee its reliability, serviceability, or function.  All sample
'code contained herein is provided to you "AS IS." ALL WARRANTIES, EXPRESS OR
'IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
'MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE HEREBY DISCLAIMER
'
'Copyright
'
'(C) COPYRIGHT IBM CORP. 2002
'ALL RIGHTS RESERVED.
'US GOVERNMENT USERS RESTRICTED RIGHTS -
'USE, DUPLICATION OR DISCLOSURE RESTRICTED
'BY GSA ADP SCHEDULE CONTRACT WITH IBM CORP.
'LICENSED MATERIAL - PROPERTY OF IBM

'This script will add the COM-based Excel data transfer add-in that
' is part of the V6R1 and later clients.  


Const HKEY_CURRENT_USER = &H80000001

strComputer = "."
Set StdOut = WScript.StdOut
'On Error Resume Next

Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\Microsoft\Office\Excel\Addins\DataTransfer.Addin.1"
oReg.CreateKey HKEY_CURRENT_USER,strKeyPath

strValueName = "LoadBehavior"
dwValue = 3
oReg.SetDWORDValue HKEY_CURRENT_USER,strKeyPath,strValueName,dwValue

strValueName = "CommandLineSafe"
dwValue = 0
oReg.SetDWORDValue HKEY_CURRENT_USER,strKeyPath,strValueName,dwValue

strValueName = "FriendlyName"
strValue = "Data Transfer Excel Add-in"
oReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue

strValueName = "Description"
strValue = "Data Transfer Excel Add-in"
oReg.SetStringValue HKEY_CURRENT_USER,strKeyPath,strValueName,strValue