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.Applic ation")
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).Nam e = 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.a utofit
End With
Next
Set objMember = Nothing
Set objExcel = Nothing
Set objGroup = Nothing
Thanks in advance.
/Jan Mikkelsen
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.Applic
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).Nam
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.a
End With
Next
Set objMember = Nothing
Set objExcel = Nothing
Set objGroup = Nothing
Thanks in advance.
/Jan Mikkelsen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Rob, your solution worked great, thanks.
Solution accepted.
No worries, thanks legoland.
Regards,
Rob.
Regards,
Rob.
then you can:
With objExcel
.Worksheets.item(icol).Nam
set wks = worksheets(mid(strList.Nam
then use
wks.Cells(iRow,1) = objUser.CN
wks.Columns(1).entirecolum
Chris