Questions? Search our site or call us at 1-800-722-6004

Collecting a Single Field of Data into Excel

This basic example demonstrates using DDE to automate data collection in Excel. When WinWedge receives a record while in DDE Server Mode, it will send a command to the application/topic specified in the WinWedge settings (Mode > DDE Server Mode). Usually this command is running a subroutine. For instance, the command [RUN("GetSingleField")] sent to an application Excel and a topic of System will run the subroutine in this Example. Make sure that the COM Port being referenced is the same COM Port your device is on! If your device has multiple fields of data, please review this example.

WinWedge DDE Server Settings

Remember to set WinWedge up in DDE Server Mode! Click here.

DDE Application Name: Excel
DDE Topic: System
DDE Command: [RUN("GetSingleField")]

Excel Instructions

Create or edit a macro module in Excel and enter the following code in the module. (To create a new module click on Insert > Module from the Menus in the Visual Basic Editor Window.)

Sub GetSingleField()

    'Dimension all variables
    Dim R As Long, Chan As Long, vDat As Variant, sDat As String

    ' Find the next empty row in Column A
    R = ThisWorkbook.Sheets("Sheet1").Cells(65000, 1).End(xlUp).Row + 1

    'Establish DDE link to WinWedge on COM1
    Chan = DDEInitiate("WinWedge", "COM1")
   
    ' The following 3 lines of code can be duplicated to collect
    ' additional fields of data into different columns.
   
    vDat = DDERequest(Chan, "Field(1)")                 ' Request the data from Field(1) in WinWedge
    sDat = vDat(1)                                      ' Convert the data into a string
    ThisWorkbook.Sheets("Sheet1").Cells(R, 1).Value = sDat     ' Put data in cell at Row = R, Column = 1
   
    DDETerminate Chan                                   ' Terminate the DDE link
   
    ' You can also insert a date/time stamp in Column B by uncommenting the following line.
    ' ThisWorkbook.Sheets("Sheet1").Cells(R, 2).Value = Now

End Sub

The example above sets up WinWedge to issue a DDE command consisting of the Excel "RUN" command forcing Excel to run the subroutine "GetSingleField()" after each data record is received from your serial device. The "GetSingleField" subroutine performs a DDERequest to WinWedge that returns the contents of FIELD(1) to a variable named "vDat". The data is then converted to a string variable, sDat, and assigned to a cell in the first worksheet. If you want to send the data to a specific Excel sheet (not the first one), replace Sheets(1) with Sheets("MySheetName") in any of these code examples - where MySheetName is the actual name of your sheet.


Categories: Macro / Code Sample, Microsoft Excel, Data Collection, Serial (RS232), WinWedge

Last Updated: 2014.06.06

Need more help?

Don't hesitate to call or email us with your questions

Our office is open 9AM - 5PM Monday Through Friday (E.S.T.)

Technical Support: 215-496-0202

Toll-Free: 1 (800) 722-6004
Skype: taltech1 (Voice only)
Email: support@TALtech.com