Excel WEBSERVICE not working with +0*NOW()

[GranSupt]

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!

[#2 · Accepted solution]

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(),"","")

[#3]

@GranSupt 

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:

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

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

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

[#4]

Thank you, your suggestion seems to be working perfectly so far.

[#5]

Thank you for the advice. I will work my way through all of them and see which one serves me the best.