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

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

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