Windmill Software Ltd
Windows Engineering Software

Monitor
May 2004

-------------------------Monitor------------------------
The Newsletter for PC-Based Data Acquisition and Control
Issue 70          www.windmill.co.uk            May 2004
--------------------ISSN 1472-0221----------------------

Hello and welcome to the 70th edition of Monitor, the 
newsletter for PC-based data acquisition and control.  
This month we explain how to automatically produce 
reports in Excel.  We also bring back the exhibitions 
listing.

We hope you find the newsletter useful, but should you 
wish to cancel your subscription you can do so at 
https://www.windmill.co.uk/newsletter.html

CONTENTS
========
* Excel Corner: Automatically Generate Reports
* Data Acquisition and Control Exhibitions
* Windmill News: Windmill Interfaces Digital Voltmeter
________________________________________________________
________________________________________________________

Excel Corner: Automatically Generating Reports
________________________________________________________

With just a couple of mouse clicks Excel can load the data 
collected by Windmill, analyse it and present the results 
in a report ready for printing.  To set this up you need 
to create a macro to automatically produce the report. 
This month we give an example of how to do this, which 
you can modify to suit your own analysis requirements.  
You can download our Excel Report Generator from 
https://www.windmill.co.uk/excel/reportwl.zip
Unzip this 
into your Windmill data folder.

To run our Report Generator you need the file 
"mscomct2.ocx" installed in Windows. You may already 
have this on your computer, but if not you can download 
it from 
https://www.windmill.co.uk/excel/mscomct2.zip
To install this component: 
1. Copy the file mscomct2.ocx to the c:\windows\system32 
   folder 
2. In Windows, select Run from the Start menu 
3. Enter: regsvr32 "c:\windows\system32\mscomct2.ocx" 
You should get back a message to say that the component 
has been registered successfully. 

If you are running a windows NT based system you may 
have to change c:\windows to c:\winnt.

We've tried to keep the macro simple, so you can re-use 
its components.  In future editions of Monitor we'll 
give suggestions for improvements, including how to 
automatically determine Windmill Logger's data folder 
and file names.

Using the Report Generator
==========================
1. Open reportWL.xls.  You are asked to select a report.
2. Choose the date from which you want to the report to 
   run.
3. Choose whether you want to create a daily, weekly or 
   monthly report.
4. Select the "Make Report" button.

The daily report gives the mean, maximum and minimum 
values for each channel on the chosen day.  The weekly 
report gives the mean values for each channel for each 
day of the week.  The monthly report gives the mean 
values for each channel for each day of the month.  You 
can modify the macro to provide the information that you 
want.

The macro makes several assumptions about the 
location and names of your data files.  You will need 
to edit it to suit your setup. 

How the Report Generator Works
==============================
The macro is contained in the reportWL.xls file.  When 
you open this a form is shown asking you to choose your 
report type and date.  The Report Generator then opens 
the appropriate Excel template file: dailyWL.xls, 
weeklyWL.xls or monthlyWL.xls.  These contain a title, 
such as "Windmill Logger - Daily Summary" and some 
headings.  To change these close the Report Generator 
(reportWL.xls) and open and edit the template files.  

Editing the Macro
=================
To edit the macro first hide the Report Selector form. 
Open the Visual Basic Editor and edit the code attached 
to the ButtonForm.

The macro assumes that the Excel and Logger data files 
are in the "c:\program files\windmill 
software\windmill\data\" folder. If this is not the case 
then change the first line: Const DEFAULT_ROOTDIRECTORY =

The macro automatically finds the correct data files.  
To do this it assumes that you have periodic logging 
enabled in Windmill Logger, that no files are to be 
overwritten and that your file prefix is 
"myLogFileName". (The file prefix is the data file name 
you chose in Logger.)  To change the file prefix, just 
edit the macro's second line: Const WLFILE_PREFIX =

Our example macro finds logged files produced daily by 
Windmill 6.  You can change this to files produced at 
different intervals (weekly for example) by editing the 
windmill_GetLogFileName procedure.

Earlier versions of Windmill Logger created the names of 
periodically saved files in a different way.  Instead of 
adding a date to the name of the file, they added a 
three-digit number which incremented with each new 
file.  If you are using Windmill 6 or Windmill 4.3 you 
therefore also need to edit the windmill_GetLogFileName 
procedure so Excel can find the correct data files.  

For more on how Windmill names file see the Logger 
program's Help, particularly the "Periodically closing 
a data file" topic.

The appropriate type of report - daily, weekly or 
monthly - is created by the showDaily, showWeekly or 
showMonthly procedures.  These have a number of things in 
common.  They all:
- load the appropriate data files
- determine the number of channels in a file (data columns) 
- determine the number of scans of data (data rows)
- write the date into the report
- write the names of the channels and their units into 
  the report. 
