Using a script to export a list of EFT users to Excel


THE INFORMATION IN THIS ARTICLE APPLIES TO:

  • EFT 

QUESTION

How can I use a script to export a list of Server users to Excel?

ANSWER

Following is a sample script that gets a list of users using the COM interface.

  1. Download CreateUserListSpreadSheet.txt or copy the script below into Notepad.
  2. Modify the server administrator login information as necessary.
  3. Save the script with a .VBS extension.
  4. At a command prompt, use CSCRIPT to run the script or double-click the file to execute the script.

**Be sure to edit the script to change the Server, Port, Username, and Password to your Server's settings. Remove any extra blank lines introduced when you copy and paste the text.**

'
' FILE: CreateUserListSpreadSheet
' CREATED: 13 OCT 2004 GTH
' PURPOSE: List the users of a site and create an excel spreadsheet.
'
Set SFTPServer = WScript.CreateObject("SFTPCOMInterface.CIServer")
CRLF = (Chr(13)& Chr(10))
   txtServer = "localhost"
   txtPort =  "1100"
   txtUserName = "eft"
   txtPassword = "eft"
' On Error Resume Next
SFTPServer.Connect txtServer, txtPort, txtUserName, txtPassword
If Err.Number <> 0 Then
   WScript.Echo "Error connecting to '" & txtServer & ":" & txtPort & "' -- " & err.Description & " [" & CStr(err.Number) & "]", vbInformation, "Error"
   WScript.Quite(255)
Else
   WScript.Echo "Connected to " & txtServer
End If
set Sites=SFTPServer.Sites
Set oExcel = WScript.CreateObject("Excel.Application")
oExcel.visible = true
Set oWorkbook = oExcel.Workbooks.Add
For i = 0 to SFTPServer.Sites.Count-1
   set theSite=Sites.Item(i)
   Set theSheet = oWorkbook.Worksheets.add
   theSheet.name = theSite.Name
   theSheet.Cells(1, 1) = "Users:"
   arUsers = theSite.GetUsers()
   For j = LBound(arUsers) to UBound(arUsers)
      theSheet.Cells((j+2), 1) = arUsers(j)
   Next
   theSheet.Columns("A:A").EntireColumn.Autofit
Next
Set oExcel = nothing
SFTPServer.Close
Set theSite = nothing
Set SFTPServer = nothing