通过java调用VBS，再用VBS执行Excel中的宏的例子

http://blog.sina.com.cn/s/blog_58b9cb3a01007kdt.html

 

(一)  首先需要降低客户端及服务器端对于Excel的宏的安全级别的要求，按照下述的图示进行操作：

(1)进入宏的安全级别设置功能

 

 

(2) 降低宏的安全级别

(二)  按照下面代码编写VBS代码

Option Explicit Dim objXLApp Dim objXLBook Set objXLApp = WScript.CreateObject("Excel.Application") ‘注意以下的“e/data_test.xls”的位置需要修改为包含宏的那个excel的实际位置 ‘位置需要设置为绝对路径 Set objXLBook = objXLApp.Workbooks.Open("e:/data_test.xls" )   ‘doChangeExcel即为excel中的宏方法的名称 objXLApp.Run "doChangeExcel" 'objXLBook.Saved = True objXLBook.Save objXLBook.Close Set objXLBook = Nothing objXLApp.Quit Set objXLApp = Nothing WScript.Quit

(三)  以下是excel中的宏，仅需要复制到excel的宏中即可：

'author guoqiang 'date 2007-12-19 Sub doChangeExcel ()     Dim count     count = Sheets.count     Debug.Print count     For i = 1 To count         'get the assigned sheet         Sheets.Item(i).Select                 'get the name of the sheet         SheetName = ActiveSheet.Name         protected = ActiveSheet.ProtectContents         'ptotected = True         ActiveSheet.Select                 '---------------------------------------         'if it is Trans_Xcpt sheet, then row 5 is autofilter         If SheetName = "Trans_Xcpt" Then             If protected = True Then                 ActiveSheet.Select                 ActiveSheet.Unprotect                 Rows("5:5").Select                 Selection.AutoFilter             Else                 Rows("5:5").Select                 Selection.AutoFilter                 ActiveSheet.Protect             End If         '---------------------------------------         'if it is MTD_IN sheet, then row 4 is autofilter         ElseIf SheetName = "MTD_IN" Then             If protected = True Then                 ActiveSheet.Select                 ActiveSheet.Unprotect                 Rows("4:4").Select                 Selection.AutoFilter             Else                 Rows("4:4").Select                 Selection.AutoFilter                 ActiveSheet.Protect             End If         '---------------------------------------         'if it is Net_Demand sheet, then row 4 is autofilter         ElseIf SheetName = "Net_Demand" Then             If protected = True Then                 ActiveSheet.Select                 ActiveSheet.Unprotect                 Rows("4:4").Select                 Selection.AutoFilter             Else                 Rows("4:4").Select                 Selection.AutoFilter                 ActiveSheet.Protect             End If         '---------------------------------------         'if it is A_Supply sheet, then row 4 is autofilter         ElseIf SheetName = "A_Supply" Then             If protected = True Then                 ActiveSheet.Select                 ActiveSheet.Unprotect                 Rows("4:4").Select                 Selection.AutoFilter             Else                 Rows("4:4").Select                 Selection.AutoFilter                 ActiveSheet.Protect             End If         '---------------------------------------         'if it is Special_Cell sheet, then row 4 is autofilter         ElseIf SheetName = "Special_Cell" Then             If protected = True Then                 ActiveSheet.Select                 ActiveSheet.Unprotect                 Rows("4:4").Select                 Selection.AutoFilter             Else                 Rows("4:4").Select                 Selection.AutoFilter                 ActiveSheet.Protect             End If         '---------------------------------------         'else, only protect or unprotect         Else             If protected = True Then                 ActiveSheet.Select                 ActiveSheet.Unprotect             Else                 ActiveSheet.Select                 ActiveSheet.Protect             End If         End If         Next i End Sub

(四)  在java代码中进行执行（注意：仅仅支持windows2000以上的系统）：

//TODO:其他的代码 //以下是执行VBS代码 //注意“E:/Temp/vbs.vbs”应该是正确的存储的VBS代码的位置 Process process=Runtime.getRuntime().exec("cmd /c CScript E:/Temp/vbs.vbs"); //等待VBS执行完毕 process.waitFor(); //TODO: 其他的代码

 

注意：该宏会自动判断是否需要进行protect(宏程序中假定protect和autofilter是一致的，即同时为true或者false)。