Vbs 操作 EXCEL 方法总结 : \-- 打开 Excel 电子表格 -- 说明 -- 演示脚本打开名为 C:\Scripts\New_users.xls 的现有 Excel 电子表格 2. Set objworkbook = objexcel.workbooks.open("c:\scripts\new_users.xls") \-- 读取 Excel 电子表格 -- 说明 -- 演示脚本读取名为 C:\Scripts\New_users.xls 的电子表格中存储的值 2. Set objworkbook = objexcel.workbooks.open("c:\scripts\new_users.xls") 3. introw = 2 4. Do Until objexcel.cells(introw,1).value = "" 5. Wscript.Echo "CN: " & objexcel.cells(introw, 1).Value 6. Wscript.Echo "samaccountname: " & objexcel.cells(introw, 2).Value 7. Wscript.Echo "GivenName: " & objexcel.cells(introw, 3).Value 8. Wscript.Echo "LastName: " & objexcel.cells(introw, 4).Value 9. introw = introw + 1 10. Loop 11. objexcel.quit \-- 自动调整 Microsoft Excel 工作表中的列 -- 说明
-- 演示脚本检索计算机上安装的服务的相关信息, 将该数据写入到 Excel 电子表格中, 然后使用 Autofit() 方 法根据该数据来自动调整列 3. Set objworkbook = objexcel.workbooks.add() 4. Set objworksheet = objworkbook.worksheets(1) 5. x = 1 6. strcomputer = "." 7. Set objwmiservice = _ 8. GetObject("winmgmts:\\" & strcomputer & "\root\cimv2") 9. Set colitems = objwmiservice.execquery _ 10. ("Select * from Win32_Service") 11. For Each objitem in colitems 12. objworksheet.cells(x, 1) = objitem.name 13. objworksheet.cells(x, 2) = objitem.displayname 14. objworksheet.cells(x, 3) = objitem.state 15. x = x + 1 16. Next 17. Set objrange = objworksheet.usedrange 18. objrange.entirecolumn.autofit() \-- 将数据添加到电子表格单元格中 3. objexcel.workbooks.add 4. objexcel.cells(1, 1).Value = "Test value" 向单元格 1,1 中写入 "Test value"
\-- 将带格式的数据添加到电子表格中 -- 说明 -- 演示脚本将文字 test value 添加到新的电子表格中, 然后设置包含该值的单元格的格式 3. objexcel.workbooks.add 4. objexcel.cells(1, 1).Value = "Test value" 写入内容 5. objexcel.cells(1, 1).Font.Bold = TRUE 加粗为真 6. objexcel.cells(1, 1).Font.Size = 24 单元格大小为 24 7. objexcel.cells(1, 1).Font.ColorIndex = 3 颜色号为 3 \-- 列出 Excel 颜色值 -- 说明 -- 演示脚本显示在以编程方式控制 Microsoft Excel 时可用的各种颜色 ( 及其相关颜色索引 ) 3. objexcel.workbooks.add 4. For i = 1 to 56 一共有 56 种颜色可供选择 5. objexcel.cells(i, 1).Value = i 6. objexcel.cells(i, 1).Interior.ColorIndex = i 7. Next
\-- 列出电子表格中的服务数据 -- 说明 -- 演示脚本检索计算机上运行的每种服务的相关信息, 然后在 Excel 电子表格中显示该数据 3. objexcel.workbooks.add 4. x = 1 5. strcomputer = "." 6. Set objwmiservice = GetObject _ 7. ("winmgmts:\\" & strcomputer & "\root\cimv2") 8. Set colservices = objwmiservice.execquery _ 9. ("Select * From Win32_Service") 10. For Each objservice in colservices 11. objexcel.cells(x, 1) = objservice.name 12. objexcel.cells(x, 2) = objservice.state 13. x = x + 1 14. Next \-- 对 Excel 电子表格应用自动套用格式 -- 说明 -- 演示脚本将数据添加到 Excel 电子表格中, 然后对该数据应用 AutoFormat(xpRangeAutoFormatList2) 1. Const xprangeautoformatlist2 = 11 2. Set objexcel = CreateObject("Excel.Application") 3. objexcel.visible = True 4. Set objworkbook = objexcel.workbooks.add() 5. Set objworksheet = objworkbook.worksheets(1)
6. k = 1 7. For i = 1 to 10 8. For j = 1 to 10 9. objworksheet.cells(i,j) = k 10. k = k + 1 11. Next 12. Next 13. Set objrange = objworksheet.usedrange 14. objrange.autoformat(xprangeautoformatlist2) \-- 基于电子表格中的信息来创建用户帐户 -- 说明 -- 演示脚本基于 Excel 电子表格中存储的信息来创建 Active Directory 用户帐户 2. Set objworkbook = objexcel.workbooks.open("c:\scripts\new_users.xls") 3. introw = 2 4. Do Until objexcel.cells(introw,1).value = "" 5. Set objou = GetObject("ou=Finance, dc=fabrikam, dc=com") 6. Set objuser = objou.create("user", "cn=" & objexcel.cells(introw, 1).Value) 7. objuser.samaccountname = objexcel.cells(introw, 2).Value 8. objuser.givenname = objexcel.cells(introw, 3).Value 9. objuser.sn = objexcel.cells(introw, 4).Value 10. objuser.accountdisabled = FALSE 11. objuser.setinfo 12. introw = introw + 1 13. Loop 14. objexcel.quit
\-- 对 Microsoft Excel 工作表进行排序 -- 说明 -- 演示脚本将数据添加到 Microsoft Excel 电子表格中, 然后在列 A 中对该数据进行排序 3. Set objworkbook = objexcel.workbooks.add 4. Set objworksheet = objworkbook.worksheets(1) 5. objexcel.cells(1, 1).Value = "4" 6. objexcel.cells(2, 1).Value = "1" 7. objexcel.cells(3, 1).Value = "2" 8. objexcel.cells(4, 1).Value = "3" 9. objexcel.cells(1, 2).Value = "A" 10. objexcel.cells(2, 2).Value = "B" 11. objexcel.cells(3, 2).Value = "C" 12. objexcel.cells(4, 2).Value = "D" 13. Set objrange = objworksheet.usedrange 14. Set objrange2 = objexcel.range("a1") 15. objrange.sort(objrange2) \-- 设置一组单元格的格式 -- 说明 -- 演示脚本将数据添加到电子表格的四个不同的单元格中, 然后使用 Range 对象同时设置多个单元格的格式
3. objexcel.workbooks.add 4. objexcel.cells(1, 1).Value = "Name" 5. objexcel.cells(1, 1).Font.Bold = TRUE 6. objexcel.cells(1, 1).Interior.ColorIndex = 30 7. objexcel.cells(1, 1).Font.ColorIndex = 2 8. objexcel.cells(2, 1).Value = "Test value 1" 9. objexcel.cells(3, 1).Value = "Test value 2" 10. objexcel.cells(4, 1).Value = "Tets value 3" 11. objexcel.cells(5, 1).Value = "Test value 4" 12. Set objrange = objexcel.range("a1","a5") 13. objrange.font.size = 14 14. Set objrange = objexcel.range("a2","a5") 15. objrange.interior.colorindex = 36 16. Set objrange = objexcel.activecell.entirecolumn 17. objrange.autofit() \-- 在三个不同的列上对 Excel 电子表格进行排序 -- 说明 -- 演示脚本打开 Excel 电子表格, 然后对列 A( 升序 ) 列 B( 降序 ) 和列 C( 升序 ) 中的数据进行排序 此脚本假定工作表包含一个标题行 ( 由 xlyes 来指示, 它是 Sort 方法中的最后一个参数 ) 脚本需要一个名为 C:\Scripts\Sort_test.xls 的 Excel 文件 1. Const xlascending = 1 2. Const xldescending = 2 3. Const xlyes = 1 4. Set objexcel = CreateObject("Excel.Application") 5. objexcel.visible = True 6. Set objexcel = CreateObject("Excel.Application") 7. objexcel.visible = True
8. Set objworkbook = _ 9. objexcel.workbooks.open("c:\scripts\sort_test.xls") 10. Set objworksheet = objworkbook.worksheets(1) 11. Set objrange = objworksheet.usedrange 12. Set objrange2 = objexcel.range("a1") 13. Set objrange3 = objexcel.range("b1") 14. Set objrange4 = objexcel.range("c1") 15. objrange.sort objrange2,xlascending,objrange3,,xldescending, _ 16. objrange4,xldescending,xlyes \-- 列出电子表格中的 Active Directory 数据 -- 说明 -- 演示脚本从 Active Directory 检索数据, 然后在 Excel 电子表格中显示该数据 1. Const ADS_SCOPE_SUBTREE = 2 2. Set objexcel = CreateObject("Excel.Application") 3. objexcel.visible = True 4. objexcel.workbooks.add 5. objexcel.cells(1, 1).Value = "Last name" 6. objexcel.cells(1, 2).Value = "First name" 7. objexcel.cells(1, 3).Value = "Department" 8. objexcel.cells(1, 4).Value = "Phone number" 9. Set objconnection = CreateObject("ADODB.Connection") 10. Set objcommand = CreateObject("ADODB.Command") 11. objconnection.provider = "ADsDSOObject" 12. objconnection.open "Active Directory Provider" 13. Set objcommand.activeconnection = objconnection 14. objcommand.properties("page Size") = 100 15. objcommand.properties("searchscope") = ADS_SCOPE_SUBTREE 16. objcommand.commandtext = _
17. "SELECT givenname, SN, department, telephonenumber FROM " _ 18. & "LDAP://dc=fabrikam,dc=microsoft,dc=com WHERE " _ 19. & "objectcategory=user" 20. Set objrecordset = objcommand.execute 21. objrecordset.movefirst 22. x = 2 23. Do Until objrecordset.eof 24. objexcel.cells(x, 1).Value = _ 25. objrecordset.fields("sn").value 26. objexcel.cells(x, 2).Value = _ 27. objrecordset.fields("givenname").value 28. objexcel.cells(x, 3).Value = _ 29. objrecordset.fields("department").value 30. objexcel.cells(x, 4).Value = _ 31. objrecordset.fields("telephonenumber").value 32. x = x + 1 33. objrecordset.movenext 34. Loop 35. Set objrange = objexcel.range("a1") 36. objrange.activate 37. Set objrange = objexcel.activecell.entirecolumn 38. objrange.autofit() 39. Set objrange = objexcel.range("b1") 40. objrange.activate 41. Set objrange = objexcel.activecell.entirecolumn 42. objrange.autofit() 43. Set objrange = objexcel.range("c1") 44. objrange.activate 45. Set objrange = objexcel.activecell.entirecolumn 46. objrange.autofit() 47. Set objrange = objexcel.range("d1") 48. objrange.activate 49. Set objrange = objexcel.activecell.entirecolumn 50. objrange.autofit()
51. Set objrange = objexcel.range("a1").specialcells(11) 52. Set objrange2 = objexcel.range("c1") 53. Set objrange3 = objexcel.range("a1")