Updating a cell that uses a .NET add-in function
I've created an automation add-in for Excel using .NET. This add-in
contains a function called GetValue. The GetValue function takes two parameters, hits a SQL database, and returns a value to the cell. I've successfully added the add-in to Excel. I've also successfully utilized the add-in function that returns data. For instance, I place the following function call in an Excel cell... =MyAddIn.GetValue("Q1", "SALES") ....and it returns me the Q1 sales number from the database. I save this Excel file and close it. Now, I go into the database and change the Q1 sales data to a different value. I open up the Excel file described above. My problem is that the cell with the "=MyAddIn.GetValue("Q1", "SALES")" function does not reflect the changed value. I have to physically click on the cell and hit enter for the data to refresh. My question is this: How can I get my data to refresh on the opening of the Excel file? Can I build something into the add-in that will allow me to do this? Thanks, Alex Agran |
Updating a cell that uses a .NET add-in function
If it is returning a number
=MyAddIn.GetValue("Q1", "SALES")+(rand()*0) since rand is a volatile function, it will cause it to update on every recalc. If that is too much, then you would probably need to use application level events in your addin to force a full recalc when the workbook is opened. http://www.cpearson.com/excel/appevent.htm Chip Pearson's page on application level events. -- Regards, Tom Ogilvy " wrote: I've created an automation add-in for Excel using .NET. This add-in contains a function called GetValue. The GetValue function takes two parameters, hits a SQL database, and returns a value to the cell. I've successfully added the add-in to Excel. I've also successfully utilized the add-in function that returns data. For instance, I place the following function call in an Excel cell... =MyAddIn.GetValue("Q1", "SALES") ....and it returns me the Q1 sales number from the database. I save this Excel file and close it. Now, I go into the database and change the Q1 sales data to a different value. I open up the Excel file described above. My problem is that the cell with the "=MyAddIn.GetValue("Q1", "SALES")" function does not reflect the changed value. I have to physically click on the cell and hit enter for the data to refresh. My question is this: How can I get my data to refresh on the opening of the Excel file? Can I build something into the add-in that will allow me to do this? Thanks, Alex Agran |
Updating a cell that uses a .NET add-in function
Perfect! Thanks...
Alex Tom Ogilvy wrote: If it is returning a number =MyAddIn.GetValue("Q1", "SALES")+(rand()*0) since rand is a volatile function, it will cause it to update on every recalc. If that is too much, then you would probably need to use application level events in your addin to force a full recalc when the workbook is opened. http://www.cpearson.com/excel/appevent.htm Chip Pearson's page on application level events. -- Regards, Tom Ogilvy " wrote: I've created an automation add-in for Excel using .NET. This add-in contains a function called GetValue. The GetValue function takes two parameters, hits a SQL database, and returns a value to the cell. I've successfully added the add-in to Excel. I've also successfully utilized the add-in function that returns data. For instance, I place the following function call in an Excel cell... =MyAddIn.GetValue("Q1", "SALES") ....and it returns me the Q1 sales number from the database. I save this Excel file and close it. Now, I go into the database and change the Q1 sales data to a different value. I open up the Excel file described above. My problem is that the cell with the "=MyAddIn.GetValue("Q1", "SALES")" function does not reflect the changed value. I have to physically click on the cell and hit enter for the data to refresh. My question is this: How can I get my data to refresh on the opening of the Excel file? Can I build something into the add-in that will allow me to do this? Thanks, Alex Agran |
All times are GMT +1. The time now is 04:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com