Don Hite

VBS Script Example For Calling An SMS Or ConfigMgr View And Sending Specific Fields To Excel

This VBS script will provide you with and example of how you can call an SMS 2003 or ConfigMgr 2007 SQL database view and retrieve selected columns or fields and write them to an Excel spreadsheet.

VBS Script:

strServer = InputBox ("Enter SQL Server Name")

strDatabase = InputBox ("Enter Three Letter Site Code")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

objExcel.Cells(1, 2).Value = "Client Version"

objExcel.Cells(1, 3).Value = "User Name"

objExcel.Cells(1, 4).Value = "User Domain"

Set objConnection = CreateObject("ADODB.Connection")

Set objCommand = CreateObject("ADODB.Command")

Set objRecordset = createobject("ADODB.RecordSet")

objConnection.Open "Provider=SQLOLEDB;Data Source =" & strServer & ";" & _

"Trusted_Connection=Yes;Initial Catalog =SMS_" & strDatabase

objCommand.activeconnection = objConnection

objCommand.CommandText = "Select * From V_R_System"

Set objRecordset = objCommand.Execute

Do Until objRecordSet.EOF

objExcel.Cells(intRow, 1).Value = objRecordset("Name0")

objExcel.Cells(intRow, 2).Value = objRecordset("Client_Version0")

objExcel.Cells(intRow, 3).Value = objRecordset("User_Name0")

objExcel.Cells(intRow, 4).Value = objRecordset("User_Domain0")

objRecordSet.MoveNext

intRow = intRow + 1

Loop

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

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

Set objRange = objExcel.Range("A1")

objRange.Sort objRange,1,,,,,,1

objConnection.close

MsgBox "Done"