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

 

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

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

 通过java调用VBS,再用VBS执行Excel中的宏的例子[原创]

(2) 降低宏的安全级别

通过java调用VBS,再用VBS执行Excel中的宏的例子[原创]

(二)  按照下面代码编写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)。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值