从九十到一百很难，但是从零到九十很简单。

VB Script: How to write data to excel

上一篇 / 下一篇  2011-04-16 01:09:06 / 个人分类：VB Script

查看( 755 ) / 评论( 0 ) / 评分( 0 / 0 )

Refer to :http://www.activexperts.com/activmonitor/windowsmanagement/scripts/msoffice/excel/

 

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"

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 bjExcel = 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

Create User Accounts Based on Information in a Spreadsheet


Demonstration script. that creates new Active Directory user accounts based on information stored in an Excel spreadsheet.
Set bjExcel = CreateObject("Excel.Application")
Set bjWorkbook = objExcel.Workbooks.Open _
    ("C:\Scripts\New_users.xls")

intRow = 2

Do Until objExcel.Cells(intRow,1).Value = ""
    Set bjOU = GetObject("ou=Finance, dc=fabrikam, dc=com")
    Set bjUser = objOU.Create _
        ("User", "cn=" & objExcel.Cells(intRow, 1).Value)
    objUser.sAMAccountName = objExcel.Cells(intRow, 2).Value
    objUser.GivenName = objExcel.Cells(intRow, 3).Value
    objUser.SN = objExcel.Cells(intRow, 4).Value
    objUser.AccountDisabled = FALSE
    objUser.SetInfo
    intRow = intRow + 1
Loop

objExcel.Quit

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 bjExcel = 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 = 30
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 bjRange = objExcel.Range("A1","A5")
objRange.Font.Size = 14

Set bjRange = objExcel.Range("A2","A5")
objRange.Interior.ColorIndex = 36

Set bjRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()

List Active Directory Data in a Spreadsheet


Demonstration script. that retrieves data from Active Directory and then displays that data in an Excel spreadsheet.
Const ADS_SCOPE_SUBTREE = 2

Set bjExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add

objExcel.Cells(1, 1).Value = "Last name"
objExcel.Cells(1, 2).Value = "First name"
objExcel.Cells(1, 3).Value = "Department"
objExcel.Cells(1, 4).Value = "Phone number"

Set bjConnection = CreateObject("ADODB.Connection")
Set bjCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
objCommand.CommandText = _
    "SELECT givenName, SN, department, telephoneNumber FROM " _
        & "'LDAP://dc=fabrikam,dc=microsoft,dc=com' WHERE " _
            & "objectCategory='user'"  
Set bjRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2

Do Until objRecordSet.EOF
    objExcel.Cells(x, 1).Value = _
        objRecordSet.Fields("SN").Value
    objExcel.Cells(x, 2).Value = _
        objRecordSet.Fields("givenName").Value
    objExcel.Cells(x, 3).Value = _
        objRecordSet.Fields("department").Value
    objExcel.Cells(x, 4).Value = _
        objRecordSet.Fields("telephoneNumber").Value
    x = x + 1
    objRecordSet.MoveNext
Loop

Set bjRange = objExcel.Range("A1")
objRange.Activate

Set bjRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set bjRange = objExcel.Range("B1")
objRange.Activate
Set bjRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set bjRange = objExcel.Range("C1")
objRange.Activate

Set bjRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set bjRange = objExcel.Range("D1")
objRange.Activate

Set bjRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set bjRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

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

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 bjExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

x = 1
strComputer = "."
Set bjWMIService = 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

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

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:

 

评分：0

我来说两句

日历

« 2024-05-11  
日 一 二 三 四 五 六
   1234
567891011
12131415161718
19202122232425
262728293031 

数据统计

  • 访问量: 6249
  • 日志数: 11
  • 建立时间: 2010-05-08
  • 更新时间: 2011-09-14

RSS订阅

Open Toolbar