【练习-057】vbs生成excel工作表并写入指定内容

batman

出题目的：
　　让大家初步接触并了解vbs对excel的操作方法
加分原则：
　　满分10分
题目如下：
　　用vbs在当前目录生成test.xls，并向工作表的c3:f10中写入所在的行列数(以空格格开)，如单元格为d8则写入
8 4。

qaz123654

1. dim aa
   
2. aa=left(wscript.scriptfullname,instrrev(wscript.scriptfullname,"")-1)
   
3. Set oexcel=CreateObject("excel.application")
   
4. oexcel.Workbooks.Add()
   
5. oexcel.Cells(3,3).Value = "4 1"
   
6. oexcel.Cells(10,6).Value = "10 6"
   
7. oexcel.ActiveWorkbook.SaveAs(aa+"\test.xls")
   
8. oexcel.Quit

复制代码

batman

楼上的是不是看错题意了，要求是向工作表的c3:f10范围内写入数字，不是只写入两个单元格中。。。

获取本地路径个人习惯用path=createobject("wscript.shell").currentdirectory&"\"

[ 本帖最后由 batman 于 2010-1-6 17:06 编辑 ]

qaz123654

的确是看错了~~

1. 
   
2. dim aa
   
3. aa=createobject("wscript.shell").currentdirectory&""
   
4. Set oexcel=CreateObject("excel.application")
   
5. oexcel.Workbooks.Add()
   
6. Set obj = oexcel.Range("C3","F10")
   
7. obj.columns ="=CONCATENATE(ROW(),"" "",COLUMN())"
   
8. oexcel.ActiveWorkbook.SaveAs(aa+"test.xls")
   
9. oexcel.Quit
   

复制代码

batman

今天才知道能根据给定单元格范围输入，以前都是用两个for来完成的。。。

batman

obj.columns ="=CONCATENATE(ROW(),"" "",COLUMN())"
巧妙地利用了excel本身的查找引用函数，但可以简化修改为obj.columns ="=ROW()&"" ""&COLUMN()"

[ 本帖最后由 batman 于 2010-1-6 18:41 编辑 ]

zqz0012005

left(wscript.scriptfullname,instrrev(wscript.scriptfullname,"\")-1)是脚本所在目录，等价于%~dp0
createobject("wscript.shell").currentdirectory&"\"是脚本工作目录，等价于%cd%
两者不是等同的，最显著的例子是在拖放时（拖放时工作在%userprofile%）。不少人对拖放的结果感到莫名其妙（怎么没有生成文件？），就是没有注意到这一点。
推荐用%~dp0

其实vbs操作excel最好的方式是vba，一个好去处是excelhome，当然MSDN上的Office参考是少不了

batman

简化4楼代码如下：

1. Set oexcel=CreateObject("excel.application")
   
2. oexcel.Workbooks.Add()
   
3. oexcel.Range("C3:F10").columns ="=ROW()&"" ""&COLUMN()"
   
4. oexcel.ActiveWorkbook.SaveAs(createobject("wscript.shell").currentdirectory&"\test.xls")
   
5. oexcel.Quit

复制代码

[ 本帖最后由 batman 于 2010-1-6 19:01 编辑 ]

Undead

两个FOR：

1. strString = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
   
2. strStart = "C3"
   
3. strEnd = "F10"
   
4. 
   
5. IntCStart = InStr(strString, Left(strStart, 1))
   
6. IntCEnd = InStr(strString, Left(strEnd, 1))
   
7. 
   
8. IntLStart = Mid(strStart, 2)
   
9. IntLEnd = Mid(strEnd, 2)
   
10. 
    
11. WorkPath = Left(Wscript.ScriptFullName, InStrRev(Wscript.ScriptFullName, "")-1)
    
12. 
    
13. Set objExcel = CreateObject("Excel.Application")
    
14. objExcel.Visible = True
    
15. objExcel.Workbooks.Add
    
16. 
    
17. For i = IntCStart To IntCEnd
    
18.     For j = IntLStart To IntLEnd
    
19.          objExcel.Cells(j, i).Value = j & " " & i
    
20.     Next
    
21. Next
    
22. 
    
23. objExcel.ActiveWorkbook.SaveAs(WorkPath & "\a.xls")
    
24. objExcel.Quit

复制代码

[ 本帖最后由 Undead 于 2010-1-7 12:51 编辑 ]

batman

路径不对，在上级目录生成了"本级目录名a.xls"

同时前面的截取是不是显得过于复杂，其实你直接3 to 10,3 to 6就可以了啊

batman

本人的解：

1. dim counter1,counter2
   
2. set objexcel=createobject("excel.application")
   
3. objexcel.workbooks.add
   
4. for counter1=3 to 10
   
5.     for counter2=3 to 6
   
6.         objexcel.cells(counter1,counter2).value=counter1&" "&counter2
   
7.     next
   
8. next
   
9. objexcel.activeworkbook.saveas(createobject("wscript.shell").currentdirectory&"\test.xls")
   
10. objexcel.quit
    
11. set objexcel=nothing

复制代码

Undead

的确是在上级目录生成了a.xls。已经修改，感谢指正。

之所以搞的那么复杂是因为本来想做成让用户自己输入C3和F10的。

more

选择目标单元格后用数组公式:
{=ROW()&" "&COLUMN()}

more

来个vba的:

1. Sub aaa()
   
2.     Range("c3:f10").Select
   
3.     Selection.FormulaArray = "=row()&"" ""&column()"
   
4. End Sub

复制代码

wc726842270

smile版主真是细心，学习了
不过4L的obj.columns ="=CONCATENATE(ROW(),"" "",COLUMN())"还看是没看明白，可能是对EXCEL的功能和函数不是很了解吧