excel中使用参数与网络查询

使用参数与网络查询
Today's author is Jan Karel Pieterse, an Excel MVP.今天的作者是1月卡雷尔科Pieterse ，一个Excel的MVP 。 You can find more useful tips from Jan Karel on his website: http://www.jkp-ads.com/ This post shows you how to dynamically work with data from a website in Excel.您可以找到更多有用的提示从1月卡雷尔科在自己的网站上： http://www.jkp-ads.com/这个职位将告诉您如何动态与数据从网站在Excel中。

Introduction导言
Excel provides a very useful option to gather data from websites, called web queries. Excel中提供了一个非常有用的选择，收集数据的网站，所谓的Web查询。 These have been introduced with Excel 97 and have been further enhanced in the versions released after Excel 97.这些都是采用Excel 97和得到了进一步加强在版本发布后， Excel 97中。 This article shows how you can setup a web query so that you can make the worksheet dynamically update based on values in so called parameter cells.本文将告诉你如何设置一个Web查询，以便您可以动态更新的工作为基础的价值观所谓参数细胞。

Setting up the web query 建立网上查询
Setting up a web query is as simple as clicking the Data tab and then clicking the “From Web button on the Get External Data tab.建立一个Web查询很简单，只要点击数据标签，然后点击“从网页上的按钮获取外部数据选项卡。



You'll get this screen:您将获得此画面：



Enter http://moneycentral.msn.com/companyreport?symbol=MSFT and click the Go Button.输入http://moneycentral.msn.com/companyreport?symbol=MSFT并点击转到按钮。

Don't do anything yet, first click the “Options” button and set the webquery to return full html formatting results (if so desired):不要做任何事，但第一次点击“选项”按钮，并设置充分webquery返回HTML格式的结果（如果有此愿望的） ：



After changing these settings, select the part you want to be imported into your sheet by clicking the appropriate black-on-yellow arrow:改变这些设置后，选择部分要被导入您的工作表，然后通过点击相应黑黄箭头：



Now we're ready to click “Import”.现在我们准备单击“导入” 。 If the page takes time to load, you'll see a progress screen:如果网页需要时间的负荷，您会看到一个屏幕上的进展：



Next Excel asks where to put the results; put them in cell A3 so we have room above the table:下一步Excel的要求下把结果;使他们在单元格A3 ，所以我们必须室以上的表：



If you want an interactive result in your sheet, enabling you to enter new search criteria in a cell, you have to go through a little VBA.如果你想要一个互动的结果在你的资产负债表，让您输入新的搜索标准单元格中，你必须经过一个小的VBA 。 Don't worry, this is easy.不要担心，这是容易的。

These steps are needed:这些步骤是必要的：

Click any cell in the new table.单击任一单元格中的新表。
Open the Visual Basic Editor by hitting the Alt+F11 key.打开Visual Basic编辑器所触及的Alt + F11键。
Hit control+g to open the immediate window.击中或Control + G打开立即窗口。
Now type the following line:现在，键入以下行：

activecell.QueryTable.Connection="URL;http://moneycentral.msn.com/companyreport?symbol=[""Ticker""]" activecell.QueryTable.Connection = “网址; http://moneycentral.msn.com/companyreport?symbol = [ ” “星空” ， “ ] ”

Click Enter to confirm.按ENTER以确认。
Close the Visual Basic Editor.关闭Visual Basic编辑器。
You have now made part of the url work as a parameter, by replacing that part of the url with some text between quotes and square brackets (we used a bit of VBA because with the MSN site you can't do this through the user interface).您现在已经取得了部分的URL作为一个参数，以取代部分的URL与报价之间的一些文字和方括号（我们使用了一些VBA的，因为与台湾网站你不能做到这一点通过用户界面） 。 The string you entered will be used as both the name of the parameter and the prompt.您输入的字符串将作为双方的名称和参数的提示。

Click the refresh all button on the data tab of the ribbon, in the connections group.单击刷新按钮，所有的数据选项卡上的缎带，在连接组。 Excel will interpret the part between the square brackets as a parameter and prompt you for a value: Excel将解释部分的方括号之间的参数，并提示您的价值：



Excel detects we have a parameter and now asks what value you want. Excel的检测，我们有一个参数，现在问你想要什么价值。