Windmill Software Ltd
Data Acquisition Intelligence

Excel Data Logging from Serial Instruments

Home
DAQ Catalogue
Excel Charting Tips

Connect RS232, RS485, Modbus and TCP/IP Instruments to Excel

Windmill software collects data from instruments and devices connected to your PC. We have a range of drivers for many types of equipment, and now offer our newsletter subscribers a free trial of software to log data from instruments connected to the serial port over RS232, RS485, Modbus and TCP/IP. This means that you can connect these instruments to Excel.

Using Excel you could average readings over time or a number of sampling points; calculate control outputs; or put data directly into reports. You can collect data with the Windmill Logger or free ComDebug application, and after collection has finished import it into your Excel spreadsheet. Alternatively you can transfer live data, using dynamic data exchange.

Importing Logged Data into Excel | Acquiring Live Data in Excel | Excel Tips and Tricks

Importing Logged Data into Excel

The easiest way to get data into Excel is to open a logged data file after collection has finished. Windmill Logger can periodically close one data file and start another without pausing data collection, so no need to interrupt your tests or experiments to see the data.

1. In Excel, from the File menu select Open.
2. Select "All Files" as the file type.
3. Choose your Logger *.wl file from the Windmill folder. (Or your WM Collect file if using the Microlink 840 Data Logger.)
4. Excel will recognise this as a tab separated value file. Use the default options offered by the spreadsheet for importing the data.
5. Save the data as an Excel file.

Example of data logged by Windmill being analysed in Excel

Acquiring Live Data in Excel

Windmill 7 lets you aquire live data in Excel. There are two ways to do this:

• Copy and paste data links which will continually update with live values
• Write a macro to grab data from Windmill.

Copying and Pasting Live Data Links

The Windmill DDE Panel lets you copy and paste live data into Excel.

1. In DDE Panel, press the Copy to Clipboard button
2. Choose the type of information you want to copy: data values, channel names, data units, alarm settings, etc.
3. Move to Excel and select Paste Special from the Edit menu.
4. Choose to paste as a link. Continually updating live data will appear in Excel.

For more sophisticated tasks, or to log live data in Excel, you can use macros.

Using Macros for Analysis and Control with Excel

Excel offers you analytical capabilities and lets you calculate control values to send back to the hardware.

To try out data acquisition into Excel

1. Load Windmill DDE Panel and start collecting data from your instrument.
2. Load Excel with a clean sheet, and then choose Record New Macro from the Tools > Macro menu. Create an empty macro and then copy and paste the example macros given below into it.

We have three example macros. The first reads data from one channel, for example the current temperature reading. The second shows how to send data to an analogue or digital output. The third reads data from all instruments' channels.

Reading data from one channel

To read data from one channel called Chan_00 and place it into a cell in the spreadsheet, create the following VBA code. (A channel holds one type of data. For a laboratory scale, you might have one channel holding the current weight. For a GPS receiver you might have two channels: one holding latitude and one holding longitude.)

' Read data value from channel "Chan_00" in Windmill
' DDE Panel and write the result in cell A1
Sub DDEread()

' Opens a DDE conversation with the Windmill DDE 
' Panel using the Service Name "Windmill" and the  
' Topic Name "data"
ddeChan = Excel.DDEInitiate("Windmill", "data")

' Requests data from a channel called Chan00.
myData = Excel.DDERequest(ddeChan, "Chan_00")
Sheets("sheet1").Select
Cells(1,1).Value = myData

' Closes the DDE conversation.
Excel.DDETerminate (ddeChan)
End Sub

Sending Data to an Analogue or Digital Output

' Send data in cell A1 to channel "00006" in
' Windmill DDE Panel
Sub DDEpoke()

' Opens a DDE conversation with the Windmill 
' DDE Panel using the Service Name "Windmill"
' and the Topic Name "Data"
ddeChan = Excel.DDEInitiate("Windmill", "data")

' Now send the data.
' Note: a1 should be the cell or range of cells 
' which contain the data, and 00006 should be 
' the name of the output channel
' 
Excel.DDEpoke ddeChan, "00006", Range("a1")

' Closes the DDE conversation.
Excel.DDETerminate (ddeChan)
End Sub

Reading Data from All the Instruments' Channels

This macro repeatedly samples data from all the instruments' channels and stores the data in the Excel worksheet (by default Sheet 1). When the macro is run the operator chooses how many times to sample all the channels, and the interval between taking sets of samples. The macro loops until the required amount of samples have been collected. Make sure DDE Panel is running before starting the macro. You can quickly check DDE links by Copying them to Clipboard in DDE Panel and using Paste Special in Excel, before running the macro.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub SampleData()
'When the number of rows is equal to the number
'of sets of samples requested, the macro will stop.
'If NoOfRows = 1, the first data will be placed in row 1.

NoOfRows = 1

'Ask for number of sets of samples and sample interval.
NoOfSamples = Val(InputBox("Enter how many samples to collect", "Samples"))
SamplePeriod = InputBox("Enter sample interval in secs", "Sample Interval")

