Jump to content

Build Theme!
  • Infected?


Hey there! :wub: Looks like you're enjoying the discussion, but you're not signed up for an account. When you create an account, we remember exactly what you've read, so you always come right back where you left off. You also get notifications, here and via email, whenever new posts are made. You can like posts to share the love. :D Join 92768 other members! Anybody can ask, anybody can answer. Consistently helpful members may be invited to become staff. Here's how it works. Virus cleanup? Start here -> Malware Removal Forum.

Try What the Tech -- It's free!


Excel 2007 Macro Worksheet_Calculate

  • Please log in to reply
2 replies to this topic

#1 Shawn


    New Member

  • New Member
  • Pip
  • 2 posts

Posted 28 September 2009 - 02:41 PM

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

Edited by Shawn, 28 September 2009 - 02:50 PM.


Register to Remove

#2 Shawn


    New Member

  • New Member
  • Pip
  • 2 posts

Posted 29 September 2009 - 09:22 AM

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.

#3 Doug


    Retired Administrator -Tech Team

  • Tech Team
  • 10,057 posts

Posted 05 October 2009 - 03:30 PM

Thanks for posting that you reached a solution. Glad you got it sorted. Best Regards
The help you receive here is free.
If you wish, you may Donate to help keep us online.

Related Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users