Don Hite at myITforum.com

VBS Script To Send Last Hardware Scan Date Info To Excel

This VBS Script will send the following information for a specifies site server to excel: The Machine Name and whether it is Obsolete or Active and its last Hardware Scan Date.

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 = "Obsolete"

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

objExcel.Cells(1, 4).Value = "Hardware 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.Obsolete0, SD.Active0," & _ 

" Convert(VarChar(11), WS.LastHwScan, 109)Date" & _ 

" From v_R_System SD" & _

" Join v_GS_WORKSTATION_STATUS WS On SD.ResourceID = WS.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("Obsolete0").Value

If objExcel.Cells(intRow, 2).Value = "1" Then

objExcel.Cells(intRow, 2).Value = "Yes"

Else

objExcel.Cells(intRow, 2).Value = "No"

End If

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

If objExcel.Cells(intRow, 3).Value = "1" Then

objExcel.Cells(intRow, 3).Value = "Yes"

Else

objExcel.Cells(intRow, 3).Value = "No"

End If

objExcel.Cells(intRow, 4).Value = objRecordSet.Fields("Date").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"