Windmill Software Ltd
Windows Engineering Software

Windmill and Excel

Home
Catalogue
Excel Charting

Route Data from Transducers and Laboratory Instruments into Excel

Windmill software reads data from instruments and devices connected to your PC. We have a range of drivers for many types of equipment, and our generic serial communication driver is now free to our newsletter subscribers. This means that you can acquire data from instruments with RS232 ports, and import it into 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 free Windmill Logger application, and after collection has finished import it into your Excel spreadsheet. Alternatively you can transfer data as it arrives, using dynamic data exchange. To do this use the free as a gateway to transfer data values between your instruments and Excel. (Windmill 4.3 with DDE Panel is free to our newsletter subscribers. Alternatively you can purchase the lastest version of Windmill (V6) with DDE Panel.)

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

Importing Logged Data into Excel

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.
4. Excel will recognise this as a tab separated value file. Use the default options offered by the spreadsheet for interpreting the structure.
5. Save the data as an Excel file.

Acquiring Live Data in Excel

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 select menu item Insert.Macro.Module (Excel 5 and 95) or Tools.Macro.Visual Basic Editor to open the VBA editor.
3. To read data from one channel called Chan_00 which is connected to the Windmill DDE Panel, and place it into a cell in the spreadsheet, create the following VBA code.

   ' 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
   

4. To send data to an analogue or digital output channel, called 00006, create the following VBA code. (Note: You can send up to 7 characters. Values with more digits will be rounded up or down. For example 1.234569 will output as 1.23457.)

   ' 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
   

   
   
5. 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.

   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")
   
   'Coverts interval to fraction of 24 hours 
   '(Excel expects times in this format).
   SamplePeriod = (Val(SamplePeriod)) / 86400
   
   '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")
   
   'Selects first sheet in default workbook.
   Sheets("Sheet1").Select
   
   '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.
   For Column = Lower To Upper
   Cells(NoOfRows,Column).Value = mydata(Column)
   Next Column 
   
   'Waits for the specified sample interval.
   Application.Wait (Now + 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

For more macros, or help with the DDE protocol, see the Help file provided with the free DDE Panel software.

If you have created any macros to use with Windmill, which you'd be willing to share, we'd like to include them in our Help file. Please e-mail monitor@windmillsoft.com.

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.
• For how to send an e-mail when a value crosses an alarm level, see Monitor issues 117 and 118.
• 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 open several files of logged data at once, see Monitor issue 51.
• For how to have Excel automatically start Windmill see Monitor issue 58.
• For how to have Excel automatically start and stop logging see Monitor issue 73.
• For how to convert latitude and longitude to decimal degrees, see Monitor issue 61.
• For how to reset a counter with Excel, see Monitor issue 98.
• For how to create virtual instruments in Excel, see Monitor issue 81.
• For how to get live data from a web page into Excel, see our Excel-to-web page.
• 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 highlight out-of-range data, see Monitor issue 78.
• 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 quickly arrange data with a pivot table, see Monitor issue 123.
• 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.
• For how to quickly select a table of data, see Monitor issue 79.
• For how to enter a line break in a cell, see Monitor issue 52.
• For tips on naming ranges, see Monitor issue 53 and 55.
• For how to show live data in an Excel form, see Monitor issue 66.
• For how to automatically generate reports, see Monitor issue 72.
• For how to run a macro from several worksheets, see Monitor issue 99
• For how to recover data from damaged spreadsheets, see Monitor issue 101.
• For how to edit the same cell on several sheets, see Monitor issue 102.
• For how to see quick calculation results, see Monitor issue 105.
• For how to work with arrays of data, see Monitor issue 116.
• For how to create custom number formats in Excel, see Monitor issue 121.
• For how to work with alternate rows, see Monitor issue 125.
• 8 Tips to Speed up Your Spreadsheet, see Monitor issue 127.
• For how to quickly switch between absolute and relative references, see Monitor issue 131.
• For how to edit the same cell in several sheets at once, see Monitor issue 132.
• For how to reduce errors in worksheet references, see Monitor issue 133.
• For quick help on formulae, see Monitor issue 134.
• For more details of creating DDE links, see the Windmill DDE Panel Help file.
• For answers to some technical support questions we've been asked on using Excel with Windmill, see our FAQ.
• Other Excel resources are listed on our Links page.

Another Question on using Excel with Windmill Software?

Question:

Excel Version: 
Name:          
Organisation:  
E-mail: