Write output to Excel Spreadsheet

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
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
 
Back
Top