Collect Users Info In Group

Anything VBScript-related, including Windows Script Host, WMI, ADSI, and more.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
rs6000101
Posts: 6
Joined: Tue Jun 19, 2012 7:54 am

Collect Users Info In Group

Post by rs6000101 » Fri Jun 22, 2012 12:28 am

Good morning question I'm new to VB scripting i've been doing CRON scripting to long, anyways; I found a VB-Script for gathering users and there information from a group. I was wondering if I could get some assistance on making a few changes.1: once the script is executed I'd like for it to ask for the group name.2: I'd like for the file to be saved in the parent folder without having to put in a hard path like "C:" this way I don't have to worry about remembering to change the path if I copy it to another machine.
; Anyways here's the script hope that someone can assist;VB Code Start Block *****************************************************************
Option Explicit Dim objExcel : Set objExcel = CreateObject("Excel.Application") With objExcel .Visible = True .DisplayAlerts = False Dim objWorkBook : Set objWorkBook = .WorkBooks.Add Dim objSheet : Set objSheet = .Worksheets(1) End With Create_Excel_Header() Dim intRow : intRow = 2 ''Enter the group name''Dim strGroup : strGroup = "VPN_USERS" Dim objGroup : Set objGroup = GetObject("LDAP://" & GetDN(strGroup)) Enum_Members(objGroup) Format_It() Save_It() Sub Create_Excel_Header() With objSheet .Name = "Data" .Cells(1, 1).Value = "User ID" .Cells(1, 2).Value = "First Name" .Cells(1, 3).Value = "Initials" .Cells(1, 4).Value = "Last Name" .Cells(1, 5).Value = "E-Mail" .Cells(1, 6).Value = "Office Location" .Cells(1, 7).Value = "Description" .Cells(1, 8).Value = "Job Title" .Cells(1, 9).Value = "Department" .Cells(1, 10).Value = "Manager" .Cells(1, 11).Value = "IP Phone" .Cells(1, 12).Value = "Mobile Phone" .Cells(1, 13).Value = "Pager Number" .Cells(1, 14).Value = "Fax Number" .Cells(1, 15).Value = "Home Phone"
End With Dim objRange : Set objRange = objSheet.UsedRange objRange.Select With objRange .Font.Bold = True .Font.Name = "Arial" .Font.Size = 10 .WrapText = False .HorizontalAlignment = -4108 .Interior.ColorIndex = 37 .Cells.RowHeight = 25 End With End Sub Sub Format_It() Const xlEdgeBottom = 9 Const xlEdgeLeft = 7 Const xlEdgeRight = 10 Const xlEdgeTop = 8 Const xlInsideHorizontal = 12 Const xlInsideVertical = 11 Const xlContinuous = 1 Const xlAutomatic = -4105 Const xlMedium = -4138 Dim objRange : Set objRange = objSheet.UsedRange objRange.Select objRange.Columns.AutoFit Dim arrBorders : arrBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal) Dim intBorder For Each intBorder in arrBorders With objRange.Borders(intBorder) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Next End Sub Sub Save_It() objExcel.ActiveWorkBook.SaveAs("C:0000000000000000000MTA_Collect_Individual_Users_In_Group_Information.xlsx") objExcel.Quit End Sub Sub Enum_Members(group) Dim arrAttributes : arrAttributes = Array("samaccountName", "GivenName", "Initials", "sn", "Mail", "physicaldeliveryofficename", "Description", "Title", "Department", "Manager", "IPPhone", "Mobile", "Pager", "facsimileTelephoneNumber", "homePhone") Dim objItem For Each objItem in group.Members If objItem.Class = "user" Then Dim intColumn : intColumn = 1 Dim objUser : Set objUser = GetObject(objItem.AdsPath) Dim strAttrib For Each strAttrib in arrAttributes On Error Resume Next objSheet.Cells(intRow, intColumn).Value = objItem.Get(strAttrib) On Error GoTo 0 intColumn = intColumn + 1 Next End If intRow = intRow + 1 Next For Each objItem in group.Members If objItem.Class = "group" Then Call Enum_Members(objItem) End If Next End Sub Function GetDN(samAccount) If Not IsObject(objWSHNetwork) Then Dim objWSHNetwork : Set objWSHNetwork = WScript.CreateObject("WScript.Network") End If Dim NT : Set NT= CreateObject("NameTranslate") NT.Init 3, "" NT.Set 3, objWSHNetwork.UserDomain & "" & samAccount GetDN = NT.Get(1) End Function;VB Code End Block*****************************************************************

