Solved

Add null value for empty fileds using vbs

Posted on 2009-03-30
6
398 Views
Last Modified: 2013-11-25
I have a script that queries Active Directory groups in an OU and outputs it's members into an excel. The problem is when a group has no members it doesn't insert a blank cell in excel.
On Error Resume Next

Const ADS_SCOPE_SUBTREE = 5
 

Dim objGroup, objExcel, iRow, strUser

'Set objGroup = GetObject("LDAP://cn="*",ou=DNP,ou=Groups,ou=Users and Groups, ou=Org,dc=xxx,dc=xxx,dc=xxx")

strGroup="*"
 

Set objConnection = CreateObject("ADODB.Connection")

Set objCommand =   CreateObject("ADODB.Command")

objConnection.Provider = "ADsDSOObject"

objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection
 

objCommand.Properties("Page Size") = 1000

objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 

objCommand.CommandText = _

    "SELECT Name, adSPath FROM 'LDAP://ou=DNP,ou=Groups,ou=Users and Groups, ou=xxx,dc=xxx,dc=xxx,dc=xxx' WHERE objectCategory='group' " & _

        "AND Name='" & strGroup & "'"
 

Set objRecordSet = objCommand.Execute
 

Set objExcel = CreateObject("Excel.Application")

With objExcel

  .SheetsInNewWorkbook = 1

  .Workbooks.Add

  .Visible = True

 .Worksheets.Item(1).Name = objRecordSet.Fields("Name").Value

  irow=1
 

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

  Set objGroup = GetObject(objRecordSet.Fields("adsPath").Value)

  For Each strUser in objGroup.Member

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

    .Cells(iRow,1) = objUser.CN

    .Cells(iRow,2) = objUser.description

    .Cells(iRow,3) = objRecordSet.Fields("Name").Value

    irow=irow + 1

  Next

  set objGroup=Nothing

  objRecordSet.MoveNext

Loop
 

 .Columns(1).entirecolumn.autofit

End With
 

Set objExcel = Nothing

Set objGroup = Nothing

Open in new window

0
Comment
Question by:leche671
  • 3
  • 2
6 Comments
 
LVL 67

Expert Comment

by:sirbounty
Comment Utility
You could check the membership count first...
On Error Resume Next

Const ADS_SCOPE_SUBTREE = 5

 

Dim objGroup, objExcel, iRow, strUser

'Set objGroup = GetObject("LDAP://cn="*",ou=DNP,ou=Groups,ou=Users and Groups, ou=Org,dc=xxx,dc=xxx,dc=xxx")

strGroup="*"

 

Set objConnection = CreateObject("ADODB.Connection")

Set objCommand =   CreateObject("ADODB.Command")

objConnection.Provider = "ADsDSOObject"

objConnection.Open "Active Directory Provider"

Set objCommand.ActiveConnection = objConnection

 

objCommand.Properties("Page Size") = 1000

objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

 

objCommand.CommandText = _

    "SELECT Name, adSPath FROM 'LDAP://ou=DNP,ou=Groups,ou=Users and Groups, ou=xxx,dc=xxx,dc=xxx,dc=xxx' WHERE objectCategory='group' " & _

        "AND Name='" & strGroup & "'"

 

Set objRecordSet = objCommand.Execute

 

Set objExcel = CreateObject("Excel.Application")

With objExcel

  .SheetsInNewWorkbook = 1

  .Workbooks.Add

  .Visible = True

 .Worksheets.Item(1).Name = objRecordSet.Fields("Name").Value

  irow=1

 

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

  Set objGroup = GetObject(objRecordSet.Fields("adsPath").Value)
 

  If ubound(objGroup.Member) = 0 Then

    .Cells(iRow,1) = "No membership found"

  Else

  For Each strUser in objGroup.Member

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

    .Cells(iRow,1) = objUser.CN

    .Cells(iRow,2) = objUser.description

    .Cells(iRow,3) = objRecordSet.Fields("Name").Value

    irow=irow + 1

  Next

  End If

  set objGroup=Nothing

  objRecordSet.MoveNext

Loop

 

 .Columns(1).entirecolumn.autofit

End With

 

Set objExcel = Nothing

Set objGroup = Nothing

Open in new window

0
 

Author Comment

by:leche671
Comment Utility
I really have zero experience with vbs scripts just a bunch of copy and pasting. I now receive an error.
Line: 48
Char: 1
Error: "loop" without 'do'

I don't know why the added code would do that. It ran fine before. Any ideas.
0
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
Comment Utility
It shouldn't at all...
Did you copy/paste the exact code above?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:leche671
Comment Utility
You're right. I fixed it and ran it again. Now omits the groups that have no members.
0
 

Author Comment

by:leche671
Comment Utility
Any idea why it's omitting the groups without members?
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
>> Any idea why it's omitting the groups without members?

It's doing that because iRow is not being incremented when no members are found.

Add
    irow=irow + 1

under this line
    .Cells(iRow,1) = "No membership found"


Regards,

Rob.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 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

19 Experts available now in Live!

Get 1:1 Help Now