How do I go to next sheet in Excel using vbscript?

Posted on 2007-10-20
Last Modified: 2008-01-09
I have a script where I list all Group members from specific OU in my AD into an Excel sheet.
Each group should be listed in separate sheet.

The script is almost done, the only problem is that I cant get it to go to the next sheet and add the members there... It names the next sheets correct, but it list all members in the first sheet, overwriting the members that have already been added.

Heres is my code.

'On Error Resume Next
Dim objGroup, objExcel, objMember, iRow, icol, strUser, strList
Set objGroup = GetObject("LDAP:// ou=, ou=, ou=,dc=domain,dc=dom")

Set objExcel = CreateObject("Excel.Application")

With objExcel
  .SheetsInNewWorkbook = 10
  .Visible = True
End With

icol = 1
For Each strList in objGroup
Set objMember = GetObject("LDAP://" & strList.Name & ", ou=, ou=, ou=,dc=domain,dc=dom")

With objExcel
  .Worksheets.item(icol).Name = mid(strList.Name, instr(1,strList.Name,"=") + 1 )
  icol=icol + 1  
  For Each strUser in objMember.Member
     Set objUser =  GetObject("LDAP://" & strUser)
     .Cells(iRow,1) = objUser.CN
     irow=irow + 1      
  End With

Set objMember = Nothing
Set objExcel = Nothing
Set objGroup = Nothing
Thanks in advance.

/Jan Mikkelsen
Question by:legoland
    LVL 59

    Expert Comment

    by:Chris Bottomley
    If you declare for example dim wks as worksheet

    then you can:

    With objExcel
      .Worksheets.item(icol).Name = mid(strList.Name, instr(1,strList.Name,"=") + 1 )
      set wks = worksheets(mid(strList.Name, instr(1,strList.Name,"=") + 1 )

    then use

    wks.Cells(iRow,1) = objUser.CN

    LVL 65

    Accepted Solution

    You could also just change this:
    .Cells(iRow,1) = objUser.CN

    so that it references a specific sheet, not just the Active sheet, which is does by default when you don't specify a different sheet.  So, try changing it to:
    .Worksheets.item(icol).Cells(iRow,1) = objUser.CN

    or perhaps that won't work, and will give an error, so instead of that, just put

    right above:
    .Cells(iRow,1) = objUser.CN



    Author Comment

    Chris. I could not get your solution to work. I tried setting Dim wks AS worksheet. That didnt work, also tried just setting Dim wks, but it kept giving me errors with type mismatch.

    Rob, your solution worked great, thanks.
    Solution accepted.

    LVL 65

    Expert Comment

    No worries, thanks legoland.



    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
    Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now