Solved

Vbs to excel macro.

Posted on 2009-04-02
2
274 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:bsharath
2 Comments
 
LVL 14

Accepted Solution

by:
rejoinder earned 500 total points
Comment Utility
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
 
LVL 11

Author Comment

by:bsharath
Comment Utility
Thank U worked perfect...
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now