Home > IT Answers > AS/400 > Transfer Data from iSeries to Excel
Mblair.au
25 pts.
 Transfer Data from iSeries to Excel
iSeries, iSeries import/export, Microsoft Excel
Hi Everyone, Could someone provide VBA code to automate the Transfer of data from the iSeries into an Excel 2003 Spreadsheet. Currently we have to manually use the iSeries Excel Add-In. All fields are left default except the Library/File field where we enter PRODDTA/F56EXT. The data then transfers into the current sheet at A1. It would be great if we can automate that process and take a step away from the users. Appreciate any help. Cheers, Mike.

Software/Hardware used:
ASKED: June 19, 2008  4:14 AM
UPDATED: June 28, 2008  10:37 PM
RELATED QUESTIONS
Answer Wiki:
On the AS400, have created a command which uses the commands CPYTOIMPF and CPYTOPCD to send the output/work file from an AS400 process to a document folder, still on the AS400, as a CSV. Once the file is in CSV format, it can be imported into Excel, using a macro, and you could add that import to be a part of your formatting process. The AS400 command is as follows: CMD PROMPT('Copy file to CSV format') PARM KWD(FILENM) TYPE(*CHAR) LEN(10) MIN(1) + PROMPT('File Name') CHOICE('File to be copied') PARM KWD(LIBRARY) TYPE(*CHAR) LEN(10) MIN(1) + CHOICE('*LIBL, Library Name') PROMPT('Library') PARM KWD(MEMBER) TYPE(*CHAR) LEN(10) MIN(1) + PROMPT('File Member') CHOICE('*FIRST, File Member Name') PARM KWD(CSVNME) TYPE(*CHAR) LEN(8) MIN(1) + PROMPT('CSV Name') CHOICE('Recognisable name') PARM KWD(CALLMODE) TYPE(*CHAR) LEN(1) VALUES('I' 'S') + RSTD(*YES) DFT(I) PROMPT('Program call mode') + CHOICE('I = Interactive, S = Submit') The CL program that it executes is as follows: PGM PARM(&FILENAME &LIBRARY &MEMBER &CSVNAME + &CALLMODE) DCL VAR(&FILENAME) TYPE(*CHAR) LEN(10) DCL VAR(&LIBRARY) TYPE(*CHAR) LEN(10) DCL VAR(&MEMBER) TYPE(*CHAR) LEN(10) DCL VAR(&CSVNAME) TYPE(*CHAR) LEN(8) DCL VAR(&FULLCSVNM) TYPE(*CHAR) LEN(12) DCL VAR(&CALLMODE) TYPE(*CHAR) LEN(1) CHGVAR VAR(&FULLCSVNM) VALUE(&CSVNAME *TCAT '.CSV') IF COND(&MEMBER *EQ *BLANKS) THEN(CHGVAR + VAR(&MEMBER) VALUE('*FIRST ')) /* Interactive calls either from a "call" or a designated command. */ IF COND(&CALLMODE *EQ 'I') THEN(DO) /* + Interactive call. */ CRTDUPOBJ OBJ(CPYCSVF) FROMLIB(*LIBL) OBJTYPE(*FILE) + TOLIB(QTEMP) MONMSG MSGID(CPF2130) EXEC(CLRPFM FILE(QTEMP/CPYCSVF)) OVRDBF FILE(CPYCSVF) TOFILE(QTEMP/CPYCSVF) MONMSG MSGID(CPF0000) CLRPFM FILE(QTEMP/CPYCSVF) MONMSG MSGID(CPF0000) CPYTOIMPF FROMFILE(&LIBRARY/&FILENAME &MEMBER) + TOFILE(QTEMP/CPYCSVF) MONMSG MSGID(CPF0000) CPYTOPCD FROMFILE(QTEMP/CPYCSVF) TOFLR(CSV) + TODOC(&FULLCSVNM) REPLACE(*YES) MONMSG MSGID(CPF0000) ENDDO /* Submitted calls will be from other programs/month-end. */ IF COND(&CALLMODE *EQ 'S') THEN(DO) /* + Submitted call. */ CLRPFM FILE(*LIBL/CPYCSVF) MONMSG MSGID(CPF0000) CPYTOIMPF FROMFILE(&LIBRARY/&FILENAME &MEMBER) + TOFILE(*LIBL/CPYCSVF) MONMSG MSGID(CPF0000) CPYTOPCD FROMFILE(*LIBL/CPYCSVF) TOFLR(CSV) + TODOC(&FULLCSVNM) REPLACE(*YES) MONMSG MSGID(CPF0000) ENDDO ENDPGM Get your AS400 developer to create these and to get the command used wherever, in your automated AS400 process, the population of the workfile, that you normally import into your Excel, is completed. Once the AS400 has done its bit, execute the Excel macro, code for which is as follows: Sub DataImport() ' ' DataImport Macro ' Macro recorded 27/03/2008 by Andy.Mahoney ' ' Keyboard Shortcut: Ctrl+j ' ' Select home cell under the headings. Range("A2").Select ' Where "I" = AS400, mapped to from PC with the Excel on it and CSV is the document folder. With ActiveSheet.QueryTables.Add(Connection:="TEXT;I:CSVCSVName.CSV", _ Destination:=Range("A2")) .Name = "OTIFKILM" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1) .Refresh BackgroundQuery:=False End With End Sub Hope this helps.
Last Wiki Answer Submitted:  June 26, 2008  3:11 pm  by  ckellems   15 pts.
All Answer Wiki Contributors:  ckellems   15 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

My favorite options for doing this:

1. To start transfer from PC, use rxferpcb.exe (installed with iSeries Access)

2. To start transfer from iSeries, use CVTPFXLS (you can download from http://www.geocities.com/SiliconValley/Pines/5581/tips.htm

There are a number of other ways and pgms, but these are easy and free.

graybeard52
 3,115 pts.
 

Consider using the CPYTOIMPF command as well, where you can take a physical file and copy it to the IFS as an ASCII, comma separated variable file. Even though it wouldn’t be an .XLS formatted file, Excel can open it just fine and then it could be saved by the user as an .XLS later, if they desire.

If you want to transfer the file from the IFS to another server on your network, there are many ways to do this, including FTP, or mapping that network directory to the IFS, among others.

Cwc
 4,275 pts.
 

Hi guys,

Thanks for the answers, but both dont appear to be exactly what I am looking for.

I have an excel spreadsheet that has some VBA code to process the data that is populated by the Transfer from iSeries. The data is manipulated quite a bit inthe excel sheet, and then multiple files are created from this data and named according to some of the fields that are transferred.

So the current process is:
Log in to AS/400
Navigate to correct Menu
Enter details of data to extract using custom program
Log off AS/400
Open Excel Spreadsheet
Transfer data from iSeries
Click button on spreadsheet to process data

DONE.

It would be great if I can automate the transfer from iseries of data (not a file) within Excel.

So, if anyone has done any coding within VBA to replace the manual clicking etc of the Excel Add-in to transfer data that’s what I am after.

Open to other ways of doing this same task though…so all information greatly appreciated.

NOTE: I am NOT an AS/400 person…I know about Windows boxes…

Thanks team.

Cheers,
Mike.

Mblair.au
 25 pts.
 

Hi everyone,

I am still looking for an answer to this question.
The above code does not answer my question at all. I am NOT looking how to copy file contents into Excel, and I am not looking for Delphi Code.

The Transfer Data from iSeries is an ADD-IN to Excel. This allows us to connect directly to the AS/400 and transfer data to populate directly into Excel.
I want to automate the add-in basically.
Can anyone help me?

Cheers,
Mike.

Mblair.au
 25 pts.
 

How about recording a macro — does that semi-fit into what you’re wanting to do?

Cwc
 4,275 pts.
 

You can create a menu option on the 400 to call a CLLE that :
1. runs a remote command on the PC to do the transfer to an excel file.Path on pc to the transfer is needed.
2.run another remote command to Open EXCEL with the specfic file created.
3. then, if needed, signoff the user from the 400

Then you can do what ever is needed in EXCEL.

It must be done interactive not batch.
We use this method on a regular basis. If the processing needed to create info for the download we send a mesage to user tthat the file is ready and to take the transfer option.
Set the transfer to auto run automatically and the user just waits a few seconds depending on length it takes transfer to run. 

 PGM                                                         
       DCL        VAR(&STRCMD) TYPE(*CHAR) LEN(55) +         
                    VALUE('C:\DOWNLOAD\Transfer1.DTF')       
       DCL        VAR(&PCCMD) TYPE(*CHAR) LEN(55)            
       STRPCO     PCTA(*NO)                                  
       MONMSG     MSGID(IWS4010)                             
       STRPCCMD   PCCMD(&STRCMD) PAUSE(*NO)                  
        STRPCO PCTA(*NO)                                      
       MONMSG MSGID(IWS4010)                                 
       STRPCCMD   PCCMD('START C:\DOWNLOAD\EXCEL1.xls') +  
                    PAUSE(*NO)                               
 ENDPGM
Bigmac46
 1,000 pts.
 

You want to upload/download directly from DB2 to Excel – no intermediate processes. To do this you must set up and ODBC connection and then you can suck the data VERY simply using SQL.

Unfortunately, my VBA code is extremely complex and based on variables so I can’t paste anything useful here.

Buy this book from MC Press Online: i5/OS and MS Office Integration Handbook by Chris Peters, Brian Singleton.

It has the EXACT VBA code you need to do the job. I.E. Connect to DB2 and populate a spreadsheet starting at any cell.

The code is basic, simple and works. You can then build and modify as necessary.

Hope this helps.

DoneThat
 830 pts.