VBS对EXCEL的操作示例
上一篇 /
下一篇 2010-12-13 20:59:34
/ 个人分类:VBS
1.Add Data to a Spreadsheet Cell
Demonstration script. that adds the words "Test Value" to cell 1,1 in a new spreadsheet.
Set bjExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"
2.Add Formatted Data to a Spreadsheet
Demonstration script. that adds the words "test value" to a new spreadsheet, then formats the cell containing the value.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1,1).Value = "Test value"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 24
objExcel.Cells(1, 1).Font.ColorIndex = 3
objExceL.Caption="JIAORUI2008"
3.Format a Range of Cells
Demonstration script. that adds data to four different cells in a spreadsheet, then uses the Range object to format multiple cells at the same time.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Name"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Interior.ColorIndex = 35
objExcel.Cells(1, 1).Font.ColorIndex = 2
objExcel.Cells(2, 1).Value = "Test value 1"
objExcel.Cells(3, 1).Value = "Test value 2"
objExcel.Cells(4, 1).Value = "Tets value 3"
objExcel.Cells(5, 1).Value = "Test value 4"
Set objRange = objExcel.Range("A1","A5")
objRange.Font.Size = 14
Set objRange = objExcel.Range("A2","A5")
objRange.Interior.ColorIndex = 36
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()'自动适应大小
objExcel.Quit
4.List Excel Color Values
Demonstration script. that displays the various colors -- and their related color index -- available when programmatically controlling Microsoft Excel.
Set bjExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
For i = 1 to 56
objExcel.Cells(i, 1).Value = i
objExcel.Cells(i, 1).Interior.ColorIndex = i
Next
5.List Service Data in a Spreadsheet
Demonstration script. that retrieves information about each service running on a computer, and then displays that data in an Excel spreadsheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
x = 1
strComputer = "."
Set objWMIService = GetObject _
("winmgmts:\\" & strComputer & "\root\cimv2")
Set colServices = objWMIService.ExecQuery _
("Select * From Win32_Service")
For Each objService in colServices
objExcel.Cells(x, 1) = objService.Name
objExcel.Cells(x, 2) = objService.State
x = x + 1
Next
6.Open an Excel Spreadsheet
Demonstration script. that opens an existing Excel spreadsheet named C:\Scripts\New_users.xls.
Set bjExcel = CreateObject("Excel.Application")
Set bjWorkbook = objExcel.Workbooks.Open("C:\Scripts\New_users.xls")
7.Read an Excel Spreadsheet
Demonstration script. that reads the values stored in a spreadsheet named C:\Scripts\New_users.xls.
Set bjExcel = CreateObject("Excel.Application")
Set bjWorkbook = objExcel.Workbooks.Open _
("C:\Scripts\New_users.xls")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
Wscript.Echo "CN: " & objExcel.Cells(intRow, 1).Value
Wscript.Echo "sAMAccountName: " & objExcel.Cells(intRow, 2).Value
Wscript.Echo "GivenName: " & objExcel.Cells(intRow, 3).Value
Wscript.Echo "LastName: " & objExcel.Cells(intRow, 4).Value
intRow = intRow + 1
Loop
objExcel.Quit
收藏
举报
TAG:
vbs
VBS