Need to list all folders/subfolders/filenames from FTP server

[sarathi57]

Hi,

I need to login to a ftp site and list all the filename and the size of the file in all the folders and sub fodlers recursively.

Is there an option to do that with access vba?

I am able to do that for a single folder. But i need to do for the entire folders and sub folders in that FTP site.

Please help with your comments and thoughts.

regards
Sarathi.

 

[SkipVought]

hi,

Please post the code that you are currently using.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sarathi57]

Hi,
Here is the code that i have which will give me the Entire File names and size from the given FTP folder.

put this code in a module:

Option Compare Database

Private Const STARTF_USESHOWWINDOW& = &H1
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Public str_srvr As String
Public str_Uname As String
Public str_psswd As String
Public str_dest_folder As String
Public str_dest_files As String
Public str_source_folder As String


Private Type STARTUPINFO
cb As Long
lpReserved As String
lpDesktop As String
lpTitle As String
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As Long
hStdInput As Long
hStdOutput As Long
hStdError As Long
End Type

Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessID As Long
dwThreadID As Long
End Type

Declare Function GetShortPathName Lib "kernel32" Alias "GetShortPathNameA" (ByVal lpszLongPath As String, ByVal lpszShortPath As String, ByVal cchBuffer As Long) As Long
Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Declare Function CreateProcessA Lib "kernel32" (ByVal lpApplicationName As Long, ByVal lpCommandLine As String, ByVal lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, lpStartupInfo As STARTUPINFO, lpProcessInformation As PROCESS_INFORMATION) As Long
Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Sub ShellWait(Pathname As String, Optional WindowStyle As Long)
On Error GoTo Err_Handler

Dim proc As PROCESS_INFORMATION
Dim start As STARTUPINFO
Dim ret As Long

' Initialize the STARTUPINFO structure:
With start
.cb = Len(start)
If Not IsMissing(WindowStyle) Then
.dwFlags = STARTF_USESHOWWINDOW
.wShowWindow = WindowStyle
End If
End With
' Start the shelled application:
ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)
' Wait for the shelled application to finish:

ret& = WaitForSingleObject(proc.hProcess, INFINITE)
ret& = CloseHandle(proc.hProcess)

Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Unable to Execute the Operation. Please try again."
Resume Exit_Here

End Sub

Public Function GetFtpFolderFileList(sSVR As String, sFLD As String, sUID As String, sPWD As String) As Boolean

Dim sLocalFLD As String
Dim sScrFile As String
Dim iFile As Integer
Dim sExe As String

Const q As String * 1 = """"

On Error GoTo Err_Handler

' will break if empty folder exist so error to pass
' must create folder first, so API calls work
On Error Resume Next
If Dir(str_dest_folder) = "" Then MkDir (sLocalFLD)
On Error GoTo Err_Handler

sScrFile = str_dest_folder & "FileDIR.scr"
If Dir(sScrFile) = "FileDIR.scr" Then Kill sScrFile
If Dir(str_dest_folder & "FileList.txt") = "FileList.txt" Then Kill (str_dest_folder & "FileList.txt")

' Open a new text file to hold the FTP script and load it with
' the appropriate commands. (Thanks Dev Ashish !!!)
iFile = FreeFile
Open sScrFile For Output As iFile
Print #iFile, "open " & sSVR
Print #iFile, sUID
Print #iFile, sPWD
Print #iFile, "binary"
Print #iFile, "cd " & q & sFLD & q
Print #iFile, "ls . " & str_dest_folder & "FileList.txt"
Print #iFile, "bye"
Close #iFile
Form_frm_FileDownload.lblStatus.Caption = "Step 3/3-- Getting the List of Input Files"
sExe = Environ$("COMSPEC")
sExe = Left$(sExe, Len(sExe) - Len(Dir(sExe)))
sExe = sExe & "ftp.exe -s:" & q & sScrFile & q

ShellWait sExe, vbHide
DoEvents

If Dir(str_dest_folder & "FileList.txt") = "FileList.txt" Then
GetFtpFolderFileList = True
If DCount("*", "MsysObjects", "[Name]='tblFileList'") = 1 Then DoCmd.DeleteObject acTable, "tblFileList"
DoCmd.TransferText acImportDelim, , "tblFileList", str_dest_folder & "FileList.txt", False

Else
GetFtpFolderFileList = False
MsgBox "Unable to Retreive the File List from FTP site. Please try again later", vbOKOnly, "FTP File Error"
Exit Function
End If
Exit Function

Exit_Here:
DoCmd.Hourglass False
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Unable to execute the operation. Please try again"
Resume Exit_Here
End Function
______________________________________________________________

This will give me the file name and file size from the Folder that i have specified. This will be available in the table that i have specified in the import statement.

All i need is , i want to sacn the entire FTP server and list all the file names and file size with folder and sub folder information.

Your help will be much valued.