Global RowPtr As Long,
ColPtr As Long ' define global variables
Global Const MyPort = "COM1" ' change
comport if necessary for your configuration
' If Wedge is not set up for COM1, then change
the line above to the right port
Global Const CmdLine = "C:\WinWedge\WinWedge.Exe C:\WinWedge\Config.SW3"
' change the above command line to point to
your copy of wedge and your config file
Sub Auto_Open() ' this sub runs automatically
when you open the spreadsheet
On Error Resume Next ' ignore errors and try to launch WinWedge
RetVal = Shell(CmdLine) ' launch wedge
using command line defined above
' the above line launches and activates the wedge with a config
file: Config.SW3
Application.Wait Now + 0.00002 ' give
wedge time to load and activate itself
AppActivate Application.Caption ' set
the focus back to excel
StartCollecting ' set up excel to collect
data from wedge End Sub Sub StartCollecting() RowPtr = 1: ColPtr = 1 '
initialize global variables
' activate
sheet 1 and set up a DDE link to WinWedge
Sheets("Sheet1").Activate
Sheets("Sheet1").Cells(1, 50).Formula
= "=WinWedge|" & MyPort & "!RecordNumber"
ActiveWorkbook.SetLinkOnData "WinWedge|" & MyPort
& "!RecordNumber", "GetSWData"
' The SetLinkOnData method causes excel to
run the GetSWData macro
'
automatically when new data is
available in the Wedge (when the
'
RecordNumber is updated).
Using the SetLinkOnData
method in excel
'
eliminates the need to have the Wedge
send a Field Postamble DDE
'
Command to excel to cause
it to run the GetSWData sub.Excel will
'
automatically
run the GetSWData sub when the Wedge updates the
'
RecordNumber
DDE item. The RecordNumber DDE item is updated after
'
all other DDE items (input data fields) have been updated. End Sub Sub Auto_Close() '
this macro runs automatically when you close the
spreadsheet StopCollecting '
set up excel to stop collecting data from wedge
On Error Resume Next '
ignore errors
' open a dde link with the wedge:
chan
= DDEInitiate("WinWedge", MyPort)
DDEExecute
chan, "[Appexit]" '
tell wedge to quit
DDETerminate Chan End Sub Sub StopCollecting() Sheets("Sheet1").Activate '
activate sheet1
' remove the dde link from R1C50
Sheets("Sheet1").Cells(1, 50).Formula
= ""
ActiveWorkbook.SetLinkOnData "WinWedge|" & MyPort
& "!RecordNumber", ""
' shut down the SetLinkOnData function by
assigning it an empty string to the macro name
ActiveWorkbook.Save End Sub Sub GetSWData() If RowPtr = 0 Then RowPtr
= 1: ColPtr = 1
' If this is the first time through then initialize
RowPointer to point to Row 1 and ColPointer to point to Column
1 - i.e. collect data into column 1 starting in Row 1
chan
= DDEInitiate("WinWedge", MyPort) '
initiate DDE link with wedge
'
get Field(1) from Wedge:
MyVariantArray
= DDERequest(chan, "Field(1)")
MyString$ = MyVariantArray(1) '
convert to a string
' the DDERequest function in Excel returns a variant array
data type
' this is a peculiarity of Excel - the return type should
be a string
'
data type the above line fixes the Excel "inconsistency" and
'
converts the variant array to a string by assigning element
1 of
'
the variant array to a string variable
' Add your code here to do something with the data maybe?
' or possibly send a command back to the Wedge as in
the following line:
' DDEExecute chan, "[Beep]" ' send a beep command
to the Wedge
Sheets("Sheet1").Cells(RowPtr,
ColPtr + 1).Value = MyString$
' the above line writes the data to cell address:
(RowPtr,ColPtr) in Sheet1
DDETerminate
chan ' terminate the dde link
RowPtr = RowPtr + 1 '
point to the next row down
End Sub
|