' Convert to milliseconds - needed for sleep statement below.
SamplePeriod = SamplePeriod * 1000

'Initiates conversation with DDE_Panel
ddeChan = DDEInitiate("Windmill", "Data")

'Keeps conversation open until the required number of samples
'have been collected.
While NoOfRows < NoOfSamples + 1

'Requests data from all channels and stores it in 
'memory in an array called mydata.
mydata = DDERequest(ddeChan, "AllChannels")

' Ignores any warnings generated
On Error Resume Next

'Finds the lower & upper boundaries of array, to determine the
'number of columns needed to store the data.
Lower = LBound(mydata, 1)
Upper = UBound(mydata, 1)

'Inserts data from the array into a row of cells in Sheet1.
For Column = Lower To Upper
Sheets("Sheet1").Cells(NoOfRows, Column).Value = mydata(Column)
Next Column 

'Waits for the specified sample interval
Sleep SamplePeriod

'Increments number of rows, 
'so next set of samples is inserted in the next row down.
NoOfRows = NoOfRows + 1

'Stops loop when required sets of samples collected.
Wend
DDETerminate (ddeChan)
End Sub

Another good macro for logging data, this time only logging when new data arrives, is given in Issue 56 of Monitor. For more macros see below.

Data Acquisition Example

In the example below Excel is reading data from the Windmill DDE Panel and storing it in cells B4 - B8. These cells are updated automatically whenever DDE Panel takes new samples, at which point Excel calculates the average and standard deviation and stores them in cells B10 and B11.

Excel Tips and Tricks

• Our free Monitor newsletter (ISSN 1472-0221) features a series of Excel Corners, giving hints and tips on using Excel. To subscribe to Monitor fill in your e-mail below.
  
  
• Information on charting with Excel, see our Excel Charting Tips.

Alarms, Email and Web Data

• For how to send an e-mail when a value crosses an alarm level, see Monitor issues 117 and 118.
• For how to get live data from a web page into Excel, see our Excel-to-web page.

Regularly Logging Data

• For how to save data only from, say, the last hour or week, see Monitor issue 50.
• For how to log only when new data arrives, see Monitor issue 56 and 57.
• For how to add a time stamp to logged data, see Monitor issue 74.
• For timestamping in tenths of seconds, see Monitor issue 77.
• For how to have Excel automatically start and stop logging see Monitor issue 73.

Opening Files

• For how to open several files of logged data at once, see Monitor issue 51.

Starting Other Applications

• For how to have Excel automatically start Windmill see Monitor issue 58.

Working with Latitude and Longitude

• For how to convert latitude and longitude to decimal degrees, see Monitor issue 61.

Counters

• For how to reset a counter with Excel, see Monitor issue 98.

Virtual Instruments

• For how to create virtual instruments in Excel, see Monitor issue 81.

Filtering, Averaging, Summing, Condition Matching

• For how to see quick calculation results, see Monitor issue 105.
• For how to filter and sum data, see Monitor issue 36.
• For how to calculate running averages for live data, see Monitor issue 38.
• For how to calculate average values per hour, see Monitor issue 80
• For how to count the number of readings matching a given condition (using CountIf), see Monitor issue 115.
• For how to find all values matching one or more conditions (using an Array Formula), see Monitor issue 116.
• For how to work with arrays of data, see Monitor issue 116.
• For how to quickly arrange data with a pivot table, see Monitor issue 123.

Formatting Data

• For how to highlight out-of-range data, see Monitor issue 78.
• For how to automatically format a cell depending on its value, see Monitor issue 83.
• For how to automatically add borders to data, see Monitor issue 86.

Selecting Cells

• For how to quickly select a table of data, see Monitor issue 79.
• For how to work with alternate rows, see Monitor issue 125.

Entering Data

• For how to enter a line break in a cell, see Monitor issue 52.
• For how to edit the same cell in several sheets at once, see Monitor issue 132.
• For how to create custom number formats in Excel, see Monitor issue 121.
• For how to quickly switch between absolute and relative references, see Monitor issue 131.
• For how to show live data in an Excel form, see Monitor issue 66.
• For how to reduce errors in worksheet references, see Monitor issue 133.

Speeding Things Up

• For tips on naming ranges, see Monitor issue 53 and 55.
• For how to automatically generate reports, see Monitor issue 72.
• For handy keyboard shortcuts, see Monitor issue 143.
• 8 Tips to Speed up Your Spreadsheet, see Monitor issue 127.
• For quick help on formulae, see Monitor issue 134.
• For finding readings with VLOOKUP, see Monitor issue 139.

Everything Else

• For how to recover data from damaged spreadsheets, see Monitor issue 101.
• For how to run a macro from several worksheets, see Monitor issue 99
• For more details of creating DDE links, see the Windmill DDE Panel Help file.
• Other Excel resources are listed on our Links page.

Another Question on using Excel with Windmill Software?

Question:

Excel Version: 
Name:          
Organisation:  
E-mail: