Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3333
  • Last Modified:

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
0
legoland
Asked:
legoland
  • 2
1 Solution
 
Chris BottomleyCommented:
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
0
 
RobSampsonCommented:
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
.Worksheets.item(icol).Activate

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

Regards,

Rob.
0
 
legolandAuthor Commented:
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.

 
0
 
RobSampsonCommented:
No worries, thanks legoland.

Regards,

Rob.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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