- dim counter,counter1,counter2,countera,counterb,numbera,numberb,numberc '声明变量
- '生成数组
- numbera=array("姓名","宽带","电话","小灵通","手机")
- numberb=array("刘起涯","周月","向文章","区同雨","顾思明","李东","张浩","合计")
- numberc=array("b","c","d","e")
- set objexcel=nothing '置空objexcel对象
- '设置object对象
- set objexcel=createobject("excel.application")
- set objworks=objexcel.workbooks
- set wshshell=createobject("wscript.shell")
- set delfile=createobject("scripting.filesystemobject")
- objexcel.visible=true '设置工作表对象可见
- objworks.open("d:\test\test.xls") '打开test.xls
- objworks.add '新建工作表
- objexcel.worksheets(1).activate '将工作表的第一页设为活动页
- '向工作表第一行中写入项目
- for counter1=0 to 4
- counter=counter1+1
- objexcel.cells(1,counter).value=numbera(counter1)
- next
- '向工作表中第一列中写入姓名和合计字符
- for counter2=0 to 7
- counter=counter2+2
- objexcel.cells(counter,1).value=numberb(counter2)
- next
- '逐行从第二列开始向工作表中写入数组公式
- for counter1=0 to 6
- countera=counter1+2
- for counter2=0 to 3
- counterb=counter2+2
- objexcel.cells(countera,counterb).value="=SUM(([test.xls]Sheet1!$A$2:$A$1001=A"&countera&")*([test.xls]Sheet1!$B$2:$B$1001="&numberc(counter2)&"1)*[test.xls]Sheet1!$C$2:$C$1001)"
- objexcel.cells(countera,counterb).activate
- wscript.sleep 10
- wshshell.sendkeys "{f2}"
- wscript.sleep 10
- wshshell.sendkeys "^+~" '发送组合键ctrl+shift+enter生成数组公式
- next
- next
- '写入求和公式
- for counter1=0 to 3
- countera=counter1+2
- objexcel.cells(9,countera).value="=sum("&numberc(counter1)&"2:"&numberc(counter1)&"8)"
- next
- '设置工作表的字体颜色
- objExcel.ActiveSheet.Rows.Font.Name="楷体_GB2312"
- objExcel.ActiveSheet.Rows.Font.size=12
- objExcel.ActiveSheet.Rows.Font.Color=vbblue
- objExcel.ActiveSheet.Rows(1).Font.Color=vbred
- objExcel.ActiveSheet.Rows(1).Font.Bold=true
- objexcel.activeworkbook.saveas("d:\test\temp.xls") '另存工作表为d:\test\temp.xls
- wscript.sleep 3000
- objexcel.quit
- set objexcel=nothing
- 'delfile.deletefile "d:\test\temp.xls" '如要删除新建的temp.xls请将d前面的'去掉
复制代码