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