This document describes how to create an Excel macro to run a data transfer download and copy the resulting data into the active Excel workbook.

The IBM i Access for Windows client includes ActiveX objects for data transfer. While you cannot use these objects to load transfer requests that were saved using the Excel data transfer Add-in, these ActiveX objects can still be used to automate a download into a workbook using a VBA macro.

You should do the following to make this work:

1. Create a transfer request to download the data you want into a BIFF8 Excel spreadsheet file and save the transfer request as dl2xl.dtf.
2. Create a macro in Excel in the workbook that you want to view that data in.

The macro code is shown below. The code creates an IBM i Access for Windows ActiveX data transfer download request object. It then loads the saved transfer request into that object and runs that transfer. When the transfer runs, it creates (or overwrites) the file "C:\Users\Public\Documents\dl.xls". Next, the macro opens the workbook dl.xls, selects the cells with data in them, and copies the cell contents to the clipboard. It then activates the original workbook, sets the "Results" sheet to be the active worksheet, and pastes the data into that sheet starting at cell A1. Finally, the macro clears the clipboard contents and closes the dl.xls file.  Before you can use the ActiveX library, you must add a reference to it.  In the Excel VBA editor select 'References...' from the 'Tools' menu.  Then select the 'IBM i Access for Windows ActiveX Object Library'.  

Sub TransferToRequestSheet()
    Dim dlr As New cwbx.DatabaseDownloadRequest
    ' Data transfer request dl2xl.dtf downloads data from the IBM i into dl.xls, a Biff8 spreadsheet
    dlr.LoadRequest "C:\Users\Public\Documents\dl2xl.dtf"
    dlr.Download
   
    ' this opens dl.xls and copies its data into the desired sheet of our Excel workbook
    Set src = Workbooks.Open(Filename:="C:\Users\Public\Documents\dl.xls")
    ActiveSheet.UsedRange.Copy
    ThisWorkbook.Activate
    Sheets("Result").Activate
    Range("A1").Select
    ActiveSheet.Paste
    ' clear the clipboard contents and close the intermediate file
    Application.CutCopyMode = False
    Workbooks("dl.xls").Close
End Sub