WinBatch - Fun Text to Speech Simulation

Here's something fun I wrote years ago... it's a brief WinBatch program that accesses the Windows Sapi.spVoice() object to allow text-to-speech translations. The text you enter in the edit box will be read back to you by the computer voice installed on your Windows machine. You can find your voices in the Speech Properties section of Control Panel (Navigation depends on Windows version).

The two voices I have installed are David (Male) & Zira (Female) - I'll incorporate these in my Speech Utility program - allowing to select either with Radio Buttons.

The page is simple (built using the WinBatch GUI Dialog Builder*). Enter some text, select the voice, rate & volume then click "Talk to Me!" to hear the robotic voice read your text back to you. This is a great utility if you have laryngitus or simply want to annoy your wife & kids.


*Note - WinBatch includes a Windows GUI Dialog Builder - I mentioned it here in the Numeric-to-Literal Post (March 14, 2019).

TDTALK.WBT - Source Code:

;**********************************************************************
;*                                                                    *
;*       MODULE: TDTALK.WBT                                           *
;*       AUTHOR: TONY DELIA.                                          *
;*         DATE: 05/18/2004.                                          *
;*         DESC: TD WINBATCH SPEECH EMULATION.                        *
;*      VERSION: WINBATCH 2004F.                                      *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*    EXTENDERS:                                                      *
;**********************************************************************
;*                                                                    *
;*    NO EXTENDERS REQUIRED.                                          *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*    REVISIONS:                                                      *
;**********************************************************************
;*                                                                    *
;*   DATE     PROGRAMMER      DESCRIPTION                             *
;* ---------- --------------- --------------------------------------- *
;*                                                                    *
;* 05/18/2004 DELIA,TONY      ORIGINAL CODING.                        *
;*                                                                    *
;**********************************************************************

;**********************************************************************
;*       UDF/SUBROUTINES                                              *
;**********************************************************************

#include "TDFUN.WBC"

;**********************************************************************
;*       PROCESS MAIN                                                 *
;**********************************************************************

TDFUN_UserProps()

myText                  = "Hello "

if LDAP_name           <> ""

   myText               = StrCat(myText, LDAP_name, ". ")

endif

SAPI                    = ObjectOpen("Sapi.SpVoice")
sVOL                    = SAPI.volume
sVOI                    = SAPI.voice
sVOI_Desc               = sVOI.getdescription

SAPI.voice              = SAPI.GetVoices.Item(0)
SAPI.speak(myText)

gosub Define_Dialog_SPK

sw                      = @YES

while sw               == @YES

   ButtonPushed         = Dialog("SPK")

   Select ButtonPushed

      Case 1

         if myText     == ""
            myText      = "Enter something you moron"
         endif

         SAPI.voice     = SAPI.GetVoices.Item(0)
         SAPI.rate      =   0   
         SAPI.volume    = 100

         if myVoice    == "2"
            SAPI.voice  = SAPI.GetVoices.Item(1)
         endif
         if myRate     <> ""
            SAPI.rate   = myRate
         endif
         if myVolume   <> ""
            SAPI.volume = myVolume
         endif

         SAPI.speak(myText)
        
         break

      Case 2

         sw             = @NO
         break

   EndSelect

endwhile

SAPI.speak("Goodbye %LDAP_name%")

ObjectClose(SAPI)

Exit

;**********************************************************************
;*      Define Dialog SPK                                              *
;**********************************************************************

:Define_Dialog_SPK

SPKFormat=`WWWDLGED,6.1`

SPKCaption=`TDTALK - Text to Speech Synthesizer`
SPKX=002
SPKY=061
SPKWidth=366
SPKHeight=246
SPKNumControls=016
SPKProcedure=`DEFAULT`
SPKFont=`Microsoft Sans Serif|6656|40|34`
SPKTextColor=`0|0|0`
SPKBackground=`DEFAULT,DEFAULT`
SPKConfig=0

