Using FTP OLE Automation

 Sample Code: Directory Listing and File Transfer

The following sample searches for all files in a specified directory and transfers all files that match a particular file filter. Note that an array is used to store the contents of the directory listing. This is done because when you are looping through a directory listing, transferring a server file will close the directory listing and terminate the loop.

Attribute VB_Name = "PublicSample"

Option Explicit

' This VB/VBA sample demonstrates the methods .OpenDirectoryListing,

' .GetNextFileDetails, .ReceiveFile, and the property .FileIsDirectory.

' The sample loops through a directory listing loading all files

' that match filter variable into an array. Then it loops through the

' array transferring files.

'Early binding syntax: Requires you add a reference to

'Reflection FTP 1.0 Type Library.

Sub DirectoryListFileTransfer()

On Error GoTo ErrorHandler

'Array to hold filenames that match filter. Array needs to be big

'enough to hold all files in directory

Dim fileArray(1 To 1000) As String

Dim fileCount As Integer 'number of files in directory

Dim x As Integer 'loop counter

Dim hostPath As String 'source path on FTP server

Dim localPath As String 'destination path on PC

Dim filter As String 'string for extension pattern matching

'such as .txt, .doc, .exe

Dim fileName As String 'variable to hold file name

hostPath = "/users/myname/data/" 'Change this to match your environment

localPath = "C:\Data\Test\" 'Change this to match your enviroment

filter = "*.txt" 'Change this to match the filter you want

'Allocate memory and instantiate ReflectionFTP3

Dim FTP As New ReflectionFTP3

'Open the FTP connection

FTP.Open "MyHost", "MyName", "MyPass"

'Set directory on host

FTP.SetCurrentDirectory hostPath

'Open the directory listing of files in host folder

'and apply file filter.

FTP.OpenDirectoryListing filter

'Initialize counter

fileCount = 0

'Loop through each file in directory listing

Do While FTP.GetNextFileDetails

'Set variable to file name if it is not a directory

If FTP.FileIsDirectory = False Then

fileName = FTP.fileName

'Check for error condition. If error goto ErrorHandler

If FTP.LastError <> 0 Then GoTo ErrorHandler

'increment the counter

fileCount = fileCount + 1

'add the file to the array

fileArray(fileCount) = fileName

End If

Loop

'Loop through array transferring files

'if no files are in the directoy then we won't enter loop

For x = 1 To fileCount

FTP.ReceiveFile localPath, fileArray(x), rcBinary, rcOverwrite

If FTP.LastError <> 0 Then GoTo ErrorHandler

Next x

'Close FTP

FTP.Close

'Free the memory

Set FTP = Nothing

Exit Sub

ErrorHandler:

'This is a simple error handler. You could easily log errors

'or email the sysadmin on errors.

Debug.Print "Error: " & CStr(FTP.LastError) & " " & FTP.LastErrorString

End Sub