They then perform the appropriate calculations and enter 
the results. The Daily Report Generator, for example, 
calculates the mean, maximum and minimum values for each 
channel.

Taking the mean value as an example.  The Daily Report 
calls the analyse_SumColumn function which returns the 
sum of a column of numbers, and stores this in the 
SumChan variable.  SumChan is divided by the number of 
rows of data (sumCnt) to calculate the mean, and this is 
entered into the appropriate cell of the report as 
follows
reportWkb.Worksheets(1).Cells(START_ROW, iChan + 1).Value 
= SumChan / sumCnt
You can replace the "SumChan / sumCnt" with the results 
of your own calculations.

Printing and Saving the Report
==============================
To print the report simply select the print button.  To 
save the report hide the Report Selector form then 
select Save As from the File menu.

Digital Data
============
In its current form the Report Generator will not work 
with digital data, where values are saved, for example, 
as "ON" and "OFF".  However, you could modify it to 
do so.

Improving the Report Generator
==============================
Our example Report Generator macro should have given you 
some pointers on creating your own.  Much of the code 
can be re-used without any editing at all, including the 
routines that find the number of channels in the data 
files, extract their names and units and write the 
information into the report.

In future issues of Monitor we will show you how to
- Add a menu item that lets you reload the opening form 
  after you have hidden or closed it. 
- Let user's select the analysis summary from Mean, 
  Sum, Maximum value and Minimum value.
- Generate a list of log file prefixes so it's easy to 
  select one to process
- Automatically obtain Windmill Logger's default data 
  folder
  
For more on using Excel with Windmill see
https://www.windmill.co.uk/excel/ 
________________________________________________________
________________________________________________________

Data Acquisition and Control Exhibitions
________________________________________________________

As promised, the exhibitions section has been reprieved 
but is now quarterly rather than every other month.

Sensors Expo and Conference
  7-10 June
  Detroit MI USA
  Features sensor-related sessions, exhibits and special 
  events.
  http://www.sensorsexpo.com

ICA
  8-11 June
  Kuala Lumpur Malaysia
  International exhibition of instruments, control and 
  automation.  Includes measurement, test, sensors, 
  systems, environmental monitoring and robotics.

ISA Expo Control
  09 - 11 Jun 2004
  Mexico DF
  Featuring sensors, transmitters, instrumentation control 
  and electronic systems.
  http://www.isamex.org/

AADECA
  30 Aug - 3 Sep 
  Buenos Aires Argentina
  Control Automation Week organised by the Argentine 
  Association of Automatic Control. For instrumentation, 
  software, engineering, research and education.
  http://www.aadeca.org/

GO. AUTOMATION DAYS
  31 Aug - 03 Sep 2004
  Basel Switzerland
  International automation fair. 
  https://www.mch-group.com/

Remote Sensing
  13-17 September
  Maspalomas, Gran Canaria, Spain 
  Covers remote sensing for agriculture, ocean, 
  environmental monitoring, GIS and so on.
  http://spie.org/
________________________________________________________
________________________________________________________

Windmill News: Windmill Interfaces Digital Voltmeter
________________________________________________________

A geotechnical engineering company is using a digital 
voltmeter to display readings from a pressure 
transducer or a load cell. The engineer connects the 
voltmeter to the computer via its RS232 port, and uses 
the free Windmill software to collect the reading. 
Windmill then outputs the data to the clipboard in 
DDE format, so that it can be pasted into 
an Excel spreadsheet. This saves time and eliminates the 
errors that occur when entering data by hand.

For more on the Windmill RS232 software see
https://www.windmill.co.uk/rs232.html

________________________________________________________
________________________________________________________

* Copyright Windmill Software Ltd
* Reprinting permitted with this notice included
* For more articles see https://www.windmill.co.uk

We are happy for you to copy and distribute this 
newsletter, and use extracts from it on your own web site 
or publication, providing the above notice is 
included and a link back to our website is in place.


An archive of previous issues is at 
https://www.windmill.co.uk/newsletter.html
and an index of articles at 
https://www.windmill.co.uk/newsletter.html

Windmill Software Ltd, PO Box 58, North District Office,
Manchester, M8 8QR, UK
Telephone: +44 (0)161 834 6688
Facsimile: +44 (0)161 833 2190
E-mail: monitor@windmillsoft.com

https://www.windmill.co.uk/

https://www.windmillsoft.com/


Subscribing

To receive Monitor every month please fill in your e-mail address below. We will not pass your address to any third parties, nor send you any unsolicited e-mail.

Previous Issue Next Issue