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")

ForEach objServicein 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

 

评分:0

我来说两句

我的栏目

日历

« 2024-03-16  
     12
3456789
10111213141516
17181920212223
24252627282930
31      

数据统计

  • 访问量: 6356
  • 日志数: 12
  • 建立时间: 2010-12-10
  • 更新时间: 2011-02-24

RSS订阅

Open Toolbar