Using a script to export a list of Secure FTP/EFT Server users to Excel


THE INFORMATION IN THIS ARTICLE APPLIES TO:

  • EFT Server (All Versions)
  • Secure FTP Server (All Versions)

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, in Windows XP and newer operating systems, 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