SPK001=`023,013,296,024,STATICTEXT,DEFAULT,"TDTALK - Text to Speech Utility",DEFAULT,1,512,"Microsoft Sans Serif|16384|70|34","0|0|0",DEFAULT`
SPK002=`021,043,172,012,STATICTEXT,DEFAULT,"Enter Speech Text  (Please keep it clean):",DEFAULT,2,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK003=`021,055,174,090,MULTILINEBOX,MyText,"Hello...",DEFAULT,3,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK004=`021,209,078,022,PUSHBUTTON,DEFAULT,"Talk to Me!",1,11,32,DEFAULT,DEFAULT,DEFAULT`
SPK005=`115,211,078,020,PUSHBUTTON,DEFAULT,"Exit",2,12,0,DEFAULT,DEFAULT,DEFAULT`
SPK006=`209,057,134,126,PICTURE,DEFAULT,"TDTALK",DEFAULT,13,DEFAULT,DEFAULT,DEFAULT,"C:\wbt\TDTALK.BMP"`
SPK007=`209,195,134,012,STATICTEXT,DEFAULT,"Developed by Tony DeLia",DEFAULT,14,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK008=`209,207,134,012,STATICTEXT,DEFAULT,"NSS/Advanced Publications",DEFAULT,15,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK009=`209,219,134,012,STATICTEXT,DEFAULT,"November '2004",DEFAULT,16,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK010=`027,165,044,012,RADIOBUTTON,MyVoice,"Male",1,5,DEFAULT,"Microsoft Sans Serif|6656|40|34","0|0|0",DEFAULT`
SPK011=`027,179,044,012,RADIOBUTTON,MyVoice,"Female",2,6,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK012=`021,153,070,048,GROUPBOX,DEFAULT,"Voice",DEFAULT,4,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK013=`155,157,036,012,EDITBOX,MyRate,DEFAULT,DEFAULT,8,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK014=`095,157,052,012,STATICTEXT,DEFAULT,"Rate (-10 to 10):",DEFAULT,7,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK015=`155,175,036,012,EDITBOX,MyVolume,DEFAULT,DEFAULT,10,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
SPK016=`095,175,052,012,STATICTEXT,DEFAULT,"Volume (0 - 100):",DEFAULT,9,DEFAULT,DEFAULT,DEFAULT,DEFAULT`

Return

;**********************************************************************
;*       END OF PROGRAM                                               *
;**********************************************************************

The included library TDFUN.WBC and the function TDFUN_UserProps() are not required for the speech functionality & can be commented out or removed. I use it to automatically extract my LDAP name assigned to my userid then start off with a personal greeting - "Hello Tony DeLia" as well as a final "Goodbye Tony DeLia".

The Male and Female Voice Options are specific to this computer (which was the impetus in the Male/Female Radio Button Design). It could also be designed as a drop-down list with all the installed voices... This was a fun little application to create & mess around with.

SQL*Engine - Dynamic SQL Script Processor - Part - 5 - Synonyms/Grants

SQL*Engine - Part 1 - Initial Overview/Demonstration
SQL*Engine - Part 2 - Launching PeopleSoft Process Requests
SQL*Engine - Part 3 - Command Guide
SQL*Engine - Part 4 - @SET Variables

I frequently use SQL*Engine to resolve mundane tasks. For instance, when you create tables in PeopleSoft using Application Designer the DDL model may not include synonyms & grant permissions (This typically applies to test environments) or they may have been dropped for some reason. This Blog Entry shows a quick method of creating synonyms (if needed) & assigning the default grants required so I can proceed with testing.

*****************************************************************
*                                                               *
*    MODULE: XXSQI_SG_DEPEXP.SQX                                *
*    AUTHOR: TONY DELIA.                                        *
*      DATE: 07/31/2016.                                        *
*      DESC: SQL*ENGINE SCRIPT - CREATE GRANTS/SYNONYMS.        *
*                                                               *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
*  PROGRAM VARIABLES                                            *
*****************************************************************
@HDR_REF=DEPEXP_Testing
@HDR_DESCR=Assign Test Environment Synonyms/Grants
@SET=&&DBNAME=@DBNAME
@SET=&&LIB=/app/iface/&&DBNAME/19000/AP/inbound
*****************************************************************
*   Set OBJECT (TABLE NAME)                                     *
*****************************************************************
@SET=&&OBJECT=PS_XX_DEPEXP_DATA 
@SCRIPT=&&LIB/XXSQL_SG.LIB
*****************************************************************
*   End of SQL*Engine Script                                    *
*****************************************************************

