I wrote a function to pull data from an external table. I am running into
the following issue - when the data in the table updates, my spreadsheet does
not. While I do not want my spreadsheet to update automatically, I would
like to update it on a demand basis. I have tried the refresh data option
under the Data menu, but it is "grayed" out and not available. I have
likewise tried the Refresh All button under the External Data menu. While
this button "clicks", my information does not updage.

Any help would be greatly appreciated!

Refresh external data only works on querytables created through MSQuery or
created through code (your function does not qualify as a querytable). If
you want to recalculate your formula cells on demand, you can set up a
commandbutton or toolbar button or the equivalent to trigger the
recalculation, then use the Range(FormulaCells).Calculate method to force a
recalculation of your user function (or recalculate the whole sheet, if you
do not know in advance where the cells are).

"Chris Brown" wrote:

I wrote a function to pull data from an external table. I am running into
the following issue - when the data in the table updates, my spreadsheet does
not. While I do not want my spreadsheet to update automatically, I would
like to update it on a demand basis. I have tried the refresh data option
under the Data menu, but it is "grayed" out and not available. I have
likewise tried the Refresh All button under the External Data menu. While
this button "clicks", my information does not updage.

Any help would be greatly appreciated!

So if my cell range was A2:V100, in the macro, the line would appear as
Range(A2:V500).Calculate?

"K Dales" wrote:

Refresh external data only works on querytables created through MSQuery or
created through code (your function does not qualify as a querytable). If
you want to recalculate your formula cells on demand, you can set up a
commandbutton or toolbar button or the equivalent to trigger the
recalculation, then use the Range(FormulaCells).Calculate method to force a
recalculation of your user function (or recalculate the whole sheet, if you
do not know in advance where the cells are).

"Chris Brown" wrote:

I wrote a function to pull data from an external table. I am running into
the following issue - when the data in the table updates, my spreadsheet does
not. While I do not want my spreadsheet to update automatically, I would
like to update it on a demand basis. I have tried the refresh data option
under the Data menu, but it is "grayed" out and not available. I have
likewise tried the Refresh All button under the External Data menu. While
this button "clicks", my information does not updage.

Any help would be greatly appreciated!