WinWedge DDE Server Settings
DDE Application Name: Excel
DDE Topic: Book1.XLS (see below)
DDE Command: [RUN(“Book1.XLS!GetSWData”)]
(Remember to set WinWedge up in DDE Server Mode! Click here.)
When you configure WinWedge to pass data to Microsoft Excel using Dynamic Data Exchange (DDE), in most cases, you would configure WinWedge to issue a “DDE Command” to Excel that triggers it to run a VBA subroutine after each data record is received by WinWedge. When you configure WinWedge in DDE Server Mode, you would normally specify the DDE Application Name Excel, the DDE Topic System and the DDE Command [RUN(“VBAMacroName”)] (where VBAMacroName is the name of the Excel VBA subroutine that is to be to run. A small problem with this approach is that when you have two or more instances of Excel running (or multiple workbooks open in the same instance of Excel), the DDE Command from WinWedge will get sent to only the instance of Excel that is the “Active WorkBook” (i.e. the workbook with the focus). The reason for this is because the DDE Topic System is a general DDE Topic for all instances of Excel. When a DDE command is issued to the System topic, Excel passes the command on to the active workbook for processing. If you have more than one instance of Excel running or more than one workbook open, then the DDE command from WinWedge will not be sent to the correct workbook if it not the active workbook. The problem is easily corrected using the following steps:
In WinWedge, when you select DDE Server as the mode, for the DDE Application Name enter: Excel and for the DDE Topic enter the name of the workbook that contains the VBA subroutine (without the file path but including the .XLS or .XLSM extension. For example, if your workbook is named Book1.XLS, you would enter Book1.XLS for the DDE Topic. By using the specific workbook name as the DDE Topic in WinWedge, only that workbook will receive the DDE Commands that are sent from that instance of WinWedge. As an additional measure, also specify the workbook name in the DDE Command. For instance, if your DDE Command is [RUN(“GetSWData”)] change it to [RUN(“Book1.XLS!GetSWData”)] where Book1.XLS is your workbook name. This additional step is required under certain circumstances, such as running both workbooks in the same instance (or window) of Excel, so it is recommended to take this extra precaution to ensure your data is reliably collected into the correct workbook.
Note: Your workbook name cannot have spaces. If your workbook has a name with spaces, rename it before setting the DDE Topic and DDE Command in WinWedge.
Excel Instructions
In addition to changing the DDE Topic in WinWedge, you also need to modify the VBA subroutine in Excel slightly by preceeding all “Sheets” or “Cells” object references in the subroutine with the ThisWorkbook reference. When Sheets or Cells object references are not preceeded by the ThisWorkbook reference, Excel refers to the Sheets or Cells objects of whatever workbook is the active workbook instead of the workbook that contains the VBA code.
For example, suppose the following is your original Excel VBA subroutine:
Sub GetSWData()
Dim R As Long, Chan As Long
Dim WedgeData As String
Dim F1 As Variant
' find the first empty cell in column 1 (column A)
R = Sheets(1).Cells(65000,1).end(xlup).row + 1
' connect to WinWedge
Chan = DDEInitiate("WinWedge", "Com1")
' read in the data from WinWedge
F1 = DDERequest(Chan, "Field(1)")
DDETerminate Chan ' close the DDE channel
' convert the variant array to a string
WedgeData = F1(1)
' Place the data in cell at Row = R, Column = 1
Sheets(1).Cells(R, 1).Value = WedgeData
' add a date/time stamp in column 2
Sheets(1).Cells(R, 2).Value = Now
End Sub
The following is the same as above except with the references to the ThisWorkbook object references added to the code.
Sub GetSWData()
Dim R As Long, Chan As Long
Dim WedgeData As String
Dim F1 As Variant
' find the first empty cell in column 1 (column A)
R = ThisWorkbook.Sheets(1).Cells(65000,1).end(xlup).row + 1
' connect to WinWedge
Chan = DDEInitiate("WinWedge", "Com1")
' read in the data from WinWedge
F1 = DDERequest(Chan, "Field(1)")
DDETerminate Chan ' close the DDE channel
' convert the variant array to a string
WedgeData = F1(1)
' Place the data in cell at Row = R, Column = 1
ThisWorkbook.Sheets(1).Cells(R, 1).Value = WedgeData
' add a date/time stamp in column 2
ThisWorkbook.Sheets(1).Cells(R, 2).Value = Now
End Sub
By adding the ThisWorkbook references to the code in the VBA subroutine, Excel will correctly write all the data to the workbook containing the VBA subroutine and not another workbook open by another instance of Excel (or another workbook that is open in the same instance of Excel).