This SQL*Engine script (XXSQI_SG_DEPEXP.SQX) will utilize a library routine XXSQL_SG.LIB I have set up. All the primary script needs to do is populate the &&OBJECT variable with the table name (in this case PS_XX_DEPEXP_DATA) & pass control to the library script via @SCRIPT=&&LIB/XXSQL_SG.LIB (in lieu of a hard-coded path, &&LIB must point to the proper location relative to the database).

*****************************************************************
*                                                               *
*    MODULE: XXSQL_SG.LIB                                       *
*    AUTHOR: TONY DELIA.                                        *
*      DATE: 02/13/2004.                                        *
*      DESC: SQL*ENGINE SCRIPT - CREATE GRANTS/SYNONYMS.        *
*                                                               *
*****************************************************************
*                                                               *
*  Required Substitution Variables                              *
*  --------------------------------------                       *
*  &&OBJECT = Table Object                                      *
*                                                               *
*****************************************************************
*****************************************************************
*   TEST IF SYNONYM EXISTS                                      *
*****************************************************************
@STEP=COUNT_SYNONYM_&&OBJECT
@COUNT=ALL_SYNONYMS
 WHERE OWNER        = 'PUBLIC'
   AND SYNONYM_NAME = '&&OBJECT'
/
*****************************************************************
*   CREATE SYNONYM IF NOT EXISTS                                *
*****************************************************************
@STEP=SYNONYM_&&OBJECT
@COND=ZERO.COUNT_SYNONYM_&&OBJECT
CREATE PUBLIC SYNONYM &&OBJECT
   FOR SYSADM.&&OBJECT
/
*****************************************************************
*   GRANT - PSSELECT                                            *
*****************************************************************
@STEP=GRANT_PSSELECT_&&OBJECT
@COND=NULL
GRANT SELECT
   ON SYSADM.&&OBJECT
   TO PSSELECT
/
*****************************************************************
*   GRANT - PSUPDATE                                            *
*****************************************************************
@STEP=GRANT_PSUPDATE_&&OBJECT
@COND=NULL
GRANT INSERT, UPDATE
   ON SYSADM.&&OBJECT
   TO PSUPDATE
/
*****************************************************************
*   GRANT - PSDELETE                                            *
*****************************************************************
@STEP=GRANT_PSDELETE_&&OBJECT
@COND=NULL
GRANT DELETE
   ON SYSADM.&&OBJECT
   TO PSDELETE
/
*****************************************************************
*   End of SQL*Engine Script                                    *
*****************************************************************

XXSQL_SG.LIB determines if the synonym exists & using the @COND= directive performs the CREATE SYNONYM if needed. Next three GRANT steps are executed for SELECT, INSERT & UPDATE and DELETE. There are 5 Steps within the library script with each assigned a unique step name based on the &&OBJECT parameter.

*****************************************************************
*   Set OBJECT (TABLE NAME)                                     *
*****************************************************************
@SET=&&OBJECT=PS_DISTRIB_LINE    
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_PYMNT_VCHR_XREF 
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_FRGH_CHRG  
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_LINE_WTHD  
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_LN_TX_DTL  
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_REG_LC     
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_SALETX_CHG 
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_USETAX_CHG 
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_VNDR_BANK  
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHR_VNDR_INFO  
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCH_DUP_INV_REG 
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VENDOR_WTHD_JUR 
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VOUCHER         
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VOUCHER_LINE    
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_XX_PC_VCHR_PEND 
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_XX_PN_VCHR_XREF 
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_XX_VCHR_ACH     
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_XX_VNDR_CAR_DEB 
@SCRIPT=&&LIB/XXSQL_SG.LIB
@SET=&&OBJECT=PS_VCHRLN_MISC_CHG
@SCRIPT=&&LIB/XXSQL_SG.LIB
*****************************************************************

