1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

help

Discussion in 'Scripting & Programming' started by aertaher, May 20, 2009.

  1. aertaher

    aertaher New Member

    2
    0
    1
    can anyone tell me what's wrong with this script, i'm treying to get it to report a hrdware inventory and dumb it in a xcl sheet but it's does not.


    Code:
    On Error Resume Next
    
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
        & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
    
    Set colItems = objWMIService.ExecQuery("Select * from Win32_PnPSignedDriver")
    
    Set objExcel = CreateObject("Excel.Application")
    
    objExcel.Visible = True
    
    objExcel.Workbooks.Add
    
    Set objWorkbook = objExcel.Workbooks.Add()
    
    Set objWorksheet = objWorkbook.Worksheets(1)
    
    intRow = 2
    
    
    For Each objItem in colItems
        Wscript.Echo "Class Guid: " & objItem.ClassGuid
        Wscript.Echo "Compatability ID: " & objItem.CompatID
        Wscript.Echo "Description: " & objItem.Description
        Wscript.Echo "Device Class: " & objItem.DeviceClass
        Wscript.Echo "Device ID: " & objItem.DeviceID
        Wscript.Echo "Device Name: " & objItem.DeviceName
        dtmWMIDate = objItem.DriverDate
        strReturn = WMIDateStringToDate(dtmWMIDate)
        Wscript.Echo "Driver Date: " & strReturn
        Wscript.Echo "Driver Provider Name: " & objItem.DriverProviderName
        Wscript.Echo "Driver Version: " & objItem.DriverVersion
        Wscript.Echo "Hardware ID: " & objItem.HardWareID
        Wscript.Echo "INF Name: " & objItem.InfName
        Wscript.Echo "Is Signed: " & objItem.IsSigned
        Wscript.Echo "Manufacturer: " & objItem.Manufacturer
        Wscript.Echo "PDO: " & objItem.PDO
        Wscript.Echo "Signer: " & objItem.Signer
        Wscript.Echo
    Next
    
    Wscript.Echo "Complete"
    
    objWorkbook.SaveAs "DriveLetter\FolderName\FileName.xls"
    
    objExcel.Quit
    
     
    Function WMIDateStringToDate(dtmWMIDate)
        If Not IsNull(dtmWMIDate) Then
        WMIDateStringToDate = CDate(Mid(dtmWMIDate, 5, 2) & "/" & _
             Mid(dtmWMIDate, 7, 2) & "/" & Left(dtmWMIDate, 4) _
                 & " " & Mid (dtmWMIDate, 9, 2) & ":" & _
                     Mid(dtmWMIDate, 11, 2) & ":" & Mid(dtmWMIDate,13, 2))
        End If
    End Function



    Moved to correct forum - AJ
     
  2. LordMoolyBap

    LordMoolyBap Nibble Poster

    95
    0
    28
    you had the error catcher on resume next so it would just skip over problems and you just had it set up to return msgboxes.

    i don't know what the exact vbscript is as haven't controlled excel from vbscript before but the vba equivalent (vb behind excel) would be something like this:

    Code:
    Sub exportontosheet()
    
    
    'On Error Resume Next - remove this. you need to know where if its going wrong.
    
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
    
    'getting the collection of items
    Set colItems = objWMIService.ExecQuery("Select * from Win32_PnPSignedDriver")
    
    'set a variable and assign it to 2 so the export starts on the second row
    introw = 2
    
    'set up some headers for each row to make it tidier
    Range("a1").Select
    ActiveCell.FormulaR1C1 = "Class Guid"
    Range("b2").Select
    ActiveCell.FormulaR1C1 = "Compatability ID"
    Range("c2").Select
    ActiveCell.FormulaR1C1 = "Description ID"
    Range("d2").Select
    ActiveCell.FormulaR1C1 = "Device Class"
    Range("e2").Select
    ActiveCell.FormulaR1C1 = "Device ID"
    
    'and so on... making the headers for each column you can do the rest.
    
    For Each objItem In colItems
    
    
        Range("A" & introw).Select
        ActiveCell.FormulaR1C1 = objItem.ClassGuid
        Range("B" & introw).Select
        ActiveCell.FormulaR1C1 = objItem.CompatID
        Range("C" & introw).Select
        ActiveCell.FormulaR1C1 = objItem.Description
        Range("D" & introw).Select
        ActiveCell.FormulaR1C1 = objItem.DeviceClass
        Range("E" & introw).Select
        ActiveCell.FormulaR1C1 = objItem.DeviceID
        
        'and so on....
        
        'increment the row down to the next one
        introw = introw + 1
    
    Next
    
    'feedback to say when it is complete
    MsgBox ("Complete")
    
    
    
    
    End Sub
    'your other function
    Code:
    Function WMIDateStringToDate(dtmWMIDate)
    [INDENT]If Not IsNull(dtmWMIDate) Then
    [INDENT]WMIDateStringToDate = CDate(Mid(dtmWMIDate, 5, 2) & "/" & _
    Mid(dtmWMIDate, 7, 2) & "/" & Left(dtmWMIDate, 4) _
    & " " & Mid(dtmWMIDate, 9, 2) & ":" & _
    Mid(dtmWMIDate, 11, 2) & ":" & Mid(dtmWMIDate, 13, 2))[/INDENT]
    End If[/INDENT]
    End Function

    I haven't done every column as you can do that yourself I think. to get into the vba just press alt + F11 in Excel and then type this into either a new code module or the "this workbook" project. If you run it it will work as I have just tried it.

    see how you get on.8)

    dom
     
    Certifications: HND (Comp) MBCS
    WIP: Msc Intelligent Systems

Share This Page

Loading...