Forum Discussion
Sachin Jain
Sep 23, 2023Brass Contributor
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.
- JerryDNYCCopper 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!
- JerryDNYCCopper 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 JainBrass ContributorWith 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. - SergCioCopper Contributor
Did the new version (2311) solve your problem? I continue to have the same issue. Sachin Jain
- Sachin JainBrass Contributoryes, this issue is resolved now in todays update Build 17005
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().
- JerryDNYCCopper 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 JainBrass ContributorIssue Resolved now in Build 17005
- Sachin JainBrass ContributorYes, 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.
- Sachin JainBrass ContributorIssue resolved 🙂