Add as many tables as needed by repetitively assigning them to the &&OBJECT variable & calling the script. SQL*Engine allows me to resolve this situation in minutes (writing or reusing a primary script and executing it). No technical administrative bureaucracy & forms. Just set the &&OBJECT variable & let it fly...

File Transfer Utility - Simplicity of Design

XXFDXFR.SQR - Generic Multi-Purpose File Transfer Utility

The secret to effective program design is isolating exactly what the requirements are (this includes anticipating future needs), streamlining the process by eliminating unnecessary complexity & delivering a solid product that is 100% reliable & very simple to use.

Here's a basic example. Our organization has a need to transfer different files via email from our file server to users/groups. They can be external files from vendors such as invoice information, security permissions for the administrator distribution group, output from other processes, monthly data extracts, etc. Regardless, the task at hand is to identify file X at folder location Y and email it to distribution group Z.

For any circumstance I'll need - a Transfer Definition, Email Distribution, Run Control & File Watcher/Interface Component - and finally, a generic transfer program (XXFDXFR) to utilize all of these components to fulfill the requirement.

Note - The File Watcher/Interface Component is our custom 24/7 Interface/Process Request Loader. It's extremely powerful - in general, within 5 minutes (or less) it links a file with a process & launches it. It can handle hundreds of files in multiple folder locations in an instant (part of our pre-Go-Live stress test). I developed the PeopleSoft/Oracle/API Components in 2004 (with Chuck Hamilton writing the underlying Unix shell scripts & cron job). Still running strong...

File Transfer Definition - Defines Key/Email Information:

Xfer Key: SPO_ERR
 Subject: ADP/SharePoint Security *ERRORS* <YMD> - [<DBASE>]
    Text: Please review the attached ADP/SharePoint ERROR file for <MDY>^^File: <FFULL>^^

Email Distribution - Directs output to PeopleSoft Oprid OR Email Address:

 Pgm Key: XXFDXFR     XXFDXFR
Xfer Key: SPO_ERR     SPO_ERR
    Type: P           E
   Value: TDELIA      user@email.com


Process Run Control - Passes Transfer Key to Process:

   Oprid: PSBATCH
Run Cntl: FD_XFR_SPOERR
Xfer Key: SPO_ERR

File Watcher Configuration - Detects File & Launches Process/Run Control:

    File: FDXSPO*.*
 Process: XXFDXFR
   Oprid: PSBATCH
Run Cntl: FD_XFR_SPOERR
  Folder: /app/iface/<DBASE>/ALL/AP/inbound
Day/Time: 24/7

XXFDXFR.SQR - File Transfer Program:

...
do IDB_Init('I', $RC_entity, 'AP', $RC_file)

do Process_Main

do IDB_Term
...

#include 'xx_tdf.sqc'    !TD Custom SQR Function Library
#include 'xx_idb.sqc'    !TD Interface Dashboard API
 

The XXFDXFR program (above) is launched via the File Watcher/Interface mechanism... i.e. minutes after the file is dropped in the folder. The core of the program is as follows:

IDB_Init() - Accepts the file that was detected via the File Watcher definition.
Process_Main - Reads the run control, assigns the Xfer Key & emails to the distribution.
IDB_Term - Automatically archives the input file.

File Watcher Configuration Page:

Above is the actual File Watcher Configuration page. It is also known as the IDB Scheduler (Interface Dashboard Scheduler) due to it's extensive interface audit capabilities.

Above is a sample screenshot of XXFDXFR on Process Monitor. Process instance 5106203 at 6:40AM contains the SPO_ERR Transfer we're using as an example.

The email with the file attachment has been successfully sent.

The subject & email body have been derived from the initial File Transfer Defintion for the key SPO_ERR. Notice the embedded tokens have been resolved in the Subject & Email Body:

<YMD> set to 2019-03-28
<DBASE> set to NSFSPRD
<MDY> set to 03-28-2019
<FFULL> set to Full Path/Filename of the attached file.
"^" Character set to CRLF.

Any additional file transfers that may be required are simply a matter of configuration without unnecessary development to engage in.

WinBatch - SQR Background Processor/Analyzer

