SOLVED
Home

=WEBSERVICE() Refresh

%3CLINGO-SUB%20id%3D%22lingo-sub-753937%22%20slang%3D%22en-US%22%3E%3DWEBSERVICE()%20Refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753937%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20got%20a%20workbook%20with%20literally%20hundreds%20of%20cells%20(and%20growing)%20in%20multiple%20worksheets%20using%20%3DWEBSERVICE()%20to%20obtain%20data%20from%20an%20API.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20open%20the%20workbook%2C%20the%20cells%20all%20show%20%23VALUE.%26nbsp%3B%20When%20I%20click%20on%20a%20cell%20and%20edit%20it%2C%20or%20just%20F2%20and%20return%2C%20the%20%23VALUE%20is%20replaced%20by%20the%20value%20returned%20by%20the%20API.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20automate%20this%20refresh%20for%20an%20entire%20sheet%20or%20the%20entire%20workbook%20instead%20of%20a%20cell-by-cell%20update.%26nbsp%3B%20I%20suppose%20I%20could%20write%20a%20macro%20to%20walk%20from%20cell-to-cell%20and%20sheet%20to%20sheet%2C%20refreshing%20cells%20as%20it%20progresses%2C%20but%20am%20hoping%20for%20a%20slightly%20more%20elegant%20solution...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-753937%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754621%22%20slang%3D%22en-US%22%3ERe%3A%20%3DWEBSERVICE()%20Refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754621%22%20slang%3D%22en-US%22%3EI'd%20suggest%20either%3A%20%3CBR%20%2F%3E-%20Do%20a%20global%20search%20and%20replace%2C%20search%20for%20%3D%20and%20replace%20with%20%3D%20(click%20options%20and%20select%20to%20search%20in%20workbook)%3CBR%20%2F%3E-%20Add%20something%20to%20the%20cells%20which%20contain%20WEBSERVICE%20to%20make%20them%20recalculate%20each%20time%2C%20like%20%3DWEBSERVICE(...)%20%2B%200*NOW()%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-754970%22%20slang%3D%22en-US%22%3ERe%3A%20%3DWEBSERVICE()%20Refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-754970%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJan%20Karel%20---%20I%20asked%20for%20an%20elegant%20solution%20---%20global%20search%20and%20replace%20%22%3D%22%20with%20%22%3D%22%20certainly%20qualifies!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20much%20for%20the%20great%20suggestion.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-755026%22%20slang%3D%22en-US%22%3ERe%3A%20%3DWEBSERVICE()%20Refresh%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-755026%22%20slang%3D%22en-US%22%3EExcellent%2C%20glad%20you%20like%20the%20suggestion%20%3A-)%3C%2FLINGO-BODY%3E
MartyK
New Contributor

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...

3 Replies
Solution
I'd suggest either:
- Do a global search and replace, search for = and replace with = (click options and select to search in workbook)
- Add something to the cells which contain WEBSERVICE to make them recalculate each time, like =WEBSERVICE(...) + 0*NOW()

@Jan Karel Pieterse 

Jan Karel --- I asked for an elegant solution --- global search and replace "=" with "=" certainly qualifies!!

 

Thanks much for the great suggestion.

Excellent, glad you like the suggestion :-)