构建宏将数据从网站下载到Excel中 [英] Building a Macro to Download Data off a Website into Excel

问题描述

我想使用VBA在Excel中构建一个宏来从以下网站下载历史数据：

http://quickstats.nass.usda.gov/results/320F1D82-1064-30F1-809E-F77E509EC508

我照常创建一个命令按钮。然后我认为这可能能够使用宏记录器轻松解决。然而，当我选择数据 - >获取外部数据 - >从Web，然后键入上述地址，我无法选择整个表作为数据。

所以，我不确定如何做这个任务，否则，但我觉得可能有一些标准的代码，我已经找不到，为了完成这个任务。

我觉得这个任务对许多人来说是有用的，因为脚本可以在网站上下载数据，可以在各种场合使用。

解决方案>

有很多方法可以使用VBA从网站获取数据。您可以使用InternetExplorer对象导航到它，并在您到达之后解析HTML。您也可以使用MSXML2.XMLHTTP来制作HTTP请求。特别是Excel有一些可以做到这一点的数据链接选项。

然而，这里我将使用UDSA曾经提供过的工具对于你，并在一行中执行：

  Workbooks.Open（http://quickstats.nass.usda.gov /data/spreadsheet/4C43034A-0EAA-3171-B4FC-84CC95FC6E0C.csv）
  

编辑：对Sandstone下面的问题的回复，这里有一些代码将数据从该工作簿复制到现有的。

  Dim thisWb，downloadWb As Workbook 
设置thisWb = ActiveWorkbook 
 
设置downloadWb = Workbooks.Open（http://quickstats.nass.usda.gov/data/spreadsheet/4C43034A-0EAA-3171-B4FC-84CC95FC6E0C。 csv）
 
 downloadWb.Worksheets（1）.Range（A1：U2613）。复制目的地：= thisWb.Worksheets（1）.Range（A2）
 
 downloadWb.Close 
  

当然，您需要更改范围等等您需要的数据。

I would like to build a macro in Excel using VBA to download historical data off the following website:

http://quickstats.nass.usda.gov/results/320F1D82-1064-30F1-809E-F77E509EC508

I first create a command button as usual. I then thought this may be able to be easily solved using a macro recorder. However when I select Data -> Get External Data -> From Web and then type in the above address I am unable to select the table as a whole as the data.

So, I am unsure how to do this task otherwise but I feel there may be some standard code somewhere, which I have been unable to find, in order to complete the task.

I feel that this task is useful to many people, as scripts to download data off websites can be used in a variety of places.

解决方案

There are lots of ways of getting data from a website using VBA. You can navigate to it using an InternetExplorer object and parse the HTML once you're there. You can also craft HTTP requests using MSXML2.XMLHTTP. Excel in particular has a number of data-link options that can do this.

However, here I'd use the tools that the UDSA have ever-so-kindly provided for you, and do it in one line:

Workbooks.Open ("http://quickstats.nass.usda.gov/data/spreadsheet/4C43034A-0EAA-3171-B4FC-84CC95FC6E0C.csv")

EDIT: In response to Sandstone's question below, here's some code to copy data from that workbook into your existing one.

Dim thisWb, downloadWb As Workbook
Set thisWb = ActiveWorkbook

Set downloadWb = Workbooks.Open("http://quickstats.nass.usda.gov/data/spreadsheet/4C43034A-0EAA-3171-B4FC-84CC95FC6E0C.csv")

downloadWb.Worksheets(1).Range("A1:U2613").Copy Destination:=thisWb.Worksheets(1).Range("A2")

downloadWb.Close

Of course, you'll want to change the ranges and so on to match the data you need.

这篇关于构建宏将数据从网站下载到Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！