Forum Discussion
Excel WEBSERVICE not working with +0*NOW()
I have WEBSERVICE on a spreadsheet displaying some weather data from NOAA. It works perfectly fine on its own, but when I add the +0*NOW() command it returns the #VALUE error. Am I not using this NOW function correctly? Is there another means of WEBSERVICE updating automatically without having to use ctrl+alt+F9? Thank you!
- What does your formula look like? 0*Now() returns the number zero, you cannot add a zero to text, that yields the #VALUE! error. Perhaps if you add this behind your WEBSERVICE formula it does work:
&IF(0*NOW(),"","")
4 Replies
I understand that you're facing an issue with the WEBSERVICE function in Excel when using the +0*NOW() combination to refresh weather data from NOAA. While this specific approach might not be working as expected, there are alternative solutions to achieve automatic updates without manually pressing Ctrl+Alt+F9:
Understanding the +0*NOW() Issue:
- Formula purpose: The +0*NOW() trick is often used to force recalculation of a formula. However, it might not work reliably with WEBSERVICE due to various factors like server-side caching or limitations in how Excel handles external data connections.
Alternative Approaches:
Timer-based Macro:
- Create a macro that uses the WEBSERVICE function to fetch your data.
- Use the Application.OnTime method to schedule the macro to run at specific intervals (e.g., every 10 minutes).
- This approach offers more control over the update schedule and reduces reliance on manual intervention.
Power Query Refresh:
- If you're comfortable with Power Query, create a query that imports your weather data using WEBSERVICE.
- Schedule the query to refresh automatically at periodic intervals.
- This method provides a powerful and user-friendly interface for data acquisition and refresh.
External Service/API:
- Explore weather data providers that offer APIs with built-in automatic refresh mechanisms.
- Integrate these APIs into your Excel spreadsheet using methods like Power Query or VBA.
- This approach might involve additional setup but can offer more robust and feature-rich data access.
Additional Tips:
- When choosing a solution, consider your technical expertise, desired update frequency, and limitations of specific methods.
- Test your chosen approach thoroughly with a small dataset before implementing it on your entire spreadsheet.
- Ensure your NOAA data source supports frequent access and doesn't have rate limits that might affect your automation.
I hope these alternatives provide a clear path to automatically refresh your weather data in Excel without relying on the unreliable +0*NOW() method.
- Thank you for the advice. I will work my way through all of them and see which one serves me the best.
- What does your formula look like? 0*Now() returns the number zero, you cannot add a zero to text, that yields the #VALUE! error. Perhaps if you add this behind your WEBSERVICE formula it does work:
&IF(0*NOW(),"","")- Thank you, your suggestion seems to be working perfectly so far.
