Web Integration

<div dir="ltr" style="text-align: left;" trbidi="on"> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> A Web Query is when you send a request to a web page and ask for some data to be returned. You'll see how to do that in this section, by importing data into your spreadsheet from a web page on our web site.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> There are many reasons why you would want to do that. If, for example, you're a hard-working sales person out in the field, and a customer wants the latest prices, you could run a web query in Excel and pull the prices from your employer's website.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> How to run a Web Query in Excel 2007 to 2013</h3> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> You'll now learn how to use Web Queries in Excel. For this lesson, you'll need an active internet connection. We're going to connect to a web page, and download a product list straight into a spreadsheet. Off we go!</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Open Excel</li> <li>Connect to the internet, if you're not already online</li> <li>Click inside A1 on your new worksheet</li> <li>From the Excel Ribbon, click on <b>Data</b></li> <li>From the Data tab, locate the <b>Get External Data</b> panel:</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The Get External Data panel in Excel 2007" border="1" height="84" src="http://www.homeandlearn.co.uk/excel2007/images/2webQueryExtDatPanel.gif" width="277" /></div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="External Data, Excel 2013" height="92" src="http://www.homeandlearn.co.uk/excel2007/images/2013/section3/external_data.gif" width="278" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> From the Get External Data panel, click on <b>From Web</b>. You'll then see the following dialogue box appear:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The New Web Query dialogue box" height="412" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery.gif" width="584" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The idea is that you type the address of a web page and then click Go. Excel will then fetch the data for you.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So, in the Address box, where it says about:blank in the image, type the following address:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>http://www.homeandlearn.co.uk/ME/webquery1.htm</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Before you click Go, click the Options button in the top right of the New Web Query dialogue box. You'll see this dialogue box appear:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Web Query Options" height="296" src="http://www.homeandlearn.co.uk/excel2007/images/2webQueryOptionsDB.gif" width="290" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> For this first web query, we're not going to change any of these settings. But the Formatting section is the one you'll use most. You can import the web page with all its current formatting, use just Rich Text formatting, or have no formatting at all. (Rich Text formatting will get you things like bold text, but won't give you any of the fancy stuff on the page.)</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Click OK on the Options dialogue box to return to the New Web Query. Now click the Go button.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> When you click the Go button, Excel will try to connect to the address you gave it. If it can't get through, you'll see a "Page Cannot be Found" error page:</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> If that's what you're getting, make sure you are connected to the internet. Check if you've typed the address correctly. Make sure that your firewall is not blocking Excel.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> If Excel is successful, you'll see the data appear in the Web Query window:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The Web Query Window" height="467" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery4.gif" width="620" /><br /> <div align="left"> Note the black arrows in the yellow squares. You can select the tables you want to import. Click the first yellow box, and it will turn green and have a tick in it. Like this one:</div> <div align="center"> <img border="1" height="157" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery5.gif" width="525" /></div> </div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Once you have the data selected, click the Import button at the bottom of the New Web Query window. You'll get yet another dialogue box:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Import Data" height="245" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery2.gif" width="290" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> There's not much to do, here. But if you want to import the data to a different starting cell, or even a new worksheet, select the appropriate option. For this particular import, Excel is only giving us the option to view the data as a Table. Click OK and the import will begin. You should see this in cell A1 on your spreadsheet:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Excel is fetching the data" border="1" height="125" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery3.gif" width="322" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> If the import is successful, your spreadsheet should look like ours below:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The web page has been imported into the Excel 2007 spreadsheet" height="241" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery6.gif" width="620" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> As you can see, the data from our web page has been imported into Excel! Let's try another one.</div> <h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Web Query Two</h3> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The next web query we'll do will see an import of full HTML formatting. When you're finished, you'll see why this can be a problem.</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>At the bottom of Excel, click on Sheet1</li> <li>On the fresh worksheet, click inside cell A1</li> <li>Click on the Data menu, then on click From Web on the Get External Data panel</li> <li>In the New Web Query Address box, type the following Address (don't click the Go button just yet):</li> </ul> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Click the Options button in the top right of the dialogue box:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img height="296" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery7.gif" width="290" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> This time, select Full HTML Formatting, as in the image above. Click OK, then click the Go button.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Excel will bring back your data. Click the yellow box with the arrow in it to select all the data:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img height="475" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery8.gif" width="630" /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Click the <b>Import</b> button at the bottom when your dialogue box looks like the one above.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> When you see the Import Data dialogue box, just click OK. The data will then be imported into Excel:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Some of the HTML formatting has not imported successfully" height="235" src="http://www.homeandlearn.co.uk/excel2007/images/2webQuery9.gif" width="445" /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The problem with importing full HTML is that some of that fancy formatting you did won't convert very well in Excel. In the image above, our Latest Prices heading has been mangled!</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In other words, you may have to spend time re-formatting your spreadsheet.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> To get the full heading back, for example, highlight the first row, from A1 to G1. Click on the Home menu, and then locate the Alignment panel. Click Merge and Centre.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> But that's it for Web Queries. They are quite simple to do, and can come in handy if you're out on the road. In the next part, we'll take a look at Hyperlinks in Excel.</div> </div>

Web Integration

A Web Query is when you send a request to a web page and ask for some data to be returned. You'll see how to do that in this section, ...

Read more »