Link to home
Start Free TrialLog in
Avatar of legoland
legoland

asked on

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

Hello.
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
  .Workbooks.Add
  .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 )
 
  irow=1
  icol=icol + 1  
  For Each strUser in objMember.Member
     Set objUser =  GetObject("LDAP://" & strUser)
     .Cells(iRow,1) = objUser.CN
     irow=irow + 1      
     Next
   .Columns(1).entirecolumn.autofit
  End With
Next

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

/Jan Mikkelsen
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

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
wks.Columns(1).entirecolumn.autofit


Chris
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of legoland
legoland

ASKER

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.

 
No worries, thanks legoland.

Regards,

Rob.