Qtp excel functions

Add Data to a Spreadsheet Cell

Demonstration script that adds the words "Test Value" to cell 1,1 in a new spreadsheet.

Set objExcel = 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 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

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 objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open _

("C:\Scripts\New_users.xls")

intRow = 2

Do Until objExcel.Cells(intRow,1).Value = ""

Set objOU = GetObject("ou=Finance, dc=fabrikam, dc=com")

Set objUser = 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 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 = 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 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()

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 objExcel = 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 objConnection = CreateObject("ADODB.Connection")

Set objCommand = 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 objRecordSet = 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 objRange = objExcel.Range("A1")

objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn

objRange.Autofit()

Set objRange = objExcel.Range("B1")

objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn

objRange.Autofit()

Set objRange = objExcel.Range("C1")

objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn

objRange.Autofit()

Set objRange = objExcel.Range("D1")

objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn

objRange.Autofit()

Set objRange = 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 objExcel = 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 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

Open an Excel Spreadsheet

Demonstration script that opens an existing Excel spreadsheet named C:\Scripts\New_users.xls.

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = 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 objExcel = CreateObject("Excel.Application")

Set objWorkbook = 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