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

 

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

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

 

(2) 降低宏的安全级别

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

OptionExplicit 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的宏中即可：

'authorguoqiang 'date2007-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            EndIf        '---------------------------------------        '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代码的位置 Processprocess=Runtime.getRuntime().exec("cmd /c CScriptE:/Temp/vbs.vbs"); //等待VBS执行完毕 process.waitFor(); //TODO:其他的代码

 

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