Thursday, December 14, 2017

excel vba script to capture the user names who ever logged in a list of servers (infrastructure management)

'' Excel vba script which connects to a server and get the usernames who's ever logged into this machine

Option Private Module
Option Explicit

Public fso As New Scripting.FileSystemObject
Public fl As Folder
Public subFl As Folders
Public fPath As String
Public sCount As Long
Public mSht As Worksheet
Public rSht As Worksheet
Public lRow As Long
Public i As Long

Sub check_LoginDetails()

Set mSht = ThisWorkbook.Sheets("Sever_IPAddress")
Set rSht = ThisWorkbook.Sheets("Result")

sCount = 2
lRow = mSht.Range("A1048576").End(xlUp).Row
rSht.Rows("2:1048576").ClearContents

For i = 2 To lRow
    Call Return_UserName(mSht.Cells(i, 1))
Next
rSht.Activate

Set fso = Nothing
Set mSht = Nothing
Set rSht = Nothing

MsgBox "done"
End Sub

Sub Return_UserName(strComputer As String)
    On Error GoTo err1
 
    fPath = "\\" & strComputer & "\c$\users\"

    Set fl = fso.GetFolder(fPath)
    Set subFl = fl.SubFolders
 
    For Each fl In subFl
        rSht.Cells(sCount, 1) = strComputer
        rSht.Cells(sCount, 2) = fl.Name
        rSht.Cells(sCount, 3) = fl.DateLastModified
        rSht.Cells(sCount, 4) = fl.DateLastAccessed
        sCount = sCount + 1
    Next
 
err1:
If Err.Number <> 0 Then
    rSht.Cells(sCount, 1) = strComputer
    rSht.Cells(sCount, 2) = "No Data"
    sCount = sCount + 1
End If
End Sub

No comments:

Post a Comment

Excel VBA to send email from excel with HTML table in a email body

'' Excel vba script to send email from outlook & email body in HTML table format. ''Write down email body in HTML tags...