Jul 14 2019 08:09 AM
I've got a workbook with literally hundreds of cells (and growing) in multiple worksheets using =WEBSERVICE() to obtain data from an API.
When I open the workbook, the cells all show #VALUE. When I click on a cell and edit it, or just F2 and return, the #VALUE is replaced by the value returned by the API.
Is there a way to automate this refresh for an entire sheet or the entire workbook instead of a cell-by-cell update. I suppose I could write a macro to walk from cell-to-cell and sheet to sheet, refreshing cells as it progresses, but am hoping for a slightly more elegant solution...
Jul 15 2019 05:07 AM
SolutionJul 15 2019 08:40 AM
Jan Karel --- I asked for an elegant solution --- global search and replace "=" with "=" certainly qualifies!!
Thanks much for the great suggestion.
May 20 2023 11:17 AM - edited May 20 2023 11:21 AM
I was having the same problem.
At first I was afraid that every time a cell was changed WEBSERVICE() would make a new request and quickly block the API (I have a daily limit), then I found out the opposite was true, no update.
Ideally it would be nice if there was a "update frequency" parameter in the function, or that it refreshed along side the power query and queries stuff.
@Jan Karel Pieterse your "=WEBSERVICE(...) + 0*NOW()" is automatic and incredibly elegant, works like a charm.
Jul 15 2019 05:07 AM
Solution