Jump to content

Build Theme!
  •  
  • Infected?

Welcome to What the Tech Forums - Register now for FREE

We're your place for tech questions. Join 87515 others, and join the conversation. Ask questions. Find answers. Share your ideas and opinions. Browse our community. You'll find experts who enjoy helping others. Who explain technical issues in a non-technical way that anyone can understand. Create an account today (it's 100% free)!

Create an Account Login to Account


Photo

Excel 2007 Macro Worksheet_Calculate


  • Please log in to reply
2 replies to this topic

#1 Shawn

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.


#2 Shawn

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

Doug

    Retired Administrator -Tech Team

  • Authentic Member
  • PipPipPipPipPipPipPip
  • 10,051 posts

Posted 05 October 2009 - 03:30 PM

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



Similar Topics: Excel 2007 Macro Worksheet_Calculate     x


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users