?
Solved

Excel 2010:  Macro that pulls WMI Info

Posted on 2012-08-14
18
Medium Priority
?
3,224 Views
Last Modified: 2012-08-17
Hello Experts!

   I have a spreadsheet that contains NetBiosNames (Computer Names) in column A.  Row 1 is my Header Row:

           Column A                        Column B
1.     NetBiosName                     Username
2.   computername1                  johndoe1
3.   computername2                  johndoe2
4.   computername3                  johndoe3
5.   computername4                  johndoe4
6.   computername5                  johndoe5

I'd like a control button that I can click on, that will pull the username for each computer name entry in my spreadsheet if possible.  Any help with this macro is greatly appreciated.
0
Comment
Question by:itsmevic
  • 9
  • 4
  • 3
  • +1
18 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 38296061
what do need to do with the data?
0
 

Author Comment

by:itsmevic
ID: 38296208
I use the data in a report at work.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38296726
This code will iterate the cells.
Dim rngData As Range
Dim rngUser As Range
Set rngData = ActiveSheet.Range(ActiveSheet.Range("B2"),ActiveSheet.Range("B2").End(xlDown)
For Each rngUser In rngData
   Debug.Print rngUser.Value
Next

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:itsmevic
ID: 38296766
Hi Aikmark,

    Does this code actually pull the WMI information from each machine and produce a username associated to each machine?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38296784
No.  This code iterates the cell data you described in your question.
0
 

Author Comment

by:itsmevic
ID: 38296813
Oh, okay, so it doesn't actually go out to get anything, it's just simply code that will populate "something" in the range of cells mentioned, correct?  It doesn't actually go out to WMI and pull the username based off the computername (NetBiosName)?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38296902
>>It doesn't actually go out...
That is not the question you asked, or at least that is not how I read it.

Perhaps you can rephrase the question.
0
 

Author Comment

by:itsmevic
ID: 38297001
it's in the Subj line and last paragraph.  ( :
0
 

Author Comment

by:itsmevic
ID: 38297020
Question rephrased:

Two columns in excel i.e.

Column A                                          Column B
NETBIOSNAME(computername)        Username


Headers take up row 1 so data would start populating on Row 2.

Need a macro that will go out and search WMI for the username of each NetBiosName(ComputerName) entered in column A and populate that username value in Column B.

Paste in computer names in Column A.  Hit the Get User control button and it goes out and pulls and populates the username in Column B for each computername in Column A.

Hopefully that helps clarify things.
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 1000 total points
ID: 38297588
Do either of these get you moving in the right direction:

Sub Win32_Account_TestExcel()
Dim WmObj As Object, Test As Object
Dim Valeur As Object, Ws As Object

Dim strComputer As String
Dim objWMIService As Object, objQuickFix As Object, colQuickFixes As Object

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}")

Set Test = objWMIService.ExecQuery("Select * from Win32_Account")
For Each Valeur In Test
    Debug.Print ("Nom : " & Valeur.Name)
    Debug.Print ("Description : " & Valeur.Description)
    Debug.Print ("Domaines : " & Valeur.Domain)
    Debug.Print ("SID : " & Valeur.SID)
    Debug.Print ("------------------------------")
Next

End Sub

Open in new window


Sub GetUser()
    Dim colCompSys As Object
    Dim objComputer As Object
    Dim objWMI As Object
    Dim strComputer As String
    strComputer = "."
    
    Set objWMI = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colCompSys = objWMI.ExecQuery("SELECT UserName FROM Win32_ComputerSystem")
     
    For Each objComputer In colCompSys
        Debug.Print objComputer.UserName
    Next
End Sub

Open in new window

0
 

Author Comment

by:itsmevic
ID: 38298541
Hi there S. -  Will the code above pull the username from each computer populate the username in column B?
0
 
LVL 24

Expert Comment

by:Steve
ID: 38299441
I think the code above will pull the names off a local computer.
If you run them, the imediate window in the VBA editor will have some information in it.
If the information you require is in either of the two outputs let us know and then we can use it to generate the VBA to get your dsired result.
At the moment I am "fishing" hoping one of the sets of WMI code will return the data you need.
Though I think that the actual WMI to obtain usernames from remote PC will require authentication. I am on the case, but this is not something that is done often, so am having to refresh memory as I go. Hopefully we can just keep casting out and hopefully we will catch something, then can write the code to populate your list and land our fish :)
ATB,
Steve.
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 1000 total points
ID: 38303315
Hi, add a module with this code.  It will read the computer names from column A, starting at row 2, and write the logged on user to column B

Regards,

Rob.

Sub GetUser()
    Dim colCompSys As Object
    Dim objComputer As Object
    Dim objWMI As Object
    Dim strComputer As String
    strComputer = "."
    
    For intRow = 2 To ActiveSheet.Cells(65536, 1).End(xlUp).Row
        strComputer = ActiveSheet.Cells(intRow, 1).Value
        If Ping(strComputer) = True Then
            On Error Resume Next
            strUser = ""
            Set objWMI = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
            Set colCompSys = objWMI.ExecQuery("SELECT UserName FROM Win32_ComputerSystem")
            For Each objComputer In colCompSys
                strUser = objComputer.UserName
            Next
            If Err.Number = 0 Then
                ActiveSheet.Cells(intRow, 2).Value = strUser
            Else
                ActiveSheet.Cells(intRow, 2).Value = "ERROR " & Err.Number & ": " & Err.Description
            End If
            Err.Clear
            On Error GoTo 0
        Else
            ActiveSheet.Cells(intRow, 2).Value = "OFFLINE"
        End If
    Next
End Sub

Function Ping(strComputer)
    Dim objShell, boolCode
    Set objShell = CreateObject("WScript.Shell")
    boolCode = objShell.Run("Ping -n 1 -w 300 " & strComputer, 0, True)
    If boolCode = 0 Then
        Ping = True
    Else
        Ping = False
    End If
End Function

Open in new window

0
 

Author Comment

by:itsmevic
ID: 38303610
Rob, you're a friggin' coding BEAST!....LOL!  Great to hear from you again sir! Hope you are well my friend!
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 38303625
LOL!  I'm doing alright thanks.  I'm finding less time for EE right now, but I hope to be back in full swing soon.

Rob.
0
 

Author Closing Comment

by:itsmevic
ID: 38303668
That worked perfectly! Thank you everyone!
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 38303691
No problem.  Thanks for giving The_Barman an assist.  I forgot to mention that I swiped the second code block from his post.

Rob.
0
 

Author Comment

by:itsmevic
ID: 38304905
Both of you guys have always offered superb solutions and have taught me so much along the way.  Thank you!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 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