Consuming DDE and providing XML

[teddysnips]

SCENARIO

A factory production line uses RSLINX software to gather data from PCI
cards and expose it through DDE.

An Excel spreadsheet consumes this via Copy and Paste of the link from
RSLINX.

This spreadsheet is then saved in XML format.

This XML file is in turn consumed by a web page that displays the data
gathered by the RSLINX software. The web page is set to refresh at 30s
intervals.

PROBLEM

The Excel spreadsheet is updated in real time (you can see the numbers
change as the data is pumped in by RSLINX), but of course the XML file
is not updated until the file is saved, so the data on the web page is
not updated unless and until the spreadsheet is saved.

The client obtained a cheap "autosave" Excel plug-in to save the
spreadsheet periodically, but it doesn't work reliably.

DESIRED SOLUTION

Remove the Excel middleware and replace it with an applet that will
consume the DDE data directly and transform it into the XML file
required by the web page. I was thinking of a windows service or
something similar. They don't want a console application/scheduled
task solution. The budget is TINY.

ACCEPTABLE SOLUTION

A reliable means of saving the XML file periodically. The most
important word in that sentence is "reliable". A background timer in a
macro? A save triggered by a cell's Change event firing?

 

[Cameron Dorrough]

Why are you using DDE? OPC would be much more useful..

Cameron

 

[NickHK]

Depends what's wrong the current autosave plug-in. Can you access the code
and correct/improve it ?

If you write your own code with a timer, you can do whatever you want. You
are still limited by Excel overall stability, which generally is pretty
good, but it was never designed to run 24/7.

Apart from that, VB6 supports DDE and XML output can be achieved easily. You
could write it as a service also. Depends how much effort you want to put
in.

NickHK

 

[Paul M]

ACCEPTABLE SOLUTION

A reliable means of saving the XML file periodically. The most
important word in that sentence is "reliable". A background timer in a
macro? A save triggered by a cell's Change event firing?

I had this problem and solved it a bit differently which worked in my
application. It might spark an idea you can use within the
spreadsheet. I linked a spreadsheet field to the data as you have
letting it update constantly. I had one field for live force data and
another for live position data. I wanted to take a force reading when
the position field reached a value. I was gathering data for a xy
graph. I wanted 10 points evenly spaced between say 1 and 100 cm, so I
triggered a force read at 10, 20,30 etc. I wrote a formula that read
something like If A1(position instantaneous) =A2(trigger) then
A3=A4(force instantaneous) and A5=A1. This formula was copied down
rows for each value I wanted the capture.

I had to fine tune the thing to get what I wanted. For instance if the
position changed so fast as to never read 10, then you would not
capture a force reading at that position. I cured this by making the
formula a range of values. I added the ability to fill in the trigger
points automatically and an external trigger for the start of the test
and a timer to time out if the test did not complete within some
value. One the last point was captured, I had that event trigger a
save command. These spreadsheets were used as test reports in a
factories QA Lab. No third party software was used. The data came from
an internal IO card. It worked amazingly well. As long as the computer
was running, all the operator had to do is push a hard switch to start
the test. He did not even have to have the monitor on. It was idiot
proof.