This blog post highlights the features and usage of a WinBatch Utility I created years ago - TDSQRW - SQR Background Processor / Analyzer. It provides an Easy-to-Use GUI Interface that selects an SQR program & easily executes it directly via the SQRW.exe background executable. This is an alternate means of running a program outside of PeopleSoft Process Scheduler. The utility is also useful for analyzing SQR source code (including embedded SQC routines) & formatting the output in Excel. Perfect for documenting large, complex processes. Here's some of the features:

SQR Background Processor Functions:

1 – Extract all PeopleSoft profiles (Configuration Manager) for selection.

2 – Ability to alter Configuration settings (temporary for application only).

3 – Execute SQRW utilizing correct set of parameters. 

4 – Analyze SQR Source Code (Includes, Procedures, Logic Flow, SQL)

Analyzer Functions include:

1 – INCLUDE – All Include files are expanded and listed.

2 – ROUTINES -  Every Procedure is listed with Module of origin and called routines.

3 – FLOWCHART – Process Flowchart created highlighting procedural logic.

4 – SQL – SQL statements extracted and listed with Procedure/Module of origin (i.e. SQR,SQC).

All sheets are automatically generated with appropriate Report Headings, Freeze Pane, AutoFilter, Colorization, etc…

System Requirements and Assumptions:

A directory named C:\TEMP is assumed to exist (All generated EXCEL output is placed in this folder). PeopleSoft is installed via client setup to utilize profile selection (process may still run without this in place). Microsoft Excel must be installed for the Analyzer - an Excel session opens up automatically & populates four worksheet tabs with the analysis output.

MAIN Screen:

Use the File List Box to select the SQR source code file. To execute via <SUBMIT> the ID, Password and Database are required. Select a Profile and hit <REFRESH> or <EDIT> to update manually – this will ensure the appropriate SQR search variables and executable location used. <ANALYZE> will evaluate the selected SQR program (no ID/Password/Database required).

The buttons labeled SQR1 thru SQR4 are navigation shortcuts to the directories specified by PSSQR1 thru PSSQR4 (for the selected profile).

The fish graphic is a watercolor I painted a while ago (it can be replaced with anything named TDSQRW.BMP). At work this utility was simply known as "The Fish"... as in "run through Process Scheduler or the Fish".

Configuration (via Refresh or Edit):

