编程

当前位置:永利皇宫463登录 > 编程 > 调用Excel宏批量处理文件

调用Excel宏批量处理文件

来源:http://www.makebuLuo.com 作者:永利皇宫463登录 时间:2019-09-22 05:19
'1.用户可以任意选择文件夹进行遍历'2.限定遍历时仅搜索EXCEL文件(你可以改变文件类型)'这个程序要先在“引用”下选择"microsoft scripting runtime"库文件Dim ArryFile() As StringDim nFile As IntegerSub Filelist()    Dim fso As New FileSystemObject    Dim fd As Folder    Dim strFilePath As String    Dim FolderSelect As FileDialog    Set FolderSelect = Application.FileDialog(msoFileDialogFolderPicker)    With FolderSelect        If .Show = -1 Then            strFilePath = .SelectedItems.Item & ""        End If    End With    Set fd = fso.GetFolder(strFilePath)    nFile = 0    searchFile fdEnd SubPrivate Function searchFile(ByVal fd As Folder)    Dim fl As File    Dim subfd As Folder    Dim i As Integer    On Error Resume Next        i = fd.files.Count             ReDim Preserve ArryFile(1 To nFile + i)    For Each fl In fd.files        If Right(fl.Name, 4) = "xlsx" Then       '后缀是xls的用   If Right(fl.Name, 3) = "xls" Then            nFile = nFile + 1            ArryFile = fl.Path        End If    Next    If fd.SubFolders.Count = 0 Then Exit Function    For Each subfd In fd.SubFolders        searchFile subfd    NextEnd Function//主函数,运行时调用该函数Sub ttt1()Dim xlname, myxl As Object, sh As ObjectCall Filelist'Set myxl = CreateObject("Aplication.Excel")    If nFile > 0 Then               For Each xlname In ArryFile()            If xlname <> "" Then //打开             Workbooks.Open Filename:=xlname             //调用Excel处理函数             Call Macro3             //保存,关闭             ActiveWorkbook.Save             ActiveWorkbook.Close            End If       Next    End IfSet myxl = NothingEnd Sub//Excel处理函数,该段替换成自己的处理过程Sub Macro3()'' Macro3 Macro'' 快捷键: Ctrl+Shift+C'    Range.Select    ActiveCell.FormulaR1C1 = "/"    With ActiveCell.Characters(Start:=1, Length:=1).Font        .Name = "宋体"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    Range.Select    ActiveCell.FormulaR1C1 = "R种植业  □林业  □畜牧业    □渔业    □其他 "    With ActiveCell.Characters(Start:=1, Length:=1).Font        .Name = "Wingdings 2"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=2, Length:=3).Font        .Name = "宋体"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=5, Length:=2).Font        .Name = "Wingdings 2"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=7, Length:=3).Font        .Name = "宋体"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=10, Length:=2).Font        .Name = "Wingdings 2"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=12, Length:=4).Font        .Name = "宋体"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=16, Length:=4).Font        .Name = "Wingdings 2"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=20, Length:=3).Font        .Name = "宋体"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=23, Length:=4).Font        .Name = "Wingdings 2"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=27, Length:=3).Font        .Name = "宋体"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    With ActiveCell.Characters(Start:=30, Length:=1).Font        .Name = "Wingdings 2"        .FontStyle = "常规"        .Size = 10        .Strikethrough = False        .Superscript = False        .Subscript = False        .OutlineFont = False        .Shadow = False        .Underline = xlUnderlineStyleNone        .ColorIndex = 1        .TintAndShade = 0        .ThemeFont = xlThemeFontNone    End With    Range.Select    Selection.Copy    Range.Select    ActiveSheet.Paste    Application.CutCopyMode = False    End Sub

