Forum Discussion

Sachin Jain's avatar
Sachin Jain
Brass Contributor
Sep 23, 2023

Webservice function not working in Excel and generating #VALUE error after launch of python in excel

Ever since Python in Excel is launched, Webservice() function is not working in excel and generating #VALUE error.  Is there any update on this ? Today i got update in Office 365 but still not fixed. Has any one else also noticed, or there is some change in functionality of this function. 

  • JerryDNYC's avatar
    JerryDNYC
    Copper Contributor

    Sachin Jain I got fed up waiting for either a fix or a response from Microsoft, so decided to write a VBA function to use instead.

    Function WebSvc(sURL As String) As String
        Dim XMLHTTP As Object
    
        ' Create an XMLHTTP object
        Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
        ' Fetch the data
        With XMLHTTP
            .Open "GET", sURL, False
            .send
            WebSvc = .responseText
        End With
    
        ' Clean up
        Set XMLHTTP = Nothing
    End Function

    A quick FIND/REPLACE of WEBSERVICE with WebSvc fixed my problem.

    I'd much rather not have to use a Macro Function, but it works!

    I'm not that well versed in VBA, but be aware that I'm using 64 bit Excel. I don't know if it makes a difference here, but I am  aware that sometimes the code could be different for 32 bit Excel. Reply here if you find that problem - and the fix!

    • JerryDNYC's avatar
      JerryDNYC
      Copper Contributor

      It figures I got the update that fixes this problem right after posting this!

      The version is now:

      Version 2311 Build 16.0.17005.20000

  • Sachin Jain's avatar
    Sachin Jain
    Brass Contributor
    With the update Version 2311 Build 17005.20000. This problem is fixed. In a sheet if Webservice function needs to be run, now one has to enable the external data connection. Assuming soon this will come in Trust Center Settings to enable without security warning.

    The issue expressed here is closed now.
    • Sachin Jain's avatar
      Sachin Jain
      Brass Contributor
      yes, this issue is resolved now in todays update Build 17005
  • Sachin Jain 

    You are right, it doesn't work on Beta. Perhaps some news with Trust center and we shall allow the source additionally. Not sure, practically don't use WEBSERVICE().

    • JerryDNYC's avatar
      JerryDNYC
      Copper Contributor

      SergeiBaklan I track what beta version of Excel I have irregularly. My notes have the following information:

      • 09/01/2023: Version 2309 Build 16.0.16827.20000 64-bit - Includes Python
      • 09/09/2023: Version 2310 Build 16.0.16830.20000 64-bit
      • 09/21/2023: Version 2310 Build 16.0.16907.20000 64-bit - WEBSERVICE problem surfaces
      • 09/28/2023: Version 2310 Build 16.0.16924.20002 64-bit - WEBSERVICE problem NOT FIXED
      • 10/04/2023: Version 2311 Build 16.0.16926.20000 64-bit - WEBSERVICE problem NOT FIXED

      Maybe that would help?

    • Sachin Jain's avatar
      Sachin Jain
      Brass Contributor
      Yes, in new worksheet if we enter the URL it gives Microsoft Excel Security Notice , stating potential security concert , i have test all the settings with trust center as well , but no specific way to run the function is not available . even if we enable the content , it does not allow the connection.

Resources