The environment variables set according to the configuration manager profile may be altered using the Configuration Screen (Accessed by using the <REFRESH> or <EDIT> functions.

View Command Line:

You may view the SQRW command line by pressing the <VIEW> button. In this example you can see the USERID and PASSWORD have not been selected yet.

Online Help:

Press the <HELP> button for some basic assistance…

SQR Analyzer:

 INCLUDE Phase:

All #Include files are expanded utilizing the PSSQR1 thru PSSQR4 file search settings. All expanded files are listed on the first worksheet – an expanded source code file will reside in the C:\TEMP directory with a file extension of .SQZ.

ROUTINES Phase:

The ROUTINES phase will create a new worksheet with all Procedures listed along with any called routines. The module of origin is also listed. AutoFilter is automatically incorporated into the sheet.

 FLOWCHART Phase:

The FLOWCHART phase produces an indented flowchart of the procedural logic of the program. Any repeated procedures will be terminated with <R> - this avoids redundancy and potential endless loops when deciphering the process logic.

SQL Phase:

All SQL statements are extracted, parsed appropriately and displayed on the SQL worksheet. AutoFilter is very useful for pinpointing the exact location of a SQL statement.

TDSQRW.WBT - Source Code Snippets:

Below you'll find some WinBatch Code snippets to illustrate how the application is written. Looks like I'll need to update the PeopleTools Registry key address since we're on a higher release. This allows you to access the various Configuration Profiles. Further down in the code you can see how the Button Selections are handled.

;**********************************************************************
;*       PROCESS MAIN                                                 *
;**********************************************************************

gosub Define_Dialog_TD

; No Listbox Selection Required
IntControl(4,0,0,0,0)

; Restrict Multiple Listbox Selection (only one allowed)
IntControl(33,0,0,0,0)

ErrorMode(@OFF)
Profiles      = ""
Rpath         = "SOFTWARE\PeopleSoft\PeopleTools\Release8.40\Profiles"
Rkey          = RegOpenKey(@REGMACHINE, Rpath)
Rerr          = LastError()
if Rerr      == 0
   Profiles   = RegQueryKeys(Rkey)
   RegCloseKey(Rkey)
endif
ErrorMode(@CANCEL)

gosub Custom_Init

IDXdir        = xSQR1
ErrorMode(@OFF)
DirChange(IDXdir)
ErrorMode(@CANCEL)

zMsg          = "[Enter Parameters please]"

sw            = @YES

while sw     == @YES

   zListBox   = Profiles
   zFileBox   = DirGet()
   zMsg       = StrCat("PATH: ", zFileBox, "  ", zMsg)

   ButtonPushed=Dialog("TD")

   zMsg       = ""

   Select ButtonPushed

      Case 0

         sw   = @NO            ; Documentation Only (See :CANCEL)

         break

      Case 1                   ; Submit

         gosub Process_Submit
         break

      Case 2                   ; Refresh

         gosub Process_Refresh
         gosub Process_Edit
         break

      Case 3                   ; Edit

         gosub Process_Edit
         break

      Case 4                   ; Analyze

         gosub Process_Analyze
         break

      Case 5                   ; Help

         gosub Process_Help
         break

      Case 6
      Case 7
      Case 8
      Case 9

         sqrX = ButtonPushed - 5
         sqrD = xSQR%sqrX%
         ErrorMode(@OFF)
         DirChange(sqrD)
         ErrorMode(@CANCEL)

    EndSelect

endwhile

:CANCEL

exit

;**********************************************************************


The Process_Analyze routine is the fun part of the program. This is where the source module text is sliced, diced, reassembled, parsed & flowcharted.

;**********************************************************************
;*      Process Analyze                                               *
;**********************************************************************

:Process_Analyze

wPGM                    = zFileBox

if wPGM                == "" | wPGM == "NOFILESELECTED"
   zMsg                 = "[Please select a program]"
else

   timeRaw              = StrReplace(TimeYmdHms(),":","")
   timeYmd              = StrSub(timeRaw,1,8)
   timeHms              = StrSub(timeRaw,9,6)
   TDdate               = StrCat(timeYmd,"_",timeHms)

   XLobj                = ObjectOpen("Excel.Application")

   XLobj.DisplayAlerts  = @FALSE
   XLobj.Visible        = @TRUE
   XLobj.UserControl    = @TRUE

   WBS                  = XLobj.Workbooks
   XLS                  = WBS.add
   WS                   = XLobj.Worksheets
   WKS                  = WS.add
   WKS                  = WS.add
   WKS                  = WS.add

   PRTname              = pGetDefPrtInf(1)    ; Name Request
   PRTport              = pGetDefPrtInf(3)    ; Port Request

   PRTactive            = "%PRTname% on %PRTport%"

   wROOT                = FileRoot(wPGM)

   gosub Expand_Source
   gosub Analyze_Routines
   gosub SQR_Flowchart
   gosub SQL_Extract

   XLobj.UserControl    = @FALSE

   XLS.SaveAs("c:\temp\tdsqrw_%wROOT%_%TDdate%.xls")
   XLobj.Quit
   ObjectClose(WKS)
   ObjectClose(WS)
   ObjectClose(XLS)
   ObjectClose(WBS)
   ObjectClose(XLobj)

endif

Return

;**********************************************************************

The most interesting part of the Analyzer is creating the Process Flowchart. This is an indented Tree Structure that visually displays the Parent/Child relationship between a procedure or function & the procedures/functions that they may call.

;**********************************************************************
;*      SQR Flowchart                                                 *
;**********************************************************************

:SQR_Flowchart

ctr                    = 1

TDXLS_SheetName(XLobj, 3, "FLOWCHART")
TDXLS_SheetActivate(XLobj,3)

TDXLS_FormatColumn(XLobj,  1,  30,  "@")
col                    = 2
while col             <= 20
   TDXLS_FormatColumn(XLobj,  col,   3,  "@")
   col                 = col + 1
endwhile

TDXLS_WriteCell(XLobj, ctr, 1, @TRUE, @TRUE,  0, "V", "Module")
TDXLS_WriteCell(XLobj, ctr, 3, @TRUE, @TRUE,  0, "V", "Process Flowchart")

TDXLS_ColorRow(XLobj,  ctr, 1, 23, 36)

ctr                    = ctr + 2

; Flowchart BEG...

S_ptr                  = ArrDimension(30)
S_sub                  = ArrDimension(30)
S_idx                  = 0
while S_idx            < 30
   S_ptr [S_idx]       = 0
   S_sub [S_idx]       = ""
   S_idx               = S_idx + 1
endwhile

S_idx                  = 1

while S_idx            > 0

   Z_idx               = S_ptr  [S_idx]
   Z_sub               = S_sub  [S_idx]

   Y_proc              = A_proc [Z_idx]
   Y_sub               = A_sub  [Z_idx]
   Y_tag               = A_tag  [Z_idx]
   Y_type              = A_type [Z_idx]
   Y_mod               = A_mod  [Z_idx]

   Z_ptr               = Z_idx
   Z_idx               = Z_idx + 1

   S_ptr [S_idx]       = Z_idx

   Y_procU             = StrUpper(Y_proc)
   Z_subU              = StrUpper(Z_sub)

   Z_go                = @FALSE

   if Y_procU         == PROC_S & S_idx  == 1
      Z_go             = @TRUE
   endif
   if Y_procU         == PROC_H & S_idx  == 1
      Z_go             = @TRUE
   endif
   if Y_procU         == PROC_F & S_idx  == 1
      Z_go             = @TRUE
   endif
   if Y_procU         == PROC_R & S_idx  == 1
      Z_go             = @TRUE
   endif
   if Y_procU         == Z_subU & Y_proc <> ""
      Z_go             = @TRUE
   endif

   if Z_go            == @TRUE

      if Y_tag        == 0

         I_sub         = Y_sub
         if Y_type    == K_error
            I_sub      = StrCat("On-Error=", I_sub)
         endif
         col           = S_idx + 2
         TDXLS_WriteCell(XLobj, ctr,   1, @FALSE, @FALSE,  0, "V", Y_mod)
         TDXLS_WriteCell(XLobj, ctr, col, @FALSE, @FALSE,  0, "V", I_sub)
         SVrow         = ctr
         SVcol         = col
         ctr           = ctr + 1

         S_idx         = S_idx + 1
         Z_idx         = 0

         S_ptr [S_idx] = Z_idx
         S_sub [S_idx] = Y_sub

      else

         if Y_tag      > 0
            I_sub      = TDXLS_GetValue(XLobj, SVrow, SVcol, @FALSE)
            I_sub      = StrCat(I_sub, " <R>")
            TDXLS_WriteCell(XLobj, SVrow, SVcol, @FALSE, @FALSE,  0, "V", I_sub)
         endif

         Z_idx         = A_idx + 1

      endif

      A_tag [Z_ptr]    = Y_tag + 1

   endif

   if Z_idx            > A_idx
      S_idx            = S_idx - 1
   endif

endwhile

; Flowchart END

TDXLS_Zoom(XLobj, 70)
TDXLS_Orientation(XLobj, "P")
TDXLS_GridLines(XLobj, @TRUE)
TDXLS_PaperSize(XLobj, 1)                  ; Letter (Default)
TDXLS_AutoFilter(XLobj, 2, 1, ctr, 1)
TDXLS_FreezePane(XLobj, 3, 1)
TDXLS_TitleRows(XLobj, 1, 2)

TDXLS_HeaderFooter(XLobj, "H", "L", "TDSQRW - %wROOT% (%TDdate%) - FLOWCHART")
TDXLS_HeaderFooter(XLobj, "H", "R", "NSS/Advance Publications [TD]")
TDXLS_HeaderFooter(XLobj, "F", "L", "Page &P of &N")
TDXLS_HeaderFooter(XLobj, "F", "R", "&D &T")

Return

;**********************************************************************

The Excel Integration is handled by a custom copybook I wrote - TDXLS.WBC. It includes routines to perform various XLS functions - each starting with the prefix TDXLS_%. Functions are listed below with a short description:

;**********************************************************************
;*                                                                    *
;*       MODULE:  TDXLS.WBC                                           *
;*       AUTHOR:  TONY DELIA.                                         *
;*         DATE:  03/18/2004.                                         *
;*         DESC:  WINBATCH MICROSOFT EXCEL SUPPORT.                   *
;*      VERSION:  WINBATCH 2003J.                                     *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     OVERVIEW:  THIS MODULE CONTAINS ROUTINES TO ACCESS MICROSOFT   *
;*                EXCEL OBJECTS/PROPERTIES/METHODS. THIS INCLUDES THE *
;*                RETRIEVAL/ASSIGNMENT OF CELL VALUES, CELL XLATION,  *
;*                COLORIZATION AND CELL/COLUMN FORMATTING. ADDITIONAL *
;*                FUNCTIONALITY WILL BE IMPLEMENTED SOON.             *
;*                                                                    *
;**********************************************************************
;*                                                                    *
;*     ROUTINES:  TDXLS_GetValue()      - Extract Cell Value.         *
;*                TDXLS_GetFormula()    - Extract Cell Formula.       *
;*                TDXLS_WriteCell()     - Write Cell Value/Formula.   *
;*                TDXLS_ColumnXlate()   - Numeric-to-Alpha Col Xlt.   *
;*                TDXLS_ColorRow()      - Color Row.                  *
;*                TDXLS_FormatCell()    - Format Cell.                *
;*                TDXLS_FormatColWrap() - Format Column.              *
;*                TDXLS_AutoFit()       - Autofit Column(s).          *
;*                TDXLS_AutoFitRows()   - Autofit Row(s).             *
;*                TDXLS_TitleRows()     - Title Rows Page Setup.      *
;*                TDXLS_Orientation()   - Landscape/Portrait.         *
;*                TDXLS_Zoom()          - Zoom Page Size (%).         *
;*                TDXLS_HeaderFooter()  - Set Header/Footer.          *
;*                TDXLS_PrintArea()     - Set PrintArea.              *
;*                TDXLS_GridLines()     - Print Grid Lines.           *
;*                TDXLS_PaperSize()     - Set Paper Size.             *
;*                TDXLS_ActivePrinter() - Set Active Printer.         *
;*                TDXLS_FreezePane()    - Freeze Window Pane.         *
;*                TDXLS_SheetName()     - Assign Sheet Name.          *
;*                TDXLS_SheetActivate() - Activate Sheet.             *
;*                TDXLS_AutoFilter()    - Set AutoFilter for Row.     *
;*                                                                    *
;**********************************************************************

Below is an example of one of the XLS functions - TDXLS_AutoFilter(). Each function is concisely documented listing the Input/Output parameters.

;**********************************************************************
;*       AUTOFILTER FUNCTION                                          *
;**********************************************************************
;*                                                                    *
;*        INPUT: I_XLS      - XL Object                               *
;*               I_rowX     - X-Coordinate Row                        *
;*               I_colX     - X-Coordinate Column                     *
;*               I_rowY     - Y-Coordinate Row                        *
;*               I_colY     - Y-Coordinate Column                     *
;*       OUTPUT: n/a        - None (Set to @TRUE)                     *
;*                                                                    *
;**********************************************************************

#DefineFunction TDXLS_AutoFilter(I_XLS, I_rowX, I_colX, I_rowY, I_colY)

colX             = TDXLS_ColumnXlate(I_colX)
colY             = TDXLS_ColumnXlate(I_colY)

myRange          = I_XLS.Range("$%colX%$%I_rowX%:$%colY%$%I_rowY%")
myRange.Select

mySel            = I_XLS.Selection
mySel.AutoFilter

ObjectClose(mySel)
ObjectClose(myRange)

return @TRUE

#EndFunction

;**********************************************************************

Notice all code is streamlined, clean & crisp, well-documented & efficient... I do not permit a single line of sloppy code... not under any circumstance... ever! We'll end this post on that note...