前不久的一文山会中国人民解放军海军事工业程大学作是做网址的营运维护,因而做了大气的帮忙工具。有Excel中写VBA的,也可以有直接C#做的工具。不经常供给在C#中施行Excel VBA宏,乃至不经常还索要在实施了VBA宏之后,获取再次回到值再开展对应的管理。为了使用方便,笔者写了多少个实施Excel VBA宏的增派类 。放在博客里做个备份也冀望对有附近要求的相恋的人有所援救。
帮扶类仅提供了一个情势:RunExcelMacro **参数说明:         string         excelFilePath  Excel文件路线                 string         macroName    宏名称         object[]     parameters     宏参数组         out object  rtnValue         宏重回值         bool            isShowExcel   推行时是还是不是呈现Excel
**

  

增加补充表明:VBA宏需如下图写在模块中,技术被此措施鉴定识别。写在ThisWorkBook中不能够被辨认。

 

图片 1

实行Excel VBA宏协助类,注释比较详细,不再累赘代码进度。最焦点部分其实正是透过反射格局调用Excel VBA宏,oBook.Save()那句话也很首要,不然纵然实行了VBA宏调用,也不会保存Excel改动后的剧情

Java代码 

 图片 2)

  1.  1 using System;  
  2.   2 using System.Collections.Generic;  
  3.   3 using System.Text;  
  4.   4 using Excel = Microsoft.Office.Interop.Excel;  
  5.   5 using Microsoft.Office.Core;  
  6.   6 using System.IO;  
  7.   7   
  8.   8 namespace DoVBAMacro  
  9.   9 {  
  10.  10     /// <summary>  
  11.  11     /// 施行Excel VBA宏帮忙类  
  12.  12     /// </summary>  
  13.  13     public class ExcelMacroHelper  
  14.  14     {  
  15.  15         /// <summary>  
  16.  16         /// 执行Excel中的宏  
  17.  17         /// </summary>  
  18.  18         /// <param name="excelFilePath">Excel文件路线</param>  
  19.  19         /// <param name="macroName">宏名称</param>  
  20.  20         /// <param name="parameters">宏参数组</param>  
  21.  21         /// <param name="rtnValue">宏重返值</param>  
  22.  22         /// <param name="isShowExcel">试行时是否呈现Excel</param>  
  23.  23         public void RunExcelMacro(  
  24.  24                                             string excelFilePath,  
  25.  25                                             string macroName,  
  26.  26                                             object[] parameters,  
  27.  27                                             out object rtnValue,  
  28.  28                                             bool isShowExcel  
  29.  29                                         )  
  30.  30         {  
  31.  31             try  
  32.  32             {  
  33.  33                 #region 检查入参  
  34.  34   
  35.  35                 // 检查文件是不是存在  
  36.  36                 if (!File.Exists(excelFilePath))  
  37.  37                 {  
  38.  38                     throw new System.Exception(excelFilePath + " 文件不设有");  
  39.  39                 }  
  40.  40   
  41.  41                 // 检查是不是输入宏名称  
  42.  42                 if (string.IsNullOrEmpty(macroName))  
  43.  43                 {  
  44.  44                     throw new System.Exception("请输入宏的称呼");  
  45.  45                 }  
  46.  46   
  47.  47                 #endregion  
  48.  48   
  49.  49                 #region 调用宏管理  
  50.  50   
  51.  51                 // 盘算展开Excel文件时的缺省参数对象  
  52.  52                 object oMissing = System.Reflection.Missing.Value;  
  53.  53   
  54.  54                 // 依据参数组是不是为空,筹划参数组对象  
  55.  55                 object[] paraObjects;  
  56.  56   
  57.  57                 if (parameters == null)  
  58.  58                 {  
  59.  59                     paraObjects = new object[] { macroName };  
  60.  60                 }  
  61.  61                 else  
  62.  62                 {  
  63.  63                     // 宏参数首席施行官度  
  64.  64                     int paraLength = parameters.Length;  
  65.  65   
  66.  66                     paraObjects = new object[paraLength + 1];  
  67.  67   
  68.  68                     paraObjects[0] = macroName;  
  69.  69                     for (int i = 0; i < paraLength; i++)  
  70.  70                     {  
  71.  71                         paraObjects[i + 1] = parameters[i];  
  72.  72                     }  
  73.  73                 }  
  74.  74   
  75.  75                 // 创制Excel对象示例  
  76.  76                 Excel.ApplicationClass oExcel = new Excel.ApplicationClass();  
  77.  77   
  78.  78                 // 剖断是不是须求举行时Excel可知  
  79.  79                 if (isShowExcel)  
  80.  80                 {  
  81.  81                     // 使创办的指标可见  
  82.  82                     oExcel.Visible = true;  
  83.  83                 }  
  84.  84   
  85.  85                 // 创建Workbooks对象  
  86.  86                 Excel.Workbooks oBooks = oExcel.Workbooks;  
  87.  87   
  88.  88                 // 创建Workbook对象  
  89.  89                 Excel._Workbook oBook = null;  
  90.  90   
  91.  91                 // 展开内定的Excel文件  
  92.  92                 oBook = oBooks.Open(  
  93.  93                                         excelFilePath,  
  94.  94                                         oMissing,  
  95.  95                                         oMissing,  
  96.  96                                         oMissing,  
  97.  97                                         oMissing,  
  98.  98                                         oMissing,  
  99.  99                                         oMissing,  
  100. 100                                         oMissing,  
  101. 101                                         oMissing,  
  102. 102                                         oMissing,  
  103. 103                                         oMissing,  
  104. 104                                         oMissing,  
  105. 105                                         oMissing,  
  106. 106                                         oMissing,  
  107. 107                                         oMissing  
  108. 108                                    );  
  109. 109   
  110. 110                 // 执行Excel中的宏  
  111. 111                 rtnValue = this.RunMacro(oExcel, paraObjects);  
  112. 112   
  113. 113                 // 保存改变  
  114. 114                 oBook.Save();  
  115. 115   
  116. 116                 // 退出Workbook  
  117. 117                 oBook.Close(false, oMissing, oMissing);  
  118. 118   
  119. 119                 #endregion  
  120. 120   
  121. 121                 #region 释放对象  
  122. 122   
  123. 123                 // 释放Workbook对象  
  124. 124                 System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);  
  125. 125                 oBook = null;  
  126. 126   
  127. 127                 // 释放Workbooks对象  
  128. 128                 System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);  
  129. 129                 oBooks = null;  
  130. 130   
  131. 131                 // 关闭Excel  
  132. 132                 oExcel.Quit();  
  133. 133   
  134. 134                 // 释放Excel对象  
  135. 135                 System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);  
  136. 136                 oExcel = null;  
  137. 137   
  138. 138                 // 调用垃圾回收  
  139. 139                 GC.Collect();  
  140. 140   
  141. 141                 #endregion  
  142. 142             }  
  143. 143             catch (Exception ex)  
  144. 144             {  
  145. 145                 throw ex;  
  146. 146             }  
  147. 147         }  
  148. 148   
  149. 149         /// <summary>  
  150. 150         /// 执行宏  
  151. 151         /// </summary>  
  152. 152         /// <param name="oApp">Excel对象</param>  
  153. 153         /// <param name="oRunArgs">参数(第二个参数为钦点宏名称,前边为钦命宏的参数值)</param>  
  154. 154         /// <returns>宏重回值</returns>  
  155. 155         private object RunMacro(object oApp, object[] oRunArgs)  
  156. 156         {  
  157. 157             try  
  158. 158             {  
  159. 159                 // 声圣元(Karicare)个赶回对象  
  160. 160                 object objRtn;  
  161. 161                   
  162. 162                 // 反射格局进行宏  
  163. 163                 objRtn = oApp.GetType().InvokeMember(  
  164. 164                                                         "Run",  
  165. 165                                                         System.Reflection.BindingFlags.Default |  
  166. 166                                                         System.Reflection.BindingFlags.InvokeMethod,  
  167. 167                                                         null,  
  168. 168                                                         oApp,  
  169. 169                                                         oRunArgs  
  170. 170                                                      );  
  171. 171   
  172. 172                 // 返回值  
  173. 173                 return objRtn;  
  174. 174   
  175. 175             }  
  176. 176             catch (Exception ex)  
  177. 177             {  
  178. 178                 // 假如有底层分外,抛出底层万分  
  179. 179                 if (ex.InnerException.Message.ToString().Length > 0)  
  180. 180                 {  
  181. 181                     throw ex.InnerException;  
  182. 182                 }  
  183. 183                 else  
  184. 184                 {  
  185. 185                     throw ex;  
  186. 186                 }  
  187. 187             }  
  188. 188         }  
  189. 189     }  
  190. 190 }  
  191. 191   

 

