11-Excel VBA 学习总结 - 网络、XML、WMI以及API扩充.docx

Excel VBA 学习总结 - 网络、XML、WMI以及API扩充前面我已经总结了VBA最常见的一些应用；相对来说，下面这几种应用都比较少见一点，这里就是做一个索引，当需要的时候可以直接查询需要的资源。经过前面的了解，大家肯定也猜到了，这些应用基本上都是通过调用COM实现的。所以只要系统底层COM能完成的功能，基本在VBA中都可以去完成。此外，除了调用COM对象完成相应的功能，扩充VBA的能力最纯粹的手段就是直接调用API了。这个已经超出了VBA的范围，这里就是简单提及一下。对于COM对象创建的时候是“前期绑定”还是“后期绑定”的区别也就不再重复了。每个对象，我就是简单总结一下常见的用法。一、网络应用直接使用Workbook对象获取网络内容在装有Frontpage Server Extension的服务器上，可以直接以Workbook的形式保存和打开Excel。除了分享这些Excel文档，直接使用Workbook也可以直接打开和使用网页上的内容。打开以后，基本上就当Sheet一样处理了。DimoBkAsWorkbook打开远程ExcelSetoBk=Workbooks.Open(http:/www.MyS/book1.xlsx)另存为oBk.SaveAshttp:/www.MyS/Book2.xlsxDimoRngAsRange打开网页SetoBk=Workbooks.Open(/d/USD/table.html)查找内容SetoRng=oBk.Worksheets(1).Cells.Find(BritishPound)显示内容MsgBoxoRng.Offset(0,1).Value使用Web Query获取网络数据从Excel97加入了Web Query的支持后，几乎在每个新的版本中，这个方面的内容都得到了强化。我们可以使用这个特性获取网络上一张表的数据。主要使用的是Application.QueryTables。例子如下：SubGetRatesWithWebQuery()DimoBkAsWorkbookDimoQTAsQueryTable数字的格式设置DimsDecimalAsStringDimsThousandAsStringDimbUseSystemAsBooleanSetoBk=Workbooks.AddWithoBk.Worksheets(1)SetoQT=.QueryTables.Add(_Connection:=URL;/d/USD/table.html,_Destination:=.Range(A1)EndWith设置QueryTable的相关属性WithoQT.Name=USD选中特定的表.WebSelectionType=xlSpecifiedTables导入Page上第14个表.WebTables=14忽略Page上的格式.WebFormatting=xlWebFormattingNone不会尝试去识别日期.WebDisableDateRecognition=True每次打开文件的时候不会刷新数据.RefreshOnFileOpen=False等待查询结束.BackgroundQuery=True伴随Workbook一起保存数据.SaveData=True根据表中数据调整列宽度.AdjustColumnWidth=TrueEndWithWithApplication保存当前文档的分隔符格式sDecimal=.DecimalSeparatorsThousand=.ThousandsSeparatorbUseSystem=.UseSystemSeparators设置分隔符.DecimalSeparator=.ThousandsSeparator=,.UseSystemSeparators=True忽略任何错误OnErrorResumeNext执行查询，等待结束oQT.RefreshBackgroundQuery:=False恢复文档的分隔符格式.DecimalSeparator=sDecimal.ThousandsSeparator=sThousand.UseSystemSeparators=bUseSystemEndWithEndSub这种方式十分依赖于页面中节点的顺序，需要注意。使用InternetExplorer对象操纵DOM模型使用前面的方法都有一定的局限性，如果我们关注的不是整个表，而只是页面的某些部分，这个时候使用InternetExplorer对象是最方便的。这个对象的Document属性代表了整个的页面，可以使用这Document的属性和方法获取页面的各个部分(就是常说的DOM操作)。使用这个对象处理页面内容是最强大，最直接的一种方式。InternetExplorer对象的使用示例：SubGetUSDtoGBPRateUsingIE()DimoIEAsInternetExplorerDimsPageAsStringDimiGBPAsLong,iDecAsLongDimiStartAsLong,iEndAsLongDimdRateAsDouble创建隐藏的IE实例，可以通过设置Visible属性来显示IESetoIE=NewInternetExplorer打开网页oIE.Navigate/d/USD/table.html等待加载完毕，也可以使用下面的方式等待:WhileobIE.Busy=TrueDoEventsWendDoUntiloIE.readyState=4DoEventsLoopDOM操作sPage=oIE.Document.body.InnerText查找目标iGBP=InStr(1,sPage,BritishPound)iDec=InStr(iGBP,sPage,.)iStart=InStrRev(sPage,iDec)+1iEnd=InStr(iDec,sPage,)dRate=Val(Mid$(sPage,iStart,iEnd-iStart)MsgBoxTheUSD/GBPexchangerateis&dRateEndSub这种方式其实也是IE自动化的一种手段：/excel-vba/ie-automation.shtmlIE文档对象资料：/forum.php?mod=viewthread&tid=263887网页分析资料：/thread-377077-1-1.html邮箱登陆问题：/thread-357475-1-1.html使用XmlHttp获取网页内容XmlHttp是一套可以在Javascript、VbScript、Jscript等脚本语言中通过http协议传送或从接收XML及其他数据的一套API。XmlHttp最大的用处是可以更新网页的部分内容而不需要刷新整个页面。来自MSDN的解释：XmlHttp提供客户端同http服务器通讯的协议。客户端可以通过XmlHttp对象(MSXML2.XMLHTTP.3.0)向http服务器发送请求并使用微软XML文档对象模型Microsoft XML Document Object Model (DOM)处理回应。现在的绝对多数浏览器都增加了对XmlHttp的支持，IE中使用ActiveXObject方式创建XmlHttp对象，其他浏览器如：Firefox、Opera等通过window.XMLHttpRequest来创建xmlhttp对象。使用这种方式访问网页内容比较快一点，但是兼容性不如使用IE对象的方式。VBA从网络下载文件是可以通过XmlHttp对象实现的，例子如下：Sethttp=CreateObject(Microsoft.XmlHttp)http.OpenGET,url,Truehttp.sendIfhttp.ReadyState4ThenDoEventsElseSetoStream=CreateObject(ADODB.Stream)oStream.Type=1oStream.OpenoStream.Writehttp.responsebodysFile=Replace(Mid(url,InStrRev(url,/)+1),?,-)oStream.SaveToFileC:&sFile,2本地保存文件名oStream.CloseEndif其它小例子：/thread-163323-1-1.html。XmlHttp对象参考：/tfgdd/article/details/5062767。VBA调用WebService这个在VBA中也可以实现的，一般需要类库辅助，其实与XML操作还是有关系的，可以参看下面链接：从Office调用Web Service：/excel-vba/ie-automation.shtml如何调用Web Service：/en-us/magazine/cc163837.aspx二、XML操作在VBA中处理XML文档在前面的文件系统中其实已经介绍过最简单的打开和保存了。下面综合总结一下：当做普通文本文档处理或者打开放到Workbook中处理使用这种方式，辅助正则表达式，再加上一大把处理字符串的方法，是可以完成任务的，只不过XML的结构优势一点都没用上。使用ADO处理XML文档这里充分体现了ADO的强大，配合SQL几乎可以完成任何的任务。别忘了添加MS ADO Library引用。生成XML的操作示例：SubCreate_XML_Recordset()ConststSQLAsString=SELECT*FROMReportDimstConAsStringstCon=Provider=Microsoft.Jet.OLEDB.4.0;&_DataSource=&ThisWorkbook.FullName&;&_ExtendedProperties=Excel8.0;HDR=Yes;DimrstAsNewADODB.RecordsetDimstrAsNewADODB.StreamWithrst.CursorLocation=adUseClient.OpenstSQL,stCon,adOpenStatic,adLockReadOnly,adCmdText.Savestr,adPersistXML.CloseWithstr.SaveToFileC:Report.xml,adSaveCreateOverWrite.CloseEndWithEndWithSetstr=NothingSetrst=NothingEndSub读取XML的操作示例：SubRead_XML_Data()DimrstAsADODB.RecordsetDimstConAsString,stFileAsStringDimiAsLong,jAsLongSetrst=NewADODB.RecordsetstFile=C:Report.xmlstCon=Provider=MSPersist;Withrst.CursorLocation=adUseClient.OpenstFile,stCon,adOpenStatic,adLockReadOnly,adCmdFileSet.ActiveConnection=NothingEndWithWithActiveSheetForj=0Toi-1.Cells(1,j+1).Value=rst.Fields(j).NameNextj.Range(A2).CopyFromRecordsetrstEndWithrst.CloseSetrst=NothingEndSub使用MSXML处理XML文档这应该是处理XML文档的首选策略了，它提供了XML DOM的所有方法和属性，使用起来很顺手。使用这个对象需要添加“Microsoft XML，v6.0”引用。SubReadXML()DimxmlDomAsMSXML2.DOMDocumentDimxmlPlaceMarkAsMSXML2.IXMLDOMNodeDimxmlPolygonAsMSXML2.IXMLDOMNodeDimxmlCoordAsMSXML2.IXMLDOMNodeDimsNameAsStringDimvaSpaceAsVariant,vaCommaAsVariantDimiAsLong,jAsLongSetxmlDom=NewMSXML2.DOMDocumentxmlDom.LoadC:Downloadsoverlay_1198.kmlFori=0ToxmlDom.childNodes(1).childNodes(0).childNodes.Length-1IfxmlDom.childNodes(1).childNodes(0).childNodes.Item(i).nodeName=PlacemarkThenSetxmlPlaceMark=xmlDom.childNodes(1).childNodes(0).childNodes.Item(i)SetxmlPolygon=xmlPlaceMark.childNodes(2).childNodes(0)SetxmlCoord=xmlPolygon.childNodes(0).childNodes(0).childNodes(0)sName=xmlPlaceMark.childNodes(1).childNodes(5).nodeTypedValueWithSheet4.Cells(Sheet2.Rows.Count,1).End(xlUp).Offset(1,0).Value=sNamevaSpace=Split(xmlCoord.childNodes(0).Text,)Forj=LBound(vaSpace)ToUBound(vaSpace)vaComma=Split(vaSpace(j),).Offset(0,1).Value=vaComma(0).Offset(0,2).Value=vaComma(1)NextjEndWithEndIfNextiEndSubXML DOM的方法和属性：/en-us/library/ms764730三、WMI信息WMI是内置在Windows 2000、 Windows XP和Windows Server 2003 系列操作系统中核心的管理支持技术。基于由 Distributed Management Task Force (DMTF) 所监督的业界标准，WMI是一种规范和基础结构，通过它可以访问、配置、管理和监视几乎所有的Windows资源。大多用户习惯于使用众多的图形化管理工具来管理Windows资源，在WMI之前这些工具都是通过Win32应用程序编程接口(Application ProgrammingInterfaces，API)来访问和管理Windows资源的。只要你熟悉系统编程你就知道API有多么重要。但是大多数脚本语言都不能直接调用Win32 API，WMI的出现使得系统管理员可以通过一种简便的方法即利用常见的脚本语言实现常用的系统管理任务。 利用WMI需要和脚本如WSH和VBScript结合起来，可以实现的功能大家可以看微软的MSDN文档。 (介绍比较全)VBA中使用WMI：/thread-733445-1-1.html。WMI中的Class介绍：/en-us/library/aa394554(v=VS.85).aspx四、API扩充在VBA中使用API，理论上可以完成任何操作系统提供的功能。但是实际上，除非万不得已，一般使用VBA或者其他COM提供的功能就足够了，真的算是价格便宜量又足。当上述这些功能不符合我们的需求的时候(特别是需要使用VBA中那纤弱的UserForm的时候)，那就使用API吧。使用API需要先声明，再使用；声明过程中还涉及到类型的转化。如果想了解更多的内容