Solved

VBScript: Output to Excel Leaves Blank Rows The Spreadsheet.

Posted on 2010-11-10
2
477 Views
Last Modified: 2012-05-10
I'm searching Active Directory and eliminating unwanted accounts by filters. The problem is that everytime it encounters one of the records I don't want, it leaves a blank row on the spreadsheet. I could easily sort and remove all the blanks rows after the fact, but I'd rather have them not there in the first place.


On Error Resume Next



' Excel Spreadsheet Setup

Set Excel = CreateObject("Excel.Application")

Excel.Visible = True

Excel.Workbooks.Add



' Setup Column Headings

Excel.Cells(1, 1).Value = "User ID"

Excel.Cells(1, 2).Value = "Full Name"

Excel.Cells(1, 3).Value = "Description"

Excel.Cells(1, 4).Value = "Email Address"

Excel.Cells(1, 5).Value = "Employee Type"

'Excel.Cells(1, 9).Value = "OU Location"



' Setup Column Widths

Excel.Columns(1).ColumnWidth = 12

Excel.Columns(2).ColumnWidth = 20

Excel.Columns(3).ColumnWidth = 30

Excel.Columns(4).ColumnWidth = 30

Excel.Columns(5).ColumnWidth = 20

'Excel.Columns(9).ColumnWidth = 75



' Setup Spreadsheet Range and Top Row

Excel.Range("A1:E1").Select

Excel.Selection.Font.Bold = True

Excel.Selection.Interior.ColorIndex = 1 'Black

Excel.Selection.Interior.Pattern = 1 'xlSolid

Excel.Selection.Font.ColorIndex = 44 'Gold



' Set Range to Freeze Top Row

Excel.Range("A2").Select

Excel.Activewindow.FreezePanes = True



counter = 2 'init to second cell





' Reference Section

Set objCommand = CreateObject("ADODB.Command")

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Provider = "ADsDSOObject"

objConnection.Open "Active Directory Provider"

objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000



Set objNameExcludes = CreateObject("Scripting.Dictionary")

objNameExcludes.Add "1", 0

objNameExcludes.Add "2", 0

objNameExcludes.Add "3", 0

objNameExcludes.Add "4", 0

objNameExcludes.Add "5", 0

objNameExcludes.Add "6", 0

objNameExcludes.Add "7", 0

objNameExcludes.Add "8", 0

objNameExcludes.Add "9", 0

objNameExcludes.Add "0", 0

objNameExcludes.Add "_", 0



' Worker Section

strDomain = "ou=domain,dc=com"

strFilter = "(&(objectCategory=User)(cn=*))"

strAttributes = "DistinguishedName,Name"

strQuery = "<LDAP://" & strDomain & ">;" & strFilter & ";" & strAttributes & ";DistinguishedName,Name,GivenName,SN;Subtree"



objCommand.CommandText = strQuery

Set objRecordSet = objCommand.Execute



Do Until objRecordSet.EOF   

 

  strLocation = objRecordSet.Fields("DistinguishedName")

  Set objUser = GetObject("LDAP://mdc1/" & strLocation)   

  

  strOU = Split(strLocation,",")

  

  ' Retrieve userAccountControl value.

  lngFlag = objUser.userAccountControl

    

    ' This For/Next loop determines your OU

    For i = LBound(strOU) To UBound(strOU)

      If InStr(strOU(i), "_") Or InStr(strOU(i), "-") > 0 Then

        strOrgUnit = mid(strOU(i), 4)

      Else

      End If

    Next

    

'  '******   Remove Unwanted Service Accounts 

	bSkip = False					

         For Each strExclude In objNameExcludes

	   If InStr(LCase(objUser.sAMAccountname), LCase(strExclude)) > 0 Then

		bSkip = True					

                  Exit For

	    End If

	Next



	If bSkip = False Then 'add the record, otherwise if bSkip is true, this block will not be executed

							

          ' The next 2 lines determine the object's group membership

	 Set objUser = GetObject("LDAP://" & strLocation )    

	 objMemberOf = objUser.GetEx("MemberOf")

						    

	     Excel.Cells(Counter,1).Value = objUser.sAMAccountname

	     Excel.Cells(Counter,2).Value = objUser.DisplayName

	     Excel.Cells(Counter,3).Value = objUser.Description

	     Excel.Cells(Counter,4).Value = objUser.Mail

	     Excel.Cells(Counter,5).Value = objUser.EmployeeType



          End If

		   

	  objRecordSet.MoveNext

	  Counter = Counter + 1  

Loop



objConnection.Close

Open in new window

0
Comment
Question by:JB4375
2 Comments
 
LVL 2

Accepted Solution

by:
jeffmowens earned 500 total points
ID: 34107060
:)  Appears to be a minor syntax matter.

Move your Counter increment on line 110 inside (BEFORE) the End If on line 107.
0
 
LVL 1

Author Closing Comment

by:JB4375
ID: 34107115
LOL.
These loops always give me so much grief.
Thanks!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

26 Experts available now in Live!

Get 1:1 Help Now