Export a project user list into an Excel report

  • Questions
  • Export a project user list into an Excel report
Question ID: 108512
0
0

Hello. We are looking to create an Excel report that includes the users that are assigned to a project. Can you provide us with the script that will do that for us?

Marked as spam
Posted by (Questions: 219, Answers: 23)
Asked on July 20, 2018 8:30 pm
23 views
Answers (1)
0
Private answer

Hello. I have entered the following script that will create an excel report containing the users assigned to the project specified within the script. Note: This script alone, will create the report you are looking for if you are using a 32-bit OS. If you are using a 64-bit OS, please refer to the following EOT article (click the link below), which explains how to use a batch file to run a vbs file like this, on a 64-bit OS.

[Batch file used to run a vbs file on a 64-bit OS][1]

To create the vbs file, you will need to open an instance of Notepad, enter the script that I have entered below, and save the file with a .vbs file extension. Before saving the file, be sure to edit the file to work in your environment. You will need to edit the server, domain, project, user, and password fields, along with the save location near the bottom of the script and the msgbox message that should display the save location for the Excel report. Once you have edited the script and saved your changes, double-click on the file to create the report. Once the report process is complete, you will be notified with a message box displaying such. I hope this helps.

server = ''http://<>:8080/qcbin''
domain = ''your_domain''
project = ''your_project''
user = ''your_username''
password = ''your_password''

Set tdc = CreateObject(''tdapiole80.tdconnection'')
tdc.InitConnectionEx (server)
tdc.Login user, password
tdc.Connect domain, project
Set cust = tdc.Customization
cust.Load
Set custUsers = cust.Users
Set users = custUsers.Users
Set grps = cust.UsersGroups
Set groups = grps.Groups

Dim Excel, Sheet
Dim Row
Row = 2

Set Excel = CreateObject(''Excel.Application'') 'Open Excel
Excel.WorkBooks.Add() 'Add a new workbook
'Get the first worksheet.
Set Sheet = Excel.ActiveSheet

'Sheet name as Users
Sheet.Name = ''users''

Sheet.Cells(1, 1) = ''User ID''
Sheet.Cells(1, 2) = ''Full Name''
Sheet.Cells(1, 3) = ''Email Address''
Sheet.Cells(1, 4) = ''Group''
Sheet.Cells(1, 5) = ''Phone''

For Each usr in users
grouplist = ''''
For Each group in groups
If usr.InGroup(group.Name) Then
grouplist = grouplist & group.Name & '';''
End If
Next
Sheet.Cells(Row, 1).Value = usr.Name
Sheet.Cells(Row, 2).Value = usr.FullName
Sheet.Cells(Row, 3).Value = usr.Email
Sheet.Cells(Row, 4).Value = grouplist
Sheet.Cells(Row, 5).Value = usr.phone
Row = Row + 1
'f.WriteLine usr.Name & '';'' & usr.FullName & '';'' & usr.Email & '';'' & grouplist
Next

Excel.ActiveWorkbook.SaveAs(''your_save_location'' & ''Users_'' & project & ''_Users_List.xls'')
Excel.Quit
tdc.Disconnect
tdc.ReleaseConnection

msgbox ''<>''

[1]: http://eyeontesting.com/questions/16603/error-activex-component-cant-create-object-tdapiol.html

Marked as spam
Posted by (Questions: 2, Answers: 280)
Answered on July 20, 2018 8:36 pm