Don Hite at myITforum.com

Vbs Script To Send SMS Console Collection Information To Excel

Use this Vbs script to send SMS collection information to Excel.

Vbs Script:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "CollectionID"

objExcel.Cells(1, 2).Value = "CollectionRules"

objExcel.Cells(1, 3).Value = "Comment"

objExcel.Cells(1, 4).Value = "CurrentStatus"

objExcel.Cells(1, 5).Value = "MemberClassName"

objExcel.Cells(1, 6).Value = "Name"

objExcel.Cells(1, 7).Value = "OwnedByThisSite"

objExcel.Cells(1, 8).Value = "RefreshSchedule"

objExcel.Cells(1, 9).Value = "RefreshType"

objExcel.Cells(1, 10).Value = "ReplicateToSubSites"

On Error Resume Next

Set Fso = CreateObject("Scripting.FileSystemObject")

strComputer = InputBox("Enter Site Server Name")

strSiteCode = InputBox("Enter Site Code")

Set objWMIService = GetObject("winmgmts://" & strComputer & "\root\sms\site_" & strSiteCode)

Set colItems = objWMIService.ExecQuery("Select * from SMS_Collection")

For Each objItem in colItems

objExcel.Cells(intRow, 1).Value = objItem.CollectionID

objExcel.Cells(intRow, 2).Value = objItem.CollectionRules

objExcel.Cells(intRow, 3).Value = objItem.Comment

objExcel.Cells(intRow, 4).Value = objItem.CurrentStatus

objExcel.Cells(intRow, 5).Value = objItem.MemberClassName

objExcel.Cells(intRow, 6).Value = objItem.Name

objExcel.Cells(intRow, 7).Value = objItem.OwnedByThisSite

objExcel.Cells(intRow, 8).Value = objItem.RefreshSchedule

objExcel.Cells(intRow, 9).Value = objItem.RefreshType

objExcel.Cells(intRow, 10).Value = objItem.ReplicateToSubSites

intRow = intRow + 1

Next

Set colItems = Nothing

Set objWMIService = Nothing

objExcel.Range("A1:J1").Select

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit