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前面的'去掉
复制代码