演示五个VBA宏方法:

Java代码 

 图片 3)

  1. 1 Sub getTime()  
  2.  2   
  3.  3     Sheet1.Cells(1, 1) = Now  
  4.  4   
  5.  5 End Sub  
  6.  6   
  7.  7   
  8.  8 Sub getTime2(title As String)  
  9.  9   
  10. 10     Sheet1.Cells(2, 1) = title & " : " & Now  
  11. 11   
  12. 12 End Sub  
  13. 13   
  14. 14 Function getTime3(title As String)  As String  
  15. 15   
  16. 16     getTime3 = title & " : " & Now  
  17. 17   
  18. 18 End Function  
  19. 19   

 

相应的多少个应用办法 1 不带参数的宏调用(兼演示实施进度显示Excel文件) 2 带参数的宏调用(兼演示推行进度不显示Excel文件) 3 有重临值的宏调用

 

Java代码 

 图片 4)

  1.  1         private void btnExe_Click(object sender, EventArgs e)  
  2.  2         {  
  3.  3             try  
  4.  4             {  
  5.  5                 // 重返对象  
  6.  6                 object objRtn = new object();  
  7.  7   
  8.  8                 // 获得一个ExcelMacroHelper对象  
  9.  9                 ExcelMacroHelper excelMacroHelper = new ExcelMacroHelper();  
  10. 10   
  11. 11                 // 实行钦赐Excel中的宏,实行时突显Excel  
  12. 12                 excelMacroHelper.RunExcelMacro(  
  13. 13                                                     @"E:csharp_studyDoVBAMacrotest.xls",  
  14. 14                                                     "getTime2",  
  15. 15                                                     new Object[] { "今后时刻" },  
  16. 16                                                     out objRtn,  
  17. 17                                                     true  
  18. 18                                               );  
  19. 19   
  20. 20                 // 施行钦定Excel中的宏,试行时不展现Excel  
  21. 21                 excelMacroHelper.RunExcelMacro(  
  22. 22                                                     @"E:csharp_studyDoVBAMacrotest.xls",  
  23. 23                                                     "getTime2",  
  24. 24                                                     new Object[] { "现在每日" },  
  25. 25                                                     out objRtn,  
  26. 26                                                     false  
  27. 27                                                );  
  28. 28   
  29. 29                 // 奉行内定Excel中的宏,推行时显示Excel,有重临值  
  30. 30                 excelMacroHelper.RunExcelMacro(  
  31. 31                                                     @"E:csharp_studyDoVBAMacrotest.xls",  
  32. 32                                                     "getTime3",  
  33. 33                                                     new Object[] { "今后时时" },  
  34. 34                                                     out objRtn,  
  35. 35                                                     true  
  36. 36                                                );  
  37. 37   
  38. 38                 MessageBox.Show((string)objRtn);  
  39. 39   
  40. 40             }  
  41. 41             catch(System.Exception ex)  
  42. 42             {  
  43. 43                 MessageBox.Show(ex.Message);  
  44. 44             }  
  45. 45         }  

 演示工程下载

本文由永利皇宫463登录发布于编程,转载请注明出处:调用Excel宏批量处理文件

关键词:

上一篇:没有了

下一篇:调用Excel宏批量处理文件