How to use the WEBSERVICE function
The WEBSERVICE function gets data from a web service on the Internet or Intranet based on a given URL.
What is a URL?
A URL stands for Uniform Resource Locator which is used to navigate to a specific website or asset on the Internet. A DNS server which stands for Domain Name System translates a URL to a numeric IP address that servers use to communicate.
URL - wikipedia
What is a web service?
A web service is a software system that provides a functionality or a resource over the internet by other applications (like Excel) or systems.
Web service - Wikipedia
Many websites uses RSS which stands for (Really Simple Syndication that lets you read for example news without having to go to the news web site. RSS - wikipedia
A RSS reader lets you subscribe to RSS feeds, however, you can use Excel to import RSS feeds. A RSS feed consists of XML data.
What is XML?
XML is an abbreviation for eXtensible Markup Language, it is a text format for storing and transporting data.
Introduction to XML - w3schools
Excel has a function named FILTERXML that lets you extract data from a RSS feed containing XML data.
Table of Contents
The function returns a #VALUE error if the returned value is above the cell limit of 32767 characters.
Formula in cell B2:
1. WEBSERVICE function Syntax
The WEBSERVICE function has only one argument named url.
WEBSERVICE(url)
2. WEBSERVICE function arguments
| url | Required. An URL (Uniform Resource allocator) to a webpage on the internet or the intranet. |
3. WEBSERVICE function example
The WEBSERVICE function gets data from a web service on the Internet specified in the url argument.
Formula in cell B2:
The basic example above demonstrates how to get the contents of a webpage located at www.google.com (url) with the WEBSERVICE function. A web server is a web service that distributes html pages to web browsers on the Internet. You can use Excel to get the html code to a worksheet with the WEBSERVICE function.
4. WEBSERVICE function not working
HTTP:// and HTTPS:// are supported.
An URL larger than 2048 returns #VALUE error.
Protocols like ftp:// and file:// are not supported, the WEBSERVICE function returns #VALUE error.
5. WEBSERVICE function and ENCODEURL function
The WEBSERVICE function requires a valid url to work properly, the ENCODEURL function lets you replace certain characters to a percentage and a hexadecimal code.
You can then use the url in the WEBSERVICE function to get the data you want.
Formula in cell C3:
The file name Budget 2025.xlsx contains a character that needs to be replaced with the corresponding hexadecimal code in order for the url to function properly.
Explaining formula in cell C3
Step 1 - Create a text string in the formula
The double quotes lets you use strings in a Excel formula, the string must start and end with a double quote.
"https://www.example.com/"
Step 2 - Encode url
The ENCODEURL function returns a URL-encoded string.
Function syntax: ENCODEURL(text)
ENCODEURL(C2)
becomes
ENCODEURL("Budget 2025.xlsx")
and returns
"Budget%202025.xlsx"
Step 3 - Concatenate website and file name
The ampersand lets you concatenate text strings in a Excel formula.
"https://www.example.com/"&ENCODEURL(C2)
becomes
"https://www.example.com/"&"Budget%202025.xlsx"
and returns
"https://www.example.com/Budget%202025.xlsx"
Useful resources
Functions in 'Web' category
The WEBSERVICE function function is one of 4 functions in the 'Web' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form