EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Forum for discussing PicoLog
Post Reply
Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS
FIGURE 1 - PICOLOG METERING
FIGURE 1 - PICOLOG METERING
A major limitation of Picotech Data Loggers driven by Picolog is (A) the very limited number of hard wired outputs (for instance only 2 on the 1012 and 4 on the DrDAQ, to name a few) and (B) the absence of hard wired outputs for the calculated channels.
PART 1
The two previous posts:
topic27591.html
and
topic27741.html
tried to give solutions to the limitation described above. These solutions depend on the on screen detection of the Picolog alarm and therefore require extreme care for not losing the detection point which can be caused by normal PC usage for other purposes. Furthermore only digital ON/OFF signals could be handled.

Conversely the proposed facility, as is, provides four digital or analog outputs (of which three are used in the present example) but this number can be easily increased by simple VB modification of an Excel spreadsheet. Operation, which uses an Arduino slave microprocessor, can be described as follows.

When Picolog is in operation, its data is present on the DDE Bus and can be read by a VB configured Excel spreadsheed. See, for example:
topic21181.html
and
topic20071.html
and
topic21151.html

In our facility the Excel spreadsheet "PICOLOG METERING" reads and displays Picolog data, shown in green colour. The Arduino slave, using a second Excel spreadsheet "ARDUINO READER" (a modified version of the generally available PLX-DAQ facility) reads the Picolog data and, to demonstrate operation, writes it back on PICOLOG METERING in real time (blue colour). Excel can do data processing in real time as well (red colour).

Now that the data is available on Excel and on Arduino, any output application is possible, both analogue or digital. Excel can further process the data as required and the same can do Arduino. For instance math calc channels can be shifted to Excel and required alarms can be outputted by Arduino, while Picolog carries on with the recording/plotting processes.

The following posts in this series will describe the operation of the Picoslave facility, while applications of this facility will be described in the "projects" section.
Attachments
FIGURE 2 - PICOSLAVE ADDITIONAL DETAIL
FIGURE 2 - PICOSLAVE ADDITIONAL DETAIL
Last edited by Glovisol on Thu Feb 09, 2017 11:54 am, edited 2 times in total.

Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

Re: EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS
FIGURE 3 - DETAILED SYSTEM DESCRIPTION
FIGURE 3 - DETAILED SYSTEM DESCRIPTION
PART 2

Figure 3 further explains the setup. The blocks diagram of this facility is uploaded below in Figure 5. To limit the complexity of the Arduino sketch, only whole numbers are translated. However it is possible and easy to translate decimal numbers by simply multiplying them by a known factor and then use Excel to divide them by the same factor. In Figure 3 above, Picolog reads temperature as 25.6 °C, but Picolog in the math channel "Temperature processing" multiplies this figure by 100 to obtain: 2564, then writes this value in Cell B9 of the Excel Picolog Metering (green colour). Note that while the Picolog channel is set for one decimal place, Excel reads two decimal places, so we have "2564" instead of "2560".

Arduino then reads Cell B9 and, to demonstrate this action, writes this value in Raw Read Cell D3. Then in True Read Cell E3 the processed value 2564 is divided by 100, to obtain the true temperature of 25.64°C (red colour).

The most important feature of this facility (which makes the dream wish list come true) see:
topic25681.html
is that we are bringing out the result of TWO PROCESSING ACTIONS IN SERIES and of course we can use the processed data for any purpouse dictated by the technical problem at hand
.

Depending on the sketch, the Microprocessor will output digital ON/FF signals for driving relays or analogue signals (PWM) for servo or any other application.

In future posts the facility set-up procedure will be examined and the operating files & sketch will be uploaded.
Attachments

[The extension bmp has been deactivated and can no longer be displayed.]

FIGURE 4 - PICOLOG METERING DETAIL
FIGURE 4 - PICOLOG METERING DETAIL
Last edited by Glovisol on Thu Feb 09, 2017 11:49 am, edited 1 time in total.

Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

Re: EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS

PART 3
The facility set - up procedure is as follows.

NOTE: the facility has been extensively tested and works faultlessly with the following:
IBM X61 - Laptop PC.
Windows XP/SP3 Operating System.
Microsoft Excel 2003
Arduino 1.0 IDE
Arduino 1 / Genuino 1 / Arduino - Genuino R3

1- Download the Arduino sketch Picoslave_12. DOC (this is a Word file because the blog does not accept Arduino sketches) carefully copy it and paste on an open Arduino blank instance.
2- Compile the sketch to verify nothing has been missed/altered in the transfer.
3- Download: (a) Picolog Reader.xls and (b) Picolog Metering.xls.
4- If you have not set up your Pico DA unit, you may download the Picoslave 2.PLW example, which works with the DrDAQ.
5. Open Picolog & check it is working. Open Picoslave 2.PLW to see how this example is set up.
6. Open Arduino Reader.xls. The ActiveX warning will come up: click OK. Note that the red colour command console "ARDUINO+EXCEL" will turn up.
7. Open Picolog Metering .xls. This window will completely hide the Arduino reader.xls window: no problem here, we do not need to see it, as it works in the background and is controlled by the red stamp console, which must be accessible as shown.
8.Open & load Arduino sketch Picoslave_12.
9. Click the "UPDATE READINGS EVERY SECOND" button: the Titles/Data list of Picolog will appear on the Picolog Metering window and data will be updated on a continous basis.
10. Click "CONNECT" button on the red console: data present on the spreadsheet will be instantly reset and immediately after Arduino data will appear and the "Raw read" column will fill and be automatically updated.
11. The "True Read" column is erased every time the "CLEAR" button is pressed, or when Excel is closed & re-opened. It is not erased when the "CONNECT" button is pressed, even if the "auto reset" is ON . In our example the E column is set up as follows: E2 = D2/1000 - E3 = D3/100 - E4 = D4.
Attachments
Picoslave 2.PLW
(20.78 KiB) Downloaded 570 times
Picolog Metering.xls
(41 KiB) Downloaded 548 times
Arduino Reader.xls
(86 KiB) Downloaded 565 times
Picoslave 12.1 Sketch.doc
(30.5 KiB) Downloaded 565 times
Last edited by Glovisol on Fri Feb 10, 2017 8:52 am, edited 3 times in total.

Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

Re: EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS

Uploaded a faster version of Picoslave, Picoslave 14.1, operating at 128,000 baud.
Attachments
Picoslave 14.1 Sketch.doc
(31 KiB) Downloaded 534 times

Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

Re: EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS

PART 4
PC screen of ON/OFF Picoslave relay application
PC screen of ON/OFF Picoslave relay application
Herewith enclosed Arduino sketch in WORD format covering an ON/OFF Picoslave application actuating three relays on calculated alarm channels of Picolog driving a DrDAQ DA unit. The Excel files already downloaded can again be used for this application and the setting - up procedure is the same as that already given in Part 3.
Picoslave APP_2.doc
Arduino sketch for Picoslave ON/OFF appication
(35 KiB) Downloaded 524 times
Last edited by Glovisol on Mon Feb 13, 2017 6:04 pm, edited 1 time in total.

Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

Re: EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS

PART 5
PC Screen showing Picoslave with two analogue PWM outputs
PC Screen showing Picoslave with two analogue PWM outputs
Herewith enclosed Arduino sketch in Word format for two analogue PWM outputs derived from Picolog data. The setting - up procedure is the same as that already given in Part 3.

By mapping the Picolog temperature processing voltage range 0 - 9999 [mV], corresponding to 0 - 99.9°C into the 0 - 255 Arduino PWM range, the PWM percentage output of Pin 5 matches the temperature reading: e.g. PWM 24.3% corresponds to 24.3°C.
Attachments
Picoslave_15.0 Sketch Analogue.doc
(32 KiB) Downloaded 526 times

Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

Re: EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS

All work presented until now has been tested with Arduino 1/ Arduino 1 IDE and Microsoft XP/SP3 OS. While there is no problem with Digital operation, the Analogue version of Picoslave has the limitation that the max. PWM output resolution is 8 bits, or 5000/255 = 20 mV approx.

With Arduino ZERO the PWM resolution could easily go up to 12 Bits, or
3300/4095 = 0.8 mV approx., but then it is mandatory to upgrade the Arduino IDE to 1.6.7 or later and to go from XP to at least W7 OS. Work is underway to test/modify all software already presented to make it useable under the new conditions.

Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

Re: EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS
Picoscope Screen of Arduino ZERO 12 Bit PWM output on two channels
Picoscope Screen of Arduino ZERO 12 Bit PWM output on two channels
PART 6

This technique, which can provide up to six PWM output channels at a resolution up to 16 Bit, has been tested with the following.

- Picotech DrDAQ + Picolog (other loggers such as 1012, 1016, etc. O.K.).
- Arduino/Genuino Zero + Arduino IDE 1.6.7.
- Microsoft W7 Operating System.
- Excel 2003 (.xls).


1) The latest Excel version that can be used is 2003, this is because the Excel file made available in the SDK has been written with Excel '95 and there are compatibility issues with Excel 2007 or later. The file: Picolog metering .xls will not read DDE data if opened with Excel 2007 or later and will give an error message. I circumvented this problem by installing Excel 2003 and using it in compatibility mode (Compatibility = Windows XP/SP3). The two Excel files must be opened from inside Excel 2003, as explained in the operating procedure.

2) The Arduino Zero sketch is naturally far more complex than that uploaded for Arduino Uno. Detailed theory of operation and sketch build-up procedure & explanation is separately available in the following tutorial series:

topic24051.html

More in the next post, PART 7.
Last edited by Glovisol on Mon Feb 20, 2017 5:18 pm, edited 1 time in total.

Glovisol
Advanced User
Advanced User
Posts: 0
Joined: Sat May 11, 2013 10:03 am
Location: San Marzano Oliveto, Asti, Italy

Re: EXCEL VB READS PICOLOG & PROVIDES HARD WIRED OUTPUTS

Post by Glovisol »

VB SOFTWARE READS PICOLOG DATA & DRIVES ARDUINO PICOSLAVE TO PROVIDE HARD WIRED OUTPUTS

PART 7

The Arduino Zero sketch is uploaded below. It is, as usual in WORD .doc format and must be lifted and pasted on a blank Arduino 1.6.7 or later instance. In the example given, the sketch handles two channels coming from DrDAQ and Picolog: the first for EXT. 1 voltage reading and the second for the internal temperature sensor. But this sketch is capable of handling up to six channels, as documented in the internal comment. It would be a good exercise to modify the sketch to increase/diversify the operating channels.

The facility set-up procedure is as follows.
1- Download the Arduino sketch Picoslave_APP_16. DOC, carefully copy it and paste on an open Arduino blank instance.
2- Compile the sketch to verify nothing has been missed/altered in the transfer.
3- Download: (a) Arduino Reader.xls and (b) Picolog Metering.xls. Make sure you have Excel 2003 or earlier on your OS. Open a BLANK Excel (.xls) spreadsheet and open the files from inside Excel in this order: Arduino Reader .xls and Arduino metering .xls. This window will completely hide the Arduino Reader.xls window: no problem here, we do not need to see it, as it works in the background and is controlled by the red stamp console, which must be accessible as shown.
4- If you have not set up your Pico DA unit, you may download the Picoslave 2.PLW example, which works with the DrDAQ.
5. Open Picolog & check it is working. Open Picoslave 2.PLW to see how this example is set up.
8.Open & load Arduino sketch Picoslave_APP_16. Please read the long notice giving detailed operating info. You can choose the value of PWM resolution as shown there.
9. Click the "UPDATE READINGS EVERY SECOND" button: the Titles/Data list of Picolog will appear on the Picolog Metering window and data will be updated on a continous basis.
10. Click "CONNECT" button on the red console: data present on the spreadsheet will be instantly reset and immediately after Arduino data will appear and the "Raw read" column will fill and be automatically updated.
11. The "True Read" column is erased every time the "CLEAR" button is pressed, or when Excel is closed & re-opened. It is not erased when the "CONNECT" button is pressed, even if the "auto reset" is ON . In our example the E column is set up as follows: E2 = D2/1000 - E3 = D3/100 .
Attachments
Picoslave_App_16_ZERO-Analog.doc
ARDUINO ZERO SKETCH
(42 KiB) Downloaded 518 times

Post Reply