• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

Add null value for empty fileds using vbs

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
leche671
Asked:
leche671
  • 3
  • 2
1 Solution
 
sirbountyCommented:
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
 
leche671Author Commented:
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
 
sirbountyCommented:
It shouldn't at all...
Did you copy/paste the exact code above?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
leche671Author Commented:
You're right. I fixed it and ran it again. Now omits the groups that have no members.
0
 
leche671Author Commented:
Any idea why it's omitting the groups without members?
0
 
RobSampsonCommented:
>> 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now