EDN Admin
Well-known member
Hi guys,
I have written this code but when I open the Excel Doc, the spreadsheet is blank apart from the column names. Any idea why because when I tested using "Wscript.Echo " I could see the content in the fields.
<pre lang="x-vbnet ======================================================================================================
Script to Find dormant accounts in the WFC Users00 Container
And write to text
Authors: Roddaz Version 1.3
Browse AD and determine when the users last logged in
the users are in OU=Users00,OU=Users,OU=WFC,OU=Europe,OU=Texaco,DC=ct,DC=texaco,DC=net
create an excel spreadsheet flagging up the user accounts that havent been accessed in 90 days
======================================================================================================
Option Explicit statement forces the explicit declaration of all variables using the Dim, Private, Public, or ReDim statements
On Error Resume Next to stop runtime errors stopping the program execution
Option Explicit
On Error Resume Next
==========================DECLARE VARIABLES=========================================================
Dim objLogon
Dim colItems
Dim intLogonTime
Dim strExcelPath
Dim objExcel
Dim objWorksheet
Dim k
Dim objRange
Dim objRange2
Dim objItem
=========================SET Variables==============================================================
Spreadsheet file to be created.
strExcelPath = "P:VB ScriptsVBSProjectUserReport.xls"
==================Analysing Last log on times =============================================================
Set colItems = GetObject("LDAP://OU=Users00,OU=Users,OU=WFC,OU=Europe,OU=Texaco,DC=ct,DC=texaco,DC=net")
This line filters all objects in that User OU to only get Users
colItems.Filter = Array("User")
We now loop each user found and set k=2 to enter content starting from the second row
k=2
For Each objItem in colItems
We get the current users LastLogon timestamp (is a raw integer8 value)
set objLogon = objItem.Get("lastLogon")
As this attribute holds a Large Integer value (64bit) we need to break it into 2 32 bit values for ease
intLogonTime = objLogon.HighPart * (2^32) + objLogon.LowPart
Now we convert the value into a proper date value
intLogonTime = intLogonTime / (60 * 10000000)
intLogonTime = intLogonTime / 1440 + #1/1/1601#
Calculate the difference in days between lastlogon time and present date (when script is run)
iDay =DateDiff("d", Now, intLogonTime)
If iday <= -90 Then
objWorksheet.Cells(k, 1).Value = objItem.CN
objWorksheet.Cells(k, 2).Value = objItem.distinguishedName
objWorksheet.Cells(k, 3).Value = intLogonTime
objWorksheet.Cells(k, 4).Value = objItem.description
objWorksheet.Cells(k, 5).Value = "Account Dormant more than 90 days"
Otherwise the user has never logged on - this is outputted in a message box / command prompt (if script run in CMD.exe)
Else If intLogonTime = #1/1/1601# Then
objWorksheet.Cells(k, 1).Value = objItem.CN
objWorksheet.Cells(k, 2).Value = objItem.distinguishedName
objWorksheet.Cells(k, 3).Value = "No Login date found"
objWorksheet.Cells(k, 4).Value = "User has never logged on"
objWorksheet.Cells(k, 5).Value = "Account Inactive"
End If
End If
k= k +1
This just goes round the beginning of the loop if there are still more users to work on
Next
========================Set up to Output to Excel Spreadsheet==========================================================
Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If Err.Number <> 0 Then
On Error GoTo 0
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0
Create a new workbook.
objExcel.Workbooks.Add
Bind to worksheet.
Set objWorksheet = objExcel.ActiveWorkbook.Worksheets(1)
objWorksheet.Name = "FlaggedUserAccounts"
objWorksheet.Cells(1, 1).Value = "User Name"
objWorksheet.Cells(1, 2).Value = "Distinguished Name"
objWorksheet.Cells(1, 3).Value = "Last Login"
objWorksheet.Cells(1, 4).Value = "Description"
objWorksheet.Cells(1, 5).Value = "Status"
=====================Format the Spreadsheet===================================================
Format the spreadsheet.
objWorksheet.UsedRange.EntireColumn.Autofit()
objWorksheet.Range("A1:C1").Font.Bold = True
Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A5") Sort by Status
objRange.Sort objRange2, xlAscending, , , , , , xlYes
========================Save spreadsheet and close the workbook
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
WScript.Echo "All done"
Quit Excel.
objExcel.Application.Quit
Clean Up
Set objWorksheet = Nothing
Set objExcel = Nothing[/code]
I am still learning vbs as I go along.
Kind regards and thanks in advance for your assistance,
Roddaz
View the full article
I have written this code but when I open the Excel Doc, the spreadsheet is blank apart from the column names. Any idea why because when I tested using "Wscript.Echo " I could see the content in the fields.
<pre lang="x-vbnet ======================================================================================================
Script to Find dormant accounts in the WFC Users00 Container
And write to text
Authors: Roddaz Version 1.3
Browse AD and determine when the users last logged in
the users are in OU=Users00,OU=Users,OU=WFC,OU=Europe,OU=Texaco,DC=ct,DC=texaco,DC=net
create an excel spreadsheet flagging up the user accounts that havent been accessed in 90 days
======================================================================================================
Option Explicit statement forces the explicit declaration of all variables using the Dim, Private, Public, or ReDim statements
On Error Resume Next to stop runtime errors stopping the program execution
Option Explicit
On Error Resume Next
==========================DECLARE VARIABLES=========================================================
Dim objLogon
Dim colItems
Dim intLogonTime
Dim strExcelPath
Dim objExcel
Dim objWorksheet
Dim k
Dim objRange
Dim objRange2
Dim objItem
=========================SET Variables==============================================================
Spreadsheet file to be created.
strExcelPath = "P:VB ScriptsVBSProjectUserReport.xls"
==================Analysing Last log on times =============================================================
Set colItems = GetObject("LDAP://OU=Users00,OU=Users,OU=WFC,OU=Europe,OU=Texaco,DC=ct,DC=texaco,DC=net")
This line filters all objects in that User OU to only get Users
colItems.Filter = Array("User")
We now loop each user found and set k=2 to enter content starting from the second row
k=2
For Each objItem in colItems
We get the current users LastLogon timestamp (is a raw integer8 value)
set objLogon = objItem.Get("lastLogon")
As this attribute holds a Large Integer value (64bit) we need to break it into 2 32 bit values for ease
intLogonTime = objLogon.HighPart * (2^32) + objLogon.LowPart
Now we convert the value into a proper date value
intLogonTime = intLogonTime / (60 * 10000000)
intLogonTime = intLogonTime / 1440 + #1/1/1601#
Calculate the difference in days between lastlogon time and present date (when script is run)
iDay =DateDiff("d", Now, intLogonTime)
If iday <= -90 Then
objWorksheet.Cells(k, 1).Value = objItem.CN
objWorksheet.Cells(k, 2).Value = objItem.distinguishedName
objWorksheet.Cells(k, 3).Value = intLogonTime
objWorksheet.Cells(k, 4).Value = objItem.description
objWorksheet.Cells(k, 5).Value = "Account Dormant more than 90 days"
Otherwise the user has never logged on - this is outputted in a message box / command prompt (if script run in CMD.exe)
Else If intLogonTime = #1/1/1601# Then
objWorksheet.Cells(k, 1).Value = objItem.CN
objWorksheet.Cells(k, 2).Value = objItem.distinguishedName
objWorksheet.Cells(k, 3).Value = "No Login date found"
objWorksheet.Cells(k, 4).Value = "User has never logged on"
objWorksheet.Cells(k, 5).Value = "Account Inactive"
End If
End If
k= k +1
This just goes round the beginning of the loop if there are still more users to work on
Next
========================Set up to Output to Excel Spreadsheet==========================================================
Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If Err.Number <> 0 Then
On Error GoTo 0
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0
Create a new workbook.
objExcel.Workbooks.Add
Bind to worksheet.
Set objWorksheet = objExcel.ActiveWorkbook.Worksheets(1)
objWorksheet.Name = "FlaggedUserAccounts"
objWorksheet.Cells(1, 1).Value = "User Name"
objWorksheet.Cells(1, 2).Value = "Distinguished Name"
objWorksheet.Cells(1, 3).Value = "Last Login"
objWorksheet.Cells(1, 4).Value = "Description"
objWorksheet.Cells(1, 5).Value = "Status"
=====================Format the Spreadsheet===================================================
Format the spreadsheet.
objWorksheet.UsedRange.EntireColumn.Autofit()
objWorksheet.Range("A1:C1").Font.Bold = True
Const xlAscending = 1
Const xlDescending = 2
Const xlYes = 1
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A5") Sort by Status
objRange.Sort objRange2, xlAscending, , , , , , xlYes
========================Save spreadsheet and close the workbook
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close
WScript.Echo "All done"
Quit Excel.
objExcel.Application.Quit
Clean Up
Set objWorksheet = Nothing
Set objExcel = Nothing[/code]
I am still learning vbs as I go along.
Kind regards and thanks in advance for your assistance,
Roddaz
View the full article