excel vba 网页数据抓取(微软推出VBA处理Excel数据既高效也比较简单，适合编程小白上手)

　　excel vba 网页数据抓取(微软推出VBA处理Excel数据既高效也比较简单，适合编程小白上手)

　　前言：

　　Excel 基本上是用来处理表单数据的。它带有一些工具和功能。熟练地使用工具和函数可以使 Excel 更加高效。然而，有时面对一些繁琐的数据却无能为力，于是微软推出了VBA开发，VBA处理Excel数据既高效又相对简单，适合编程新手。

　　VBA 是基于 Visual Basic 开发的，它们具有相似的语言结构。 VB 旨在创建标准应用程序，而 VBA 是自动化现有应用程序（EXCEL 等）； VB有自己的开发环境；而VBA必须寄生在现有的办公应用上，是开发模块之一。

　　效果图：

　　

　　屏幕1.png

　　

　　屏幕2.png

　　

　　屏幕3.png

　　上图中的功能不代表VBA开发功能。它仅用于练习。作用是输入关键词后，在Excel表格中显示关键词的描述列表。

　　VBA在Excel中的应用

　　VBA可以实现Excel本身无法实现的更多非常强大的自动化功能，比如应用公式，从自动计算和制表到办公平台，如：

　　1.自动将数据录入客户系统

　　2.系统网页数据抓取

　　3.自动任务分配

　　4.状态跟踪管理

　　5.自动批量发送邮件

　　6.自动生成报告

　　7.问卷调查（使用 Outlook）

　　8.项目管理（带Access数据库）

　　9.能力测试系统

　　10.知识库

　　11.询价管理系统

　　12.员工报销管理系统

　　代码分析：

　　Dim listDataArr As Collection 'excel显示所需的数组数据

Sub testVBA()

Dim URLStr As String 'API

Dim originalStr As String '网络请求后的原始字符串数据

Dim dataDic As Dictionary '字符串转化为的json，即dic

keyword = Application.InputBox("请输入需查询的关键字：")

'随意使用的一个免费API用于下面的练习

URLStr = "http://baike.baidu.com/api/openapi/BaikeLemmaCardApi?scope=103&format=json&appid=379020&bk_length=600&bk_key=" & keyword

'执行get请求

originalStr = LXHelpModel.XMLHttpGET(URLStr)

If Len(originalStr) = 2 Then

MsgBox ("请输入有效的关键字，如：vba、互联网、app等")

Exit Sub

End If

'json解析，将服务器返回的字符串数据转化为json（Dictionary），这里使用的是www.json提供的官方解析方法（使用别的方法解析时含有数组的数据将得到JScriptTypeInfo类型的数据，不可使用）

Set dataDic = JSON.parse(originalStr)

Set listDataArr = dataDic("card")

updateExcelData

End Sub

'更新excel数据

Function updateExcelData()

'清空旧数据

ActiveSheet.Cells.ClearContents

'ActiveSheet.Cells.Interior.ColorIndex = 0

'将数据更新到excel指定的cell中

Dim item As Dictionary

For i = 1 To listDataArr.Count

Set item = listDataArr.item(i)

ActiveSheet.Cells(3 + i, 1) = item("name")

ActiveSheet.Cells(3 + i, 3) = item("format")(1)

ActiveSheet.Cells(3 + i, 1).Font.Color = RGB(0, 200, 50)

'ActiveSheet.Cells(3 + i, 1).Interior.Color = RGB(0, 50, 200)

Next

End Function

　　这个效果是在LXTestModel模块中实现的。 testVBA 进程被指定为按钮宏。可以点击宏按钮选择testVBA并运行，也可以直接点击查询按钮运行testVBA方法。

　　代码分析在注释里已经写的很清楚了，主要是通过ActiveSheet.Cells(3 + i, 1) = item("name")给表格的指定位置赋值。

　　遇到的坑：

　　解析json时，如果字段收录数组，则不能使用该字段，类行为JScriptTypeInfo。

　　出现这个结果的原因是创建的ScriptControl对象被指定为JS语言进行解析。需要使用JSON网站提供的JSON库分析，下载地址，找到VB-JSON下载，然后将cJSONScript.cls、cStringBuilder.cls、JSON.bas三个文件导入到工程中，然后像这样使用 Set dataDic = JSON.parse(originalStr) 。传入一个 json 字符串将返回一个 Dictionary 类型的字典。这种类型对于 iOS 开发者来说很熟悉，在 Java 中称为 map。

　　导入VBJson的三个文件后，提示错误：“User-defined type is not defined”，代码位于方法RStoJSON中

　　您需要将类型 ADODB.Recordset 更改为 Object 才能通过编译。

　　使用Dictionary后报错：“User-defined type is not defined”

　　出现这个结果的原因是VBA本身不支持这种数据类型。您需要引用该库。依次点击：工具->参考->勾选“Microsoft Scripting Runtime”->确定。不推荐使用Set dic=CreateObject("scripting.dictionary")这种方式创建

　　使用Utf8ToUnicode方法解码乱码时，提示错误：Runtime error ‘424’: Object required

　　在该文件的第一行添加以下代码

　　Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long

Private Const CP_UTF8 = 65001

Option Explicit

　　请求网络时使用Inet方法，错误码提示位于Inet1，提示error: variable is not defined

　　Inet 是用于网络请求的控件。您需要将此控件添加到 Execl。操作如下：开发工具->最后一个插入控件（图片右侧）->在下拉列表中找到“Microsoft Internet Transfer Control”并点击->点击插入任意表格位置。如果有下拉列表中没有“Microsoft Internet Transfer Control”，操作前需要安装MSINET.OCX。

　　运行宏无效

　　可能是运行后出错，修改代码后没有点击reset。

　　VBA开发刚研究两天，我还是菜鸟，望指正。

　　请点击github地址下载源码。