Vbs to excel macro.

Hi,

I have this vbs code that finds the Bios date of each machine in the txt file.
Now i want the same to work from within a excel file.
I have the machine names in colum "Q" instead of a txt file and want the results in colum "CW"

Regards
Sharath


on error resume next
 
Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")
Set objReadFile = fso.OpenTextFile("C:\machines.txt", 1)
Set objWriteFile = FSO.OpenTextFile("C:\bios-dates.txt", 2, True)
 
objWriteFile.WriteLine """Computer"",""WMI Release Date"",""Release Date"""
 
do while not objReadFile.AtEndOfStream
    strComputer = objReadFile.ReadLine
    Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colBIOS = objWMIService.ExecQuery _
    ("Select * from Win32_BIOS")
    For each objBIOS in colBIOS
        objWriteFile.WriteLine """" & strComputer & """,""" & objBIOS.ReleaseDate & """,""" & WMIDateStringToDate(Replace(objBIOS.ReleaseDate, "*", "0")) & """"
    Next
Loop
 
Set objFile = Nothing
Set fso = Nothing
 
 
Function WMIDateStringToDate(dtmInstallDate)
    WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) & " " & Mid (dtmInstallDate, 9, 2) & ":" & _
    Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, 13, 2))
End Function

Open in new window

LVL 11
bsharathAsked:
Who is Participating?
 
rejoinderConnect With a Mentor Commented:
Can you try this please...
Sub Check_Computer_Hardware()
    on error resume next
    Application.EnableEvents = False
    
    For lngRow = 2 To Cells(65536, "Q").End(xlUp).Row
        strComputer = Trim(Cells(lngRow, "Q").Value)
        If strComputer <> "" Then
            Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
            Set colBIOS = objWMIService.ExecQuery ("Select * from Win32_BIOS")
    
            For each objBIOS in colBIOS
                dtmInstallDate = Replace(objBIOS.ReleaseDate, "*", "0")
                WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) & " " & Mid (dtmInstallDate, 9, 2) & ":" & Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, 13, 2))
                Cells(lngRow, "CW").Value = WMIDateStringToDate
            Next
        End If
    Next
    
    Application.EnableEvents = True
End Sub

Open in new window

0
 
bsharathAuthor Commented:
Thank U worked perfect...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.