Solved

VBScript: Output to Excel Leaves Blank Rows The Spreadsheet.

Posted on 2010-11-10
2
481 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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