Using VB to upload to an FTP

I'm looking for a way to automatically upload a file to an FTP site. I'm currently writing out a text file, but then have to use an FTP app to move the file over... any thoughts on how to best do this?

Hello CCK,

Welcome to the Forum!

As a new member of the forum, please take a few minutes and read the forum rules for posting and general etiquette. To see them, click on the link below...

Forum Rules

There are two ways to use the FTP with VBA. One is to create and run a batch file or to user API calls. Personally, I feel the API is easier and safer to use. Change the information marked in red below to match your information.

'Written: June 11, 2008
'Author:  Leith Ross

'Open the Internet object
 Private Declare Function InternetOpen _
   Lib "wininet.dll" _
     Alias "InternetOpenA" _
       (ByVal sAgent As String, _
        ByVal lAccessType As Long, _
        ByVal sProxyName As String, _
        ByVal sProxyBypass As String, _
        ByVal lFlags As Long) As Long

'Connect to the network
 Private Declare Function InternetConnect _
   Lib "wininet.dll" _
     Alias "InternetConnectA" _
       (ByVal hInternetSession As Long, _
        ByVal sServerName As String, _
        ByVal nServerPort As Integer, _
        ByVal sUsername As String, _
        ByVal sPassword As String, _
        ByVal lService As Long, _
        ByVal lFlags As Long, _
        ByVal lContext As Long) As Long

'Get a file using FTP
 Private Declare Function FtpGetFile _
   Lib "wininet.dll" _
     Alias "FtpGetFileA" _
       (ByVal hFtpSession As Long, _
        ByVal lpszRemoteFile As String, _
        ByVal lpszNewFile As String, _
        ByVal fFailIfExists As Boolean, _
        ByVal dwFlagsAndAttributes As Long, _
        ByVal dwFlags As Long, _
        ByVal dwContext As Long) As Boolean

'Send a file using FTP
 Private Declare Function FtpPutFile _
   Lib "wininet.dll" _
     Alias "FtpPutFileA" _
       (ByVal hFtpSession As Long, _
        ByVal lpszLocalFile As String, _
        ByVal lpszRemoteFile As String, _
        ByVal dwFlags As Long, _
        ByVal dwContext As Long) As Boolean

'Close the Internet object
 Private Declare Function InternetCloseHandle _
   Lib "wininet.dll" _
     (ByVal hInet As Long) As Integer

Sub UploadFTP()

 'When uploading a file, make sure you have permisson to create a file on the server.
 'The size limit for a uploading a file is 4GB.
 
  Dim hostFile As String
  Dim INet As Long
  Dim INetConn As Long
  Dim hostFile As String
  Dim Password As String
  Dim RetVal As Long
  Dim ServerName As String
  Dim Success As Long
  Dim UserName As String
  
  Const ASCII_TRANSFER = 1
  Const BINARY_TRANSFER = 2

    ServerName = "myserver.some.company"
    UserName = "anonymous"
    Password = "MyEmail@somewhere.net"
    localFile = "C:\My Documents\Test.Txt"
    hostFile = "\\My Test File.txt"

      RetVal = False
      INet = InternetOpen("MyFTP Control", 1&, vbNullString, vbNullString, 0&)
        If INet > 0 Then
          INetConn = InternetConnect(INet, ServerName, 0&, UserName, Password, 1&, 0&, 0&)
            If INetConn > 0 Then
              Success = FtpPutFile(INetConn, localFile, hostFile, BINARY_TRANSFER, 0&)
              RetVal = InternetCloseHandle(INetConn)
            End If
         RetVal = InternetCloseHandle(INet)
        End If

      If Success <> 0 Then
        MsgBox ("Upload process completed")
      Else
        MsgBox "FTP File Error!"
      End If

End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Leith, thanks that was fast.

I'll work with this tomorrow, to see if I can get it running properly.
I did a quick test, and had an error "duplicate definition" on the "Dim hostFile As String"

any thoughts, I don't see it in another place?

also, a couple quick questions, is the "hostFile" a new name that will be used to rename the localFile when it's place on the FTP?

localFile = "C:\My Documents\Test.Txt"
hostFile = "\\My Test File.txt"

thanks

CCK

Ok, I had to try tonight, and it works great...!

I just commented out the one error, and it's fine..

I do have another question though... The location I'm trying to put the file is several layer down...

The FTP site is 123.123.xxxx.xxx

But when it try to use the following it fails...

123.123.xxxx.xxx/folder1/folder2/

how should I input this to make it work properly...

Thanks for the help... !!

CCK

Can you please mark this as solved... !

thank you

Hey Leith, your code is very useful. Is there some statement on copyright, reuse, licence you can give as I want to include it in a project and credit without problems surfacing later.

How can we add a folder to an ftp site

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.