Re: Retrieve iSeries data in Excel spreadsheet

["Peter Dow (ML)" <maillist@xxxxxxxxxxxxxxx>]

Hi Mike,

Here's something I did to retrieve member text into a spreadsheet.  It 
was a quickie written back in 1994 with some stuff hardcoded, designed 
for a spreadsheet with object name, lib and type in adjacent columns.  
When a range of these columns is selected, the macro gets the member 
text and puts it into the next column.  You can probably figure out how 
to change it to a function.

Here's the VBA macro:

Sub GetMemberText()

   Dim AS400 As New AS400System
   Dim MbrName As String
   Dim Parms As New ProgramParameters
   Dim Pgm As New Program
   Dim SrcFile As String
   Dim SrcLib As String
   Dim strCvtr As New StringConverter
   Dim Text As String

   AS400.Define "DEVELOP"
  
   Pgm.programName = "RTVMBRTXT"
   Pgm.libraryName = "PDOW"
   Set Pgm.system = AS400
  
   Parms.Append "Source library", cwbrcInput, 10
   Parms.Append "Source file", cwbrcInput, 10
   Parms.Append "Member name", cwbrcInput, 10
   Parms.Append "Member text", cwbrcOutput, 50
  
   For Each c In Selection
       MbrName = Cells(c.Row, c.Column).Value
       SrcFile = "HSSSRC"
       SrcLib = "HS#LIBR"
       strCvtr.Length = 10
       Parms("Source library") = strCvtr.ToBytes(SrcLib)
       Parms("Source file") = strCvtr.ToBytes(SrcFile)
       Parms("Member name") = strCvtr.ToBytes(MbrName)
  
       Pgm.Call Parms
       If Pgm.Errors.Count > 0 Then
           Text = "Member not in file!"
       Else
           strCvtr.Length = Parms("Member text").Length
           Text = Trim(strCvtr.FromBytes(Parms("Member text").Value))
       End If
      
       Cells(c.Row, c.Column + 1).Value = Text
   Next
  
   MsgBox "Member texts retrieved for selection.", vbOKOnly
  
End Sub

and the CL that's called is

PGM PARM(&SRCLIB &SRCFIL &MBR &TEXT)                                   
            DCL        VAR(&SRCLIB) TYPE(*CHAR) LEN(10)               
            DCL        VAR(&SRCFIL) TYPE(*CHAR) LEN(10)               
            DCL        VAR(&MBR)    TYPE(*CHAR) LEN(10)               
            DCL        VAR(&TEXT)   TYPE(*CHAR) LEN(50)               
                                                                      
            RTVMBRD    FILE(&SRCLIB/&SRCFIL) MBR(&MBR) TEXT(&TEXT)    
            MONMSG     MSGID(CFP9801 CPF9810 CPF9812 CPF9815) EXEC(DO)
            CHGVAR     VAR(&TEXT) VALUE('*ERR*')                      
            ENDDO                                                     
ENDPGM                                                                 

hth
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

MCorbo@xxxxxxxxxx wrote:
> I am not an excel data person (rpg programmer by trade), and have been
> asked to see if I can come up with a solution for a problem we have.
>
> I have an excel spreadsheet, that contains 5 columns, I need to use the
> first 4 columns as  keys to an file on the iSeries and fill the 5th column
> with a field from that file.  I would prefer that the fifth column be a
> user defined function if possible.  So in column E, we would code the
> following
>
> =get400data(a1,b1,c1,d1)
>
> and it will retrieve the record from the system and populate column E with
> the field.
>
> I know this has to be possible, I have searched and can not make heads or
> tails of what I find.  Can anyone point me in the right direction?
>
> Mike
>
> Please consider the environment before printing this email. The Donna Karan Company LLC DISCLAIMER:   This e-mail is intended only for the addressee(s) and may contain confidential information.    If you are not the intended recipient, you are hereby notified that any use of this information or dissemination, distribution or copying of this e-mail is strictly prohibited.    If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete the original message.    Thank you. 
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG. 
> Version: 7.5.524 / Virus Database: 269.24.1/1470 - Release Date: 5/28/2008 7:20 AM
>