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

leche671Asked:
Who is Participating?
 
sirbountyConnect With a Mentor Commented:
It shouldn't at all...
Did you copy/paste the exact code above?
0
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.