Hello Helpers!

I have an external data source feeding data into the Excel 2007 using DDE (Dynamic Data Exchange). Since I need to re-capture the data and store it into different places (writing to Access 2007, no issue here), I have to use the macro and event trigger "Worksheet_Calculate". The workbook I have is name "Feeder.xlsm", and the sheet name (with data feed) is called "Data_Feeder".

However, this event trigger has 2 big issues:
1) It fires the macro with various errors whenever I am working on different workbook. My analysis told me it was due to wrong trigger on wrong workbook/worksheet. This lead me to a solution below.

2) I tried fixing the issue (1) with code detector "If Sheets.Item(1).Name = "Data_Feeder" Then ....".

This work, but with another major issue -- When I am working in a seperate Workbook, it seems like the the macro stop working (maybe due to the code detector?), even though I can verify that the workbook Feeder.xlsm and worksheet Data_Feeder is still being fed with live DDE data. Probably due to the code detector sensing the wrong workbook and stop the macro?

Therefore, my question is, is there a better method or trigger event for DDE that would not jeopardize my work on other workbook? Or if you have any suggestion to resolve the Code Detector issue mentioned in (2)?

My last question is, when working in VB/Macro editor, I clearly created and saw that the Macro and codes are for the workbook Feeder.xlsm and worksheet Data_Feeder. But why when I opened another workbook, this would cause various errors! It seems like all these workbooks share the SAME Macro and Codes even though I clearly wrote these codes for 1 particular workbook and worksheet. Is this a bug or my lack of understand of Excel macro? I am a newbie here.

Many Thanks!
Sean

This post has been edited by Shawn: Sep 28 2009, 02:50 PM

Helpers,

Don't worry, I already resolved it. I use Workbooks collection to first loop through to ID the correct workbook. The I set a new reference to this correct workbook. From then on, all my calculations are based on sheets from this particular workbook.

All are good. Thanks for stoppin by.

Thanks for posting that you reached a solution.
Glad you got it sorted.

Best Regards