none
VBScript to pull members of email group and export to Excel

    Question

  • hihi,

    I'm trying to work out how to pull members of an email distribution group using LDAP and export this data into Excel.

    This is what I've got this so far:
    Dim objGroup, objExcel, iRow, strUser, objOU
    Set objOU = GetObject("LDAP://group OU here")
    Set objExcel = CreateObject("Excel.Application")
    For Each objItem in objOU
      If Lcase(objItem.Class) = "group" Then
        Set objGroup = GetObject(objItem.AdsPath) 
    End if
     With objExcel
      .SheetsInNewWorkbook = 1
      .Workbooks.Add
      .Visible = True
      .Worksheets.Item(1).Name = mid(objGroup.Name, instr(1,objGroup.Name,"=") + 1 ) 'set Worksheet name to that of the DL
     irow=1
    
     For Each strUser in objGroup.Member
      Set objUser = GetObject("LDAP://" & strUser)
      .Cells(iRow,1) = objUser.CN
      irow=irow + 1
     Next
     .Columns(1).entirecolumn.autofit
    End With
    
    Set objGroup = Nothing
    next
    Set objExcel = Nothing
    It works, but every distribution group is generated in a new workbook, how do I make it so every group is generated in a separate worksheet of the one workbook?

    One of the group's name is longer than the 31 character limit of worksheet names and this stops the script in its tracks. Is there a way to tell the script to only use the first 31 characters of the group name as the worksheet name?

    thanks
    Friday, August 06, 2010 4:02 AM

Answers

  • use the worksheet.add() method to create a new worksheet.

    add this worksheet part of the loop and add items to it.

    for the name of the worksheet.

    use the worksheet.name properties to set the name you want to.

    $a=$b.worksheets.add()
    $a.name="ABCD"

    $c = $b.Worksheets.Item(1)

     

    thanks

    Thiyagu


    Thiyagu | MCTS/MCITP - Exchange 2007 | MCSE 2003[Messaging] | http://www.myExchangeWorld.com. This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, August 06, 2010 5:05 AM
    Moderator
  • .Worksheets.Item(1).Name = left(mid(objGroup.Name, instr(1,objGroup.Name,"=") + 1 ),31)
    Friday, August 06, 2010 9:33 AM

All replies

  • use the worksheet.add() method to create a new worksheet.

    add this worksheet part of the loop and add items to it.

    for the name of the worksheet.

    use the worksheet.name properties to set the name you want to.

    $a=$b.worksheets.add()
    $a.name="ABCD"

    $c = $b.Worksheets.Item(1)

     

    thanks

    Thiyagu


    Thiyagu | MCTS/MCITP - Exchange 2007 | MCSE 2003[Messaging] | http://www.myExchangeWorld.com. This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, August 06, 2010 5:05 AM
    Moderator
  • .Worksheets.Item(1).Name = left(mid(objGroup.Name, instr(1,objGroup.Name,"=") + 1 ),31)
    Friday, August 06, 2010 9:33 AM
  • Sorry it's taken me this long to reply, haven't had the time to revisit this until now.

     

    Must admit, this is straight over my head.  Not sure how/where ABCD fits in.

     

    Please try and dumb this down for me or tell me to look elsewhere ;)

    Tuesday, August 24, 2010 11:59 PM