MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Triggering a change Event based on field change of DDE link

  Asked By: Deepu    Date: Dec 08    Category: MS Office    Views: 3727

I have a PLC controller (direct serial connect) feeding data to Excel via a
DDE server. I've successfully connected Excel to the PLC and have test data
being fed to the worksheet. (A simple cycle count sending a change every 10
seconds). I am attempting to trigger an event to drive the capture of data
each time the cycle count increments. I've written numerous variations of
Worksheet_Change(ByVal Target As Range) in VB all of these scripts work as
expected, EXCEPT, when applied to a cell linked to the DDE. I've also tried
triggering an event by referencing the DDE cell and adding zero to see if I
could trigger a Worksheet_Calculate event. I've also opened another
workbook and linked to the DDE field then linked back to the original sheet,
to try and trigger something. It seems that everything linked to that DDE
cell taints it so that none of the events fire.

I was hoping that someone else could pitch some other ideas to try.



3 Answers Found

Answer #1    Answered By: Rene Sullivan     Answered On: Dec 08

I'm not familiar with doing this, but I wouldn't have expected a worksheet
change event  or recalculation to be triggered by a cell change  made via DDE.

Is this the only way your PLC can update the spreadsheet? If so, you may
need to put a timed loop into VBA to periodically check the contents of the
cells that the PLC fills, and do a workbook calculate if it has changed from
last time.

However, I'm not even sure that a workbook calculate would do anything, as
it might not think the cells have been changed. You might need to "dirty"
then cell  to get Excel's attention - e.g. from VBA, set recalculation to
manual, set the cell to its value plus one, set the cell back  to what it
should be, set recalculation to automatic, do a workbook calculate. (I
can't remember whether it's workbook calculate or application calculate -
just don't do a worksheet  calculate.)

The other approach is to look for events  that DDE does trigger  - there must
be some. Google will know.

Answer #2    Answered By: Milton Robinson     Answered On: Dec 08

One thought I had was to see if Excel will notice a change  in color
(format change). I have an incoming bit where conditional formating will
change cell  color. Maybe I can grab that change. Also I'm thinking maybe I
can grab a change directly though VB. Which I'll also test  today. A timed
loop may not be the answer because I'm trying to track timer output from 50
machines, each one sending  its own timer seqence, I was counting on Excel to
give me a time  stamp for each one. I've contacted the manufacturer also but
they have not replied yet. I'll post updates

Answer #3    Answered By: Vinit Online     Answered On: Dec 08

On the idea of the timer loop ,,,

You wouldn't try to synchronise it with the inputs - just let it free-run.
I assume that it's not terribly important to display a change  immediately
you get it - a second or so probably wouldn't make much difference. I.e.,
if you ran the timed loop at say 1 second and checked all the inputs, you'd
be no more than a second late in displaying changes when they arrived.

However, it would certainly be better to trap an event  if one is generated
by the DDE.

I don't think Excel will ever notice a colour change. It isn't a cell
change as such, and no event will be generated, even if you do it through
the keyboard. (I think.)