How to log FTP commands?

[Mike Gend]

Hi,
I am new to excel VBA. I am trying to FTP a text file to a server. I am using the below code to achieve it.

Code :
vPath = "c:\Documents and Settings\Mike Gend"
VFTP = "fpsba.com"
num = FreeFile()

Open Vpath & "\ftpcommands.txt" For Output as #num
Print #1, "User Mike"
Print #1, "XXXXX"
Print #1, "cd Mike"
Print #1, "put delta.txt"
Print #1, "close"
Print #1, "quit"
close #num

shell "ftp -n -i -g -s:" & vPath &\ftpcommands.txt" & VFTP,vbNormalNoFOcus

Here are what I am exactly lookin for :

1. Normally I will get connection errors. I am not sure how to handle connection errors using VBA. Is it possible to perform retry logic automatically?
2. I am not sure how to find whether the file transfer is successful or not

Any help is appreciated!

Thanks
Mike

 

[Tom Urtis]

My take on this is, I would not attempt to do what you are doing and expect it to work from a VBA platform. Sorry for the gloom and doom opinion but I can tell you from first hand experience, FTP is challenging enough to work correctly when being manually monitored. It's not anyone's fault and in fact I have FTP accounts for my clients off my own server. Even with that I sometimes get time-out errors and connection errors despite a fast connection speed (50/10). I would not rely on VBA to do the monitoring, executing, or error fixing.

Just my take, others may disagree for very good reasons that have worked for them, and I hope you find a solution, but with VBA it's a steep and improbable uphill climb.

 

[John_w]

Running the ftp commands as a separately shelled process, as your code does, it will be very difficult if not impossible to detect and handle connection errors, or whether the file transfer succeeded.

A better approach, which allows more control over individual ftp commands, is the Microsoft Internet Transfer Control - see http://msdn.microsoft.com/en-us/library/aa733648(VS.60).aspx. Start by setting a reference to Microsoft Internet Transfer Control (MSINET.OCX) in your VBA project.

 

[Mike Gend]

Hi John,

Thanks for your reply.

As I am new to VB could you please help me in referencing to the MSINET and some sample code by which I can acheive my requirements.

Thanks a ton in advance.

Thanks
Mike

 

[John_w]

To set the reference, in the VB editor click Tools - References, find and tick Microsoft Internet Transfer Control. If MITC isn't listed, add a userform, click additional controls, add MS Internet Transfer Control to userform, remove the userform. MITC should still be listed.

There are snippets of code in the link I posted.

 

[Mike Gend]

Hi,

Thanks a lot for your comments. I did some research in internet and downloaded msinet.ocx and did run --> regsvr32 "c:\windows\system32\msinet.ocx" which gave me succeed message.

When I try to execute the below code

Function UploadFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String) As Boolean
Dim FTP As Inet
Set FTP = New Inet
With FTP
.Protocol = icFTP
.RemoteHost = HostName
.UserName = UserName
.Password = Password
.Execute .URL, "Put " + LocalFileName + " " + RemoteFileName
Do While .StillExecuting
DoEvents
Loop
UploadFile = (.ResponseCode = 0)
End With
Set FTP = Nothing
End Function

I am getting runtime error 429, Active X component cant create an object.

I am not sure if this is because I ran the run command as an user but not as an admin as I am not the admin to my system

Any pointers are well appreciated.

Thanks
Mike