User avatar
rs6000101
Posts: 6
Joined: Tue Jun 19, 2012 7:54 am

Collect Users Info In Group

Post by rs6000101 » Fri Jun 22, 2012 12:28 am

Good morning question I'm new to VB scripting i've been doing CRON scripting to long, anyways; I found a VB-Script for gathering users and there information from a group. I was wondering if I could get some assistance on making a few changes.1: once the script is executed I'd like for it to ask for the group name.2: I'd like for the file to be saved in the parent folder without having to put in a hard path like "C:" this way I don't have to worry about remembering to change the path if I copy it to another machine.
; Anyways here's the script hope that someone can assist;VB Code Start Block *****************************************************************
Option Explicit Dim objExcel : Set objExcel = CreateObject("Excel.Application") With objExcel .Visible = True .DisplayAlerts = False Dim objWorkBook : Set objWorkBook = .WorkBooks.Add Dim objSheet : Set objSheet = .Worksheets(1) End With Create_Excel_Header() Dim intRow : intRow = 2 ''Enter the group name''Dim strGroup : strGroup = "VPN_USERS" Dim objGroup : Set objGroup = GetObject("LDAP://" & GetDN(strGroup)) Enum_Members(objGroup) Format_It() Save_It() Sub Create_Excel_Header() With objSheet .Name = "Data" .Cells(1, 1).Value = "User ID" .Cells(1, 2).Value = "First Name" .Cells(1, 3).Value = "Initials" .Cells(1, 4).Value = "Last Name" .Cells(1, 5).Value = "E-Mail" .Cells(1, 6).Value = "Office Location" .Cells(1, 7).Value = "Description" .Cells(1, 8).Value = "Job Title" .Cells(1, 9).Value = "Department" .Cells(1, 10).Value = "Manager" .Cells(1, 11).Value = "IP Phone" .Cells(1, 12).Value = "Mobile Phone" .Cells(1, 13).Value = "Pager Number" .Cells(1, 14).Value = "Fax Number" .Cells(1, 15).Value = "Home Phone"
End With Dim objRange : Set objRange = objSheet.UsedRange objRange.Select With objRange .Font.Bold = True .Font.Name = "Arial" .Font.Size = 10 .WrapText = False .HorizontalAlignment = -4108 .Interior.ColorIndex = 37 .Cells.RowHeight = 25 End With End Sub Sub Format_It() Const xlEdgeBottom = 9 Const xlEdgeLeft = 7 Const xlEdgeRight = 10 Const xlEdgeTop = 8 Const xlInsideHorizontal = 12 Const xlInsideVertical = 11 Const xlContinuous = 1 Const xlAutomatic = -4105 Const xlMedium = -4138 Dim objRange : Set objRange = objSheet.UsedRange objRange.Select objRange.Columns.AutoFit Dim arrBorders : arrBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal) Dim intBorder For Each intBorder in arrBorders With objRange.Borders(intBorder) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With Next End Sub Sub Save_It() objExcel.ActiveWorkBook.SaveAs("C:0000000000000000000MTA_Collect_Individual_Users_In_Group_Information.xlsx") objExcel.Quit End Sub Sub Enum_Members(group) Dim arrAttributes : arrAttributes = Array("samaccountName", "GivenName", "Initials", "sn", "Mail", "physicaldeliveryofficename", "Description", "Title", "Department", "Manager", "IPPhone", "Mobile", "Pager", "facsimileTelephoneNumber", "homePhone") Dim objItem For Each objItem in group.Members If objItem.Class = "user" Then Dim intColumn : intColumn = 1 Dim objUser : Set objUser = GetObject(objItem.AdsPath) Dim strAttrib For Each strAttrib in arrAttributes On Error Resume Next objSheet.Cells(intRow, intColumn).Value = objItem.Get(strAttrib) On Error GoTo 0 intColumn = intColumn + 1 Next End If intRow = intRow + 1 Next For Each objItem in group.Members If objItem.Class = "group" Then Call Enum_Members(objItem) End If Next End Sub Function GetDN(samAccount) If Not IsObject(objWSHNetwork) Then Dim objWSHNetwork : Set objWSHNetwork = WScript.CreateObject("WScript.Network") End If Dim NT : Set NT= CreateObject("NameTranslate") NT.Init 3, "" NT.Set 3, objWSHNetwork.UserDomain & "" & samAccount GetDN = NT.Get(1) End Function;VB Code End Block*****************************************************************

User avatar
jvierra
Posts: 13143
Joined: Tue May 22, 2007 9:57 am
Contact:

Collect Users Info In Group

Post by jvierra » Fri Jun 22, 2012 3:18 am

HI -

Try this:

Look into InputBox for collecting information or use the WScript.StdIn.ReadLine() method.

YOu say you wan the script saved in teh 'parent folder". The parent of what" The current location or teh script or some other location.

Just saving with no path will always save in the current folder.



User avatar
rs6000101
Posts: 6
Joined: Tue Jun 19, 2012 7:54 am

Collect Users Info In Group

Post by rs6000101 » Fri Jun 22, 2012 4:55 am

Good morning and thanks for the reply, I've tried using the following syntax and have been unsuccessful on getting it to work. Also saving with no path this doesn't work I've tried removing the (C:) path out and it ends up not generating any file.
;Start Block
groupName = inputbox("Please enter the name of the group:")
if groupName = "" then
wscript.quit
end if
groupPath = getgrouppath(groupName)
if groupPath = "" then
wscript.echo "Unable to find the specified group in the domain"
wscript.quit
end if
;End Block

User avatar
jvierra
Posts: 13143
Joined: Tue May 22, 2007 9:57 am
Contact:

Collect Users Info In Group

Post by jvierra » Fri Jun 22, 2012 5:38 am

You seem to be mixing VBScript code with batch code. You need to review the syntax differences.

In vbscript use an apostrophe and not a semicolon.
The code you just posted doesm't match the qestion you are asking?

Here is the only line that references the file name:

Code: Select all

objExcel.ActiveWorkBook.SaveAs("C:0000000000000000000MTA_Collect_Individual_Users_In_Group_Information.xlsx")

You need to remove the path:

Code: Select all

objExcel.ActiveWorkBook.SaveAs("MTA_Collect_Individual_Users_In_Group_Information.xlsx")

Tius willplace the file in teh same folder as teh scritp was executed from as long as it is wriable. Just removing the c: assume = a folder in the current directory named 00000000000.

This si basic WIndows file and folder stuf an is not really about scripting. Scripting wit VBScritp or an language uses WIndows just like any other program uses WIndows. Tis is trhe same on AIX, Lunx, Unix flavors and under MVS. It has always been like this. Keeping that in mind will help you to undestand the behaviors of Windows in a batch or in a script.

THe behavior of nearly all systsms is that there is a 'context' or 'default environment' that an process is launched into. The system and launching mechanisms can tailor that context to a specific need. I wWIndows all programs default to the folder they are launched unless a subsystem establisheds a new default. It is poossible for Ecxel to have a macro or setting that wil lforce files into a different location.

You can test that by retrieving the full filename from excel.

msgbox objExcel.ActiveWorkBook.FullName

User avatar
jvierra
Posts: 13143
Joined: Tue May 22, 2007 9:57 am
Contact:

Collect Users Info In Group

Post by jvierra » Fri Jun 22, 2012 6:03 am


Also do you have any suggestions to my last reply as to getting the input box to work.
inputbox("Please enter the name of the group:")


What is wrong with the inputbox? You just say it doesn't work. How does it not work?

Put the following in a file all by itself and prove to yourself that it is working.

Code: Select all

	
resp = InputBox("Get Input","default text")
MsgBox resp
	

PLease place code samples inside of code brackets. Click here for instructions -> BBcodes (you want the one marked CODE at the bottom).
jvierra2012-06-22 13:04:32

User avatar
rs6000101
Posts: 6
Joined: Tue Jun 19, 2012 7:54 am

Collect Users Info In Group

Post by rs6000101 » Fri Jun 22, 2012 7:17 am

Ok I do apologize yes " resp = InputBox("Get Input","default text")" works by it's self. I can only assume that I'm not being clear.
My problem is getting the " InputBox " to work in my script to allow me to put the group in the " InputBox " instead of having to hard code in the following section " Dim strGroup : strGroup = "domain admins" with the script itself.

Locked