Microsoft Excel Scripts
Add Data to a Spreadsheet CellAdd Formatted Data to a Spreadsheet
Create User Accounts Based on Information in a Spreadsheet
Format a Range of Cells
List Active Directory Data in a Spreadsheet
List Excel Color Values
List Service Data in a Spreadsheet
Open an Excel Spreadsheet
Read an Excel Spreadsheet
You can use any of the VBScript programs below in ActiveXperts Network Monitor. Click here for an explanation about how to include scripts in ActiveXperts Network Monitor.
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