Don Hite at myITforum.com

VBS Script To Send Clients Assigned And Installed Site Codes To Excel

This VBS Script will send your resources assigned and installed site codes to Excel.

VBS Script:

strServer = InputBox ("Enter Site 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 = "Assigned Site"

objExcel.Cells(1, 3).Value = "Installed Site"

Const adOpenStatic = 3

Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")

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

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

Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.Open _

" Select SD.Name0, A.SMS_Assigned_Sites0, I.SMS_Installed_Sites0" & _ 

" From v_R_System SD" & _

" Join v_RA_System_SMSAssignedSites A On SD.ResourceID = A.ResourceID" & _

" Join v_RA_System_SMSInstalledSites I On SD.ResourceID = I.ResourceID" _

, objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

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

objExcel.Cells(intRow, 2).Value = objRecordSet.Fields("SMS_Assigned_Sites0").Value

objExcel.Cells(intRow, 3).Value = objRecordSet.Fields("SMS_Installed_Sites0").Value

objRecordSet.MoveNext

intRow = intRow + 1

Loop

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

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

MsgBox "Done"