ExcelRunScript RPG2SQL Function to run custom user VB script macros

From Support

For users who want to write their own custom VBScript extensions that utilize the RPG2Excel functionality within the RPG2SQL Integrator product, the following must be done:

1.) Make sure the user's RPG2SQL Integrator PC component is at V1.0.32 or higher.

RPG2SQL Integrator Install http://www.rjssoftware.com/files/rpg2sql/integrator/rpg2sqlsetup.exe Password: RPGSQL400

V1.0.32 Exe Update: http://www.rjssoftware.com/files/rpg2sql/integrator/updates/rpgsqlsvup132.exe

2.) Download and install the RJS Scripting component and install on the selected PC: http://www.rjssoftware.com/files/rjsscripting/rjsscriptsetup.exe

3.) Add the following sample RPG2SQL RPG code to an RPG program to call the sample XLS_RunScript function you will create in step 4).

  *--------------------------------------------------------------------- 
  *                  ** Launch MS Excel and make it visible              
  *--------------------------------------------------------------------- 
 C                   Eval      Rtn = XLS_Launch(SQL_Socket)              
 C                   Eval      Rtn = XLS_Visible(SQL_Socket:1)           

  *--------------------------------------------------------------------- 
  *                  ** Create new spreadsheet file                      
  *--------------------------------------------------------------------- 
 C                   Eval      Rtn = XLS_Command(SQL_Socket:             
 C                             'FILENEW':'')                             

  *--------------------------------------------------------------------- 
  *                  ** Run Excel VBScript  Macro to set data in MS
  *                  ** Excel spreadsheet cells. 
  *--------------------------------------------------------------------- 
 C                   Eval      Rtn = XLS_RunScript(SQL_Socket:           
 C                             'c:\temp\helloexcel.vbs':                 
 C                             'TestExcel1':                             
 C                             'Call Test Data for B3;' +                
 C                             'Call Test Data for C3')                  
                                                                         

4.) Create a text file named: C:\temp\helloexcel.vbs and add the following code to the file:

Function TestExcel1(sCommand,sExcelObject,sObject2,sObject3)

    '---------------------------------------------------------------------
    'Function: TestExcel1
    'Purpose : Write a few cells of data into the current Excel sheet. 
    'Parms   : Single parameter separated by semicolons
    '---------------------------------------------------------------------

    '---------------------------------
    'Break down parameters. Parms should be delimited by semicolons if passed.
    'Parms are all zero-baed (Parm1 = arrparms(0), Parm2=arrparms(1), etc..)
    '---------------------------------
    arrParms=Split(sCommand,";")

    '---------------------------------
    'Move parm 1 value to cell B3
    '---------------------------------
    sExcelObject.ActiveSheet.Range("B3").value = arrparms(0)

    '---------------------------------
    'Move parm 2 value to cell C3
    '---------------------------------
    sExcelObject.ActiveSheet.Range("C3").value = arrparms(1)

    TestExcel1 = 0

End Function