VBA高级应用30例 应用6 第5部分：5   利用VBA从XML文件中导出数值数据

《VBA高级应用30例》版权10178985是我推出的第十套教程教程是专门针对高级学员在学习VBA过程中提高路途上的案例展开这套教程案例与理论结合紧贴“实战”并做“战术总结”以便大家能很好的应用。教程的目的是要求大家在实际工作中分发VBA程序写好的程序可以升级。本套教程共三册三十个专题本讲的内容是VBA高级应用30例应用6 第5部分5 利用VBA从XML文件中导出数值数据【分享成果随喜正能量】我们能在生时多做善事则临死何惧我们能在平日贡献社会则虽死犹生。每一个在你的生命里出现的人都有原因喜欢你的人给了你温暖和勇气你喜欢的人让你学会了爱和自持你不喜欢的人教会你宽容与尊重不喜欢你的人让你自省与成长。应用6 第5部分5 利用VBA从XML文件中导出数值数据这部分开始我们深入讲解这个应用如何利用VBA操作XML文件我给大家介绍的有三部分内容一是如何导出数值数据二是如何修改工具栏三是如何导出工作表中的图片。可以说这部分内容值得深入研究的还很多我给大家展示的只是很少的一部分而已希望有兴趣的朋友能深入地研究。我们先来研究如何导出数值数据。如之前讲解的手工修正XML文件的方法利用VBA处理XML文件也同样需要以下几个步骤a解压缩.xlsx或.xlsm文件b) 提取适当的XML文件c将XML文件写回文件夹结构d把文件还原这里我将利用一个类模块来完成主要的工作同时为了处理XML文件我们还要做如下的引用本专题提供的实例应用程序如下本专题利用到的文件高级应用06文件夹-2高级应用06-2.xlsm示例1.xlsm示例1和示例2的备份(文件夹用于原始文件的恢复)目的在上述的“示例1.xlsm”文件中有名称为“mySheet”的工作表这个工作表中有很多的数据我们要把这些数据导出到程序文件“高级应用06-2.xlsm”的“sheet1”工作表中。一般情况下这种操作是复制粘贴如果应用VBA对工作表的操作方法是多种多样的在我之前的教程中有讲解本专题的内容是利用VBA对XML文件进行操作获得导出的数据这里我们只给大家数值数据的导出代码操作的是工作表的xml文档对于其他的操作没有展开大家可以自己研究利用styles.xml文档。为了实现我们的目的我们按下面的步骤1) 解压.xlsx或.xlsm文件类模块Public Sub UnzipFile()Dim FSO As ObjectDim oShellApp As ObjectSet FSO CreateObject(scripting.filesystemobject)从源文件的位置派生要解压缩到的文件夹,FolderName(类属性)XMLFolderRoot FolderName在与sourcefile相同的文件夹中创建一个专用解压缩文件夹On Error Resume Next删除所有以前存在的文件夹FSO.deletefolder XMLFolderRoot *, TrueKill XMLFolderRoot *.*On Error GoTo 0创建普通文件夹If FolderExists(XMLFolderRoot) False ThenMkDir XMLFolderRootEnd IfXLFolder XMLFolderRoot xl\Set oShellApp CreateObject(Shell.Application)将文件复制到新创建的文件夹中oShellApp.Namespace(XMLFolderRoot).CopyHere oShellApp.Namespace(SourceFile).itemsOn Error Resume Next清理临时文件夹FSO.deletefolder Environ(Temp) \Temporary Directory*, TrueSet oShellApp NothingSet FSO NothingExit SubEnd Sub代码截图2获取属于特定工作表的xml文件信息类模块使用OpenXML包时最基本的操作之一是操作文件中工作表的XML文档。当然我们希望根据一定的信息从包中提取正确的XML工作表的名称。下面显示的代码提取适当的rId关系标识符属性值该属性值列在该文件的“工作表”节点中的文件workbook.xml中。这个操作我将利用两段代码来完成首先在workbook.xml文件中获得指定工作表的r:id值例如本专题给的例子是从工作表“mySheet”中导出数据那么首先要获得该工作表的rid为rid3然后找出哪个xml文件属于该Id,程序中获得的文件名称是worksheets/sheet3.xml.(当然第二个步骤也可以使用另一种方法获取属于特定rid的xml文件,这个方法在我提供的程序文件中有Function GetSheetNameFromId)。我们看下面的两段代码Private Function GetSheetIdFromSheetName(sSheetName) As String得到ridDim oXMLDoc As MSXML2.DOMDocumentDim oXMLNode As MSXML2.IXMLDOMNodeDim oXMLNodeList As MSXML2.IXMLDOMNodeListIf mvXLFolder And Sheet2Change ThenSet oXMLDoc New MSXML2.DOMDocumentoXMLDoc.Load XLFolder workbook.xmlSet oXMLNodeList oXMLDoc.SelectNodes(/workbook/sheets/sheet)For Each oXMLNode In oXMLNodeListIf oXMLNode.Attributes.getNamedItem(name).NodeValue sSheetName ThenGetSheetIdFromSheetName oXMLNode.Attributes.getNamedItem(r:id).NodeValueExit FunctionEnd IfNextEnd IfEnd FunctionEnd IfEnd Function代码的截图代码的讲解在本例子中上述第一段代码得到的返回值是rid3第二段代码得到的返回值是worksheets/sheet3.xml。3提取XML文件标准模块下面的过程从解压缩的文件夹中提取xml文件Public Function GetXMLFromFile(sFileName As String) As StringDim oXMLDoc As MSXML2.DOMDocumentIf Len(XLFolder) 0 ThenGetXMLFromFile ElseSet oXMLDoc New MSXML2.DOMDocumentoXMLDoc.Load XLFolder sFileNameGetXMLFromFile oXMLDoc.XMLSet oXMLDoc NothingEnd IfEnd Function代码的截图4将XML文件中的信息导出到指定文件中类模块下面的函数是将XML文件中的信息导出到程序文件中的Sheet1工作表中Function WriteXML2Sheet(sXML As String)填入处理的工作表是sheet1Dim oNode As MSXML2.IXMLDOMNode代码的截图5文件的还原类模块下面的代码将压缩文件还原成原始压缩文件。Public Sub ZipAllFilesInFolder()Dim oShellApp As ObjectDim sDate As StringDim sDefPath As StringDim vFileNameZip As VariantDim FSO As ObjectDim lFileCt As Long代码的部分截图我20多年的VBA实践经验全部浓缩在下面的各个教程中