FTP

These code extracts show you how to send and receive files using FTP from within VBA.

You will need to amend the code for your own account name, password, directories and file names.

The code creates three files.

• First is a text file (.txt) that contains the commands processed by FTP,
• Second is a batch file (.bat) that runs the FTP command
• Third is a marker file (.out) that is created by the batch when FTP has finished processing. When this is created, our VBA code detects it and knows it can then carry on with its tidying up routine.

You can (sometimes) see this in operation at this page: Financial Data.
 

Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer

On Error GoTo Err_Handler
    lStr_Dir = ThisWorkbook.Path
    lInt_FreeFile01 = FreeFile
    lInt_FreeFile02 = FreeFile

    '' ANW  07-Feb-2003 :
    strDirectoryList = lStr_Dir & "\Directory"

    '' Delete completion file
    If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")

    '' Create text file with FTP commands
    Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
    Print #lInt_FreeFile01, "open yoursite.com"
    Print #lInt_FreeFile01, "account_name"
    Print #lInt_FreeFile01, "account_password"
    Print #lInt_FreeFile01, "cd source/uploads"
    Print #lInt_FreeFile01, "binary"
    Print #lInt_FreeFile01, "send " & ThisWorkbook.Path & "\Picture.gif targetdir/Picture.gif"

    '' To receive a file, replace the above line with this one
    ''Print #lInt_FreeFile01, "recv \Picture.gif " & ThisWorkbook.Path & "\Picture.gif"
    
    Print #lInt_FreeFile01, "bye"
    Close #lInt_FreeFile01

    '' Create Batch program
    Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
    Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"

    Print #lInt_FreeFile02, "Echo ""Complete"" > " & strDirectoryList & ".out"
    Close #lInt_FreeFile02

    '' Invoke Directory List generator
    Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus
    'Wait for completion
    Do While Dir(strDirectoryList & ".out") = ""
        DoEvents
    Loop

    Application.Wait (Now + TimeValue("0:00:03"))

    '' Clean up files
    If Dir(strDirectoryList & ".bat") <> "" Then Kill (strDirectoryList & ".bat")
    If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")
    If Dir(strDirectoryList & ".txt") <> "" Then Kill (strDirectoryList & ".txt")

bye:

Exit Sub

Err_Handler:
    MsgBox "Error : " & Err.Number & vbCrLf & "Description : " & Err.Description, vbCritical
    Resume bye

End Sub

 

The FTP program is part of the Windows operating system. The Windows Help file contains more information aboout the commands available.

Published: 16-Jul-2004
Last edited: 01-Apr-2007 19:11