Using VBA to ftp a file

I am having a heck of time getting this to work and would welcome any suggestions. All I want to do is FTP a file from one server to another using a batch script that I wrote. When I execute this code, it attempts to open up a command prompt on my local machine, not on the server where the batch file resides.

Option Compare Database
Option Explicit

Function testftp()

    Dim c_test As String

    c_test = "\\na.paccar.com\itdren\Projects\NATS Project\Working Documents\Access\Batch\ftp_order_file.bat ord.20050915141932"

    Call Shell(Environ$("COMSPEC") & " " & c_test, vbNormalFocus)

End Function

Answer this question

Using VBA to ftp a file

Juan Ignacio Gelos
Shell will always open a local command prompt. Based on your passed variables, it will call the remote batch file (but run the batch file locally). The batch file would fail if the called files are not accessible from your local machine.

If for some reason you cannot access remote files from your machine, you'd need to setup some type of remote shell daemon on the remote machine (much easier if remote machine is Windows server or Linux/Unix vs. a client machine). Microsoft purposely disables quite a few remote operations, since they are security breaches waiting to happen.

Hope this helps,
Josh Lindenmuth
demmith
The easiest solution would be to create a seperate dll, and then add it as a reference in vba. If you're using vs.net 2002/3 then this Microsoft KB should help you out. If you're using any of the express products or vs.2005 then you can use the much simpler My.Computer.Network.UploadFile() method. One of it's overloads supports username and password.

Cathal
Using VBA to ftp a file

Answer this question