At the bottom is a little sample.
Chip Dorman wrote:
>
> Glenn,
>
> I am interested in the answer to this, too. Particularly, what
> is the escape sequence from the 3000 to Reflection (R1) that starts
> a Reflection VBA subroutine.
>
> Chip Dorman
> Greenfield Industries
>
> Glenn J. Koster, Sr. wrote:
>
> > I have a question... well, okay, a problem... I was hoping that someone on
> > this list might actually be able to help me resolve....<snip>
;-----------------------------------------------------------------------
; DDE_XL.RCL v5.0
;
; Reflection/Excel program showing sending/receiving data via DDE.
;
; DDE, or Dynamic Data Exchange, is a protocol for automatically
passing
; data and/or commands between Windows applications. Under the protocol
; one application initiates a DDE conversation with another application;
; then data or commands may be passed, hot links may be established that
; will automatically keep data values current between the applications;
; finally, either application may terminate the DDE conversation.
;
; Description:
; This little program launches the Excel application if it isn't already
; present, starts a DDE conversation with Excel, if one isn't already in
; progress, opens a spreadsheet within Excel and then sends data to it,
; updating sales values in the spreadsheet.
; The program keeps on looping as the data is updated, until one of the
; data items reaches a certain value. The data values are formatted and
; displayed in Reflection's terminal window.
; Excel calculates the total sales from the data sent by Reflection; the
; total value is sent back to Reflection via DDE Request from Reflection
; and is displayed in the Reflection terminal window with the sales data
; for each update loop.
; The program detects any DDE errors by checking Reflection's ERROR-CODE
; value after each DDE command; these codes are translated into English
; error messages and are printed in the terminal window.
;
; Variables:
; v0 - Value of Reflection/Excel DDE conversation number
; v1 - Data value sent to Excel via DDE POKE
; v2 - Data value sent to Excel via DDE POKE
; v3 - Data value sent to Excel via DDE POKE
; v4 - Data value sent to Excel via DDE POKE
; v5 - Data value received from Excel thru DDE REQUEST
; v6 - String naming a DDE operation that has failed
; v7 - String set to "keep_running" or "stop_running"
; v8 - Error-Code value and translated error string
; v9 - Loop counter
;
; Usage Notes:
; Note 1: This script requires:
; - Microsoft Excel Version 4.0 or later,
; - DDE_XL.XLS, Reflection sample Excel spreadsheet file.
; The Excel file DDE_XL.XLS should be in the directory from
; which this RCL script is being run; otherwise, the path to
; DDE_XL.XLS should be added to subroutine SetupExcel.
; Note 2: The program may be run multiple times with or without
closing
; Excel between runs; if closing Excel, choose either option
to
; save the Excel files or not, it doesn't matter. Also Excel
; and/or Reflection can be minimized while DDE_XL is running.
; Note 3: The length of time the DDE_XL executes may be varied by
; changing a decrement value (in subroutine UpdateSalesData).
; Note 4: Activating any of Excel's menus for several seconds blocks
; DDE messages, causing the DDE conversation to be shut down
; by Reflection and terminating the program. Closing Excel
; while the program is running will have the same effect.
;
;-----------------------------------------------------------------------
; initialize system: set timeout value, stop any ongoing DDE
conversations
gosub SetupReflection
; initiate a DDE conversation with Excel, launch Excel if neccesary
gosub StartXLConverstn
gosub SetupExcel
; initialize the 'Sales Revenue' figures
gosub InitSalesData
; start monitoring "Sales Activities", loop until done
:loop
if v7 = "keep_running"
; do some incrementing/decrementing of the data values
gosub UpdateSalesData
; send sales data to Excel and get its calculated total back
gosub SendDataToXL
gosub GetDataFromXL
;wait 0:0:1
; display the data values in the terminal window
gosub DisplaySalesData
; decide if we should keep looping or quit
gosub TestDataValue
goto loop
else
; terminate DDE conversation
gosub EndXLConverstn
; stop the program and say so
gosub StopProgram
endif
;----------------------------------------------------------------------
; Sub: SetupReflection
;
; Initialize the system: set Reflection's DDE timeout value, kill any
; ongoing DDE conversations and check for errors along the way.
;----------------------------------------------------------------------
:SetupReflection
quiet command on
set literal-escape "~~"
SET DDE-TIMEOUT 10
let v6 = ""
let v7 = "keep_running"
let v9 = 0
display " ^M^J"
display " ^M^J"
display " ================================================== ^M^J"
display " ^M^J"
display " ***** Beginning Reflection/Excel DDE Program ***** ^M^J"
display " ^M^J"
display " ***** Terminating Existing DDE Conversations ***** ^M^J"
display " ^M^J"
continue
DDE-TERMINATE-ALL
let v8 = error-code
if v8 <> 0
let v6 = "Terminate-All"
gosub ErrorHandler
endif
return
;----------------------------------------------------------------------
; Sub: StartXLConverstn
;
; This subroutine tries to initiate a DDE conversation with Excel (using
; the generic topic "SYSTEM") assuming Excel is running. If the
; conversation can't be started because Excel doesn't answer, we try to
; launch Excel and then start the conversation. If the conversation
; still can't be initiated, we quit trying and exit the program.
; If the conversation is initiated, the conversation number is put in
; the variable v0.
;----------------------------------------------------------------------
:StartXLConverstn
display " ***** Initiating Conversation with Excel ***** ^M^J"
display " ^M^J"
continue
DDE-INITIATE "EXCEL" "SYSTEM" V0
let v8 = error-code
if v8 = 203
; can't initiate conversation, must launch Excel application
gosub LaunchExcel
display " ***** Retry Initiating Conversation ***** ^M^J"
display " ^M^J"
continue
DDE-INITIATE "EXCEL" "SYSTEM" V0
let v8 = error-code
endif
; if still some DDE problem, put up a message and quit
if v8 <> 0
let v6 = "Initiate"
goto ErrorHandler
endif
return
;----------------------------------------------------------------------
; Sub: LaunchExcel
;
; This subroutine asks the user for the directory of the Excel
; application and then launches Excel, specifying the worksheet file
; supplied with this demo (DDE_XL.XLS).
;
;----------------------------------------------------------------------
:LaunchExcel
display " ***** Excel Application Is Not Running ***** ^M^J"
display " ^M^J"
display " ***** Launching Excel Application ***** ^M^J"
display " ^M^J"
; prompt the user for the Excel directory
let v1 = "Please specify the path to your Excel executable, or press
Cancel to quit."
let v6 = "c:\excel\excel.exe"
continue
acceptbox v6 prompt v1 default v6 icon question
let v8 = error-code
if v8 <> 0
display " ***** DDE demo aborted by user ***** ^M^J"
display " ^M^J"
goto StopProgram
endif
; launch Excel application with the specified worksheet file, DDE_XL.XLS
; (note: DDE_XL.XLS file should be in the same directory as DDE_XL.RCL)
; (also note: SHELL is not a DDE command)
shell v6 DDE_XL.XLS
; give Excel a moment to collect its wit
wait 0:0:3
return
;----------------------------------------------------------------------
; Sub: SetupExcel
;
; This routine uses two DDE commands to initialize Excel, with a
; spreadsheet open and a DDE conversation referencing that spreadsheet.
;
; The "SYSTEM" topic DDE conversation established in StartXLConverstn
; is terminated and we then initiate a new conversation with the
; topic "DDE_XL.XLS". The new conversation number is put in V0 and is
; used in all subsequent DDE commands in this program.
;----------------------------------------------------------------------
:SetupExcel
display " ***** Setting Up Excel Spreadsheet ***** ^M^J"
display " ^M^J"
; start a conversation with topic "DDE_XL.XLS"
continue
DDE-INITIATE "EXCEL" "DDE_XL.XLS" V1
let v8 = error-code
if v8 <> 0
; send command to open the file
DDE-EXECUTE V0 '[OPEN("DDE_XL.XLS", 0)]'
let v8 = error-code
if v8 <> 0
let v6 = "Execute"
goto ErrorHandler
endif
; try again to start "DDE_XL.XLS" topic conversation
DDE-INITIATE "EXCEL" "DDE_XL.XLS" V1
let v8 = error-code
if v8 <> 0
let v6 = "Initiate"
goto ErrorHandler
endif
endif
; close the "SYSTEM" topic conversation
continue
DDE-TERMINATE V0
let v8 = error-code
if v8 <> 0
let v6 = "Terminate"
gosub ErrorHandler
endif
; reuse v0 for "DDE_XL.EXE" topic conversation
let v0 = v1
return
;----------------------------------------------------------------------
; Sub: InitSalesData
;
; This subroutine sets the initial values for the sales data.
; For this demonstration, data values are created here rather than
; getting them from a host application.
;
;----------------------------------------------------------------------
: InitSalesData
display " ***** Acquire Initial Sales Data ***** ^M^J"
display " ^M^J"
let v1 = 500
let v2 = 0
let v3 = 100
let v4 = 200
let v5 = 820
display " ***** Begin Sending Sales Data To Excel ***** ^M^J"
display " ^M^J"
return
;----------------------------------------------------------------------
; Sub: UpdateSalesData
;
; This subroutine increments and decrements the data values. The loop
; counter is used to decide whether to send sales up or down, just like
; in real life.
;
;----------------------------------------------------------------------
:UpdateSalesData
if v9 < 20 or v9 < 7
let v1 = v1 - 7
else
let v1 = v1 + 9
endif
if v9 < 23 or v2 < 61
let v2 = v2 + 43
else
let v2 = v2 - 91
endif
if v9 < 12 or v3 < 11
let v3 = v3 + 17
else
let v3 = v3 - 11
endif
; **** V4 is the value that controls how long we run ****
; note: change the decrement value below - smaller is slower
; (the v4 value is tested later, after it's poked to Excel)
let v4 = v4 - 6
if v4 < 6
let v4 = 0
endif
let v9 = v9 + 1
return
;----------------------------------------------------------------------
; Sub: SendDataToXL
;
; This subroutine uses the DDE-POKE command to send each value to
; a cell in the Excel spreadsheet. Excel then displays the data in
; spreadsheet and calculates the total sales value. (Note: there are
; ways to poke a range of cell values into an Excel spreadsheet.)
;
;----------------------------------------------------------------------
:SendDataToXL
continue
DDE-POKE V0 "R5C2" "$1"
let v8 = error-code
if v8 <> 0
let v6 = "Poke"
goto ErrorHandler
endif
continue
DDE-POKE V0 "R6C2" "$2"
let v8 = error-code
if v8 <> 0
let v6 = "Poke"
goto ErrorHandler
endif
continue
DDE-POKE V0 "R7C2" "$3"
let v8 = error-code
if v8 <> 0
let v6 = "Poke"
goto ErrorHandler
endif
continue
DDE-POKE V0 "R8C2" "$4"
let v8 = error-code
if v8 <> 0
let v6 = "Poke"
goto ErrorHandler
endif
return
;----------------------------------------------------------------------
; Sub: GetDataFromXL
;
; This subroutine uses the DDE-REQUEST command to retrieve a value from
; a cell in the Excel spreadsheet. This cell contains a formula that
; adds the values sent from this program, so the value we get back from
; Excel should be the sum of the four values we poked in SendDataToXL.
; (Note: a DDE-ADVISE could be used to link to the data, but Excel would
; update us every time we poked one of the values to it, so this way
; causes less DDE message traffic.)
;
;----------------------------------------------------------------------
:GetDataFromXL
continue
DDE-REQUEST V0 "R9C2" V5
let v8 = error-code
if v8 <> 0
let v6 = "Request"
goto ErrorHandler
endif
return
;----------------------------------------------------------------------
; Sub: DisplaySalesData
;
; This subroutine displays the new, updated data values in Reflection's
; terminal window.
; The "Total Sales" value is calculated by Excel. Reflection gets the
; calculated value and puts it in the V5 variable (see GetDataFromXL).
; That value is then displayed by this routine.
;
;----------------------------------------------------------------------
:DisplaySalesData
display " ^M^J"
display " ^M^J"
display " ^M^J"
display " ^M^J"
display " --------------------------------------------------- ^M^J"
display " Current Sales Revenues: Companies X, Y, Z and Other ^M^J"
display " -- as of " & $time & ", " & $date & " -- ^M^J"
display " --------------------------------------------------- ^M^J"
display " ^M^J"
display " Company X: $ $1 Million ^M^J"
display " Company Y: $ $2 Million ^M^J"
display " Company Z: $ $3 Million ^M^J"
display " All Others: $ $4 Million ^M^J"
display " ^M^J"
display " Total Sales: $ $5 Million ^M^J"
display " ^M^J"
display " --------------------------------------------------- ^M^J"
return
;----------------------------------------------------------------------
; Sub: TestDataValue
;
; This subroutine decides if the value in variable v4 is below some
; critical level. If so, then we terminate the program (instead of
; looping forever).
;
;----------------------------------------------------------------------
:TestDataValue
if v4 = 0
display " ^M^J"
display " ***** No Market Share for Others ***** ^M^J"
display " ^M^J"
let v7 = "stop_running"
endif
return
;----------------------------------------------------------------------
; Sub: ErrorHandler
;
; This subroutine displays an appropriate error message for any DDE
; command errors detected, and then exits to the approprite routine for
; stopping the program.
;
;----------------------------------------------------------------------
:ErrorHandler
display " ***** DDE $6 Error: "
gosub XlateErrorString
display "$8 ***** ^M^J"
display " ^M^J"
if v6 = "Initiate" or v6 = "Execute" or v6 = "Poke" or v6 = "Request"
gosub EndXLConverstn
endif
; stop the program
let v7 = "stop_running"
gosub StopProgram
return
;----------------------------------------------------------------------
; Sub: XlateErrorString
;
; This subroutine translates an error-code value in v8 into an error
; string in v8. Note that this routine handles all DDE client errors,
; and so may be used in other programs.
;
;----------------------------------------------------------------------
:XlateErrorString
if v8 = 161
let v8 = "Syntax error in RCL command"
return
endif
if v8 = 201
let v8 = "System error, unable to complete operation"
return
endif
if v8 = 202
let v8 = "Insufficient memory; operation aborted"
return
endif
if v8 = 203
let v8 = "Conversation not initiated or not found"
return
endif
if v8 = 204
let v8 = "Maximum conversation count exceeded"
return
endif
if v8 = 205
let v8 = "Message not Acknowledged; timed out"
return
endif
if v8 = 206
let v8 = "Negative Acknowledge from Server"
return
endif
if v8 = 207
let v8 = "Server application is busy"
return
endif
if v8 = 208
let v8 = "Prior DDE message must be acknowledged"
return
endif
if v8 = 209
let v8 = "Invalid Advise-link"
return
endif
if v8 = 210
let v8 = "Maximum Advise-link count exceeded"
return
endif
if v8 = 211
let v8 = "Link to data item already exists"
return
endif
if v8 = 212
let v8 = "Received data is invalid or has the wrong format"
return
endif
; some other error condition: just report the error number
let v8 = "DDE Operation failed, error $8"
return
;----------------------------------------------------------------------
; Sub: EndXLConverstn
;
; This subroutine displays an appropriate error message for any DDE
; command errors detected, and then exits to an approprite routine for
; terminating the program.
;
;----------------------------------------------------------------------
:EndXLConverstn
display " ***** Terminating Conversation with Excel ***** ^M^J"
display " ^M^J"
continue
DDE-TERMINATE V0
let v8 = error-code
if v8 <> 0
let v6 = "Terminate"
gosub ErrorHandler
endif
return
;----------------------------------------------------------------------
; Sub: StopProgram
;
; This subroutine stops program execution after notifying the user.
;
;----------------------------------------------------------------------
:StopProgram
display " ***** Reflection Program Terminating ***** ^M^J"
display " ^M^J"
display " =================================================== ^M^J"
quiet command off
stop
|
