FTP upload file in VBA

[lotsbg]

Hi I'm trying to upload a file within excel 2003 and I want to do it from VBA.

so for example I would ahve the following variables

username: username
password: password
ftp: ftp.website.com
filetoupload: filename
webfolder to upload to: ftp.website.com/uploadfilehere/

If anyone can help with how to do this from within VBA that would be great.
Thanks
Jon

 

[rjp]

How about something like this? It's the only way I can think of doing it but it's horribly insecure as the temp file/workbook contains the password in plaintext. Personally I wouldn't do this, I'd look at setting up AD authentication on the FTP server.

It takes it's parameters from the workbook (B1:B5).

Option Explicit

Sub FTPFile()
On Error GoTo Err_FTPFile

    Dim sHost As String
    Dim sUser As String
    Dim sPass As String
    Dim sSrc As String
    Dim sDest As String
    Dim sFTPCmds As String
    Dim iFNum As Integer

    'Build up the necessary parameters
    sHost = Sheet1.Cells(1, "B").Value
    sUser = Sheet1.Cells(2, "B").Value
    sPass = Sheet1.Cells(3, "B").Value
    sSrc = Sheet1.Cells(4, "B").Value
    sDest = Sheet1.Cells(5, "B").Value

    'Write the FTP commands to a text file
    iFNum = FreeFile
    sFTPCmds = Environ("TEMP") & "\" & "FTPCmd.tmp"
    Open sFTPCmds For Output As #iFNum
        Print #iFNum, "op " & sHost
        Print #iFNum, "user " & sUser & " " & sPass
        Print #iFNum, "cd " & sDest
        Print #iFNum, "put " & sSrc
        Print #iFNum, "bye"
    Close #iFNum

    'Upload the file
    Shell Environ("WINDIR") & "\System32\ftp.exe -n -s:" & sFTPCmds

Exit_FTPFile:
    On Error Resume Next
    Close #iFNum
    'Delete the temp FTP command file
    Kill sFTPCmds
    Exit Sub

Err_FTPFile:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error"
    Resume Exit_FTPFile

End Sub

 

[AndrewLeonard]

This worked for me after a long time of trial and error
I had to add the following line :

Application.Wait (Now + TimeValue("00:00:01"))

before and after

Shell Environ("WINDIR") & "\System32\ftp.exe -n -s:" & sFTPCmds

I am guessing I needed to slow the macro down(?)

My values in B1-B5 are
host name (ie mysite.co.uk)
Username
Password
source file (ie C:\folder_name\folder_name\file_name)
Destination (ie www/host name/folder_name - file name not required - this structure may vary for different hosts - you have to check the file structure on your server)

I hope this helps and I congratulate rjp
You can password protect the Excel macro file and then it would be secure (?)