Don Hite at myITforum.com

VBS Script To Send Last Hardware Scan Date Information To Excel

This VBS Script will send last hardware scan information to Excel for your sites resources.

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 = "Resource ID"

objExcel.Cells(1, 3).Value = "Time Stamp"

objExcel.Cells(1, 4).Value = "Scan Date"

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, SD.ResourceID, WD.LastHWScan," & _ 

" Convert(VarChar(11), WD.LastHwScan, 109) CD " & _ 

" From v_R_System SD" & _

" Join v_Gs_Workstation_Status WD On SD.ResourceID = WD.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("ResourceID").Value

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

objExcel.Cells(intRow, 4).Value = objRecordSet.Fields("CD").Value

objRecordSet.MoveNext

intRow = intRow + 1

Loop

objExcel.Range("A1:D1").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"