VBA code to transfer data from iSeries to Excel

25 pts.
Tags:
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

On the AS/400, 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 AS/400 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 AS/400 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 AS/400 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:\CSV\CSVName.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.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • graybeard52
    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.
    3,115 pointsBadges:
    report
  • Cwc
    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.
    4,290 pointsBadges:
    report
  • Mblair.au
    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.
    25 pointsBadges:
    report
  • Mblair.au
    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.
    25 pointsBadges:
    report
  • Cwc
    How about recording a macro -- does that semi-fit into what you're wanting to do?
    4,290 pointsBadges:
    report
  • Bigmac46
    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
    1,000 pointsBadges:
    report
  • DoneThat
    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.
    830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following