Excel VBA FTP ECONNREFUSED error

[knifey]

Hi,
I've got a problem that is causing me to lose hair fast and hope one of you can help!
I have an Excel 2010 VBA spreadsheet with 35,000 rows of data. Each row referres to an ftp image that needs downloading. I then dedupe and sort this image list and feed it into some VBA FTP code.
This works perfectly on my laptop in London, England. But when my customer in New York, USA runs the FTP code it runs to completion but only downloads 50 or so images (it should be 2,302 images).
After running the FTP code my customer logged on to the ftp site manually and got this error:

Status: Connection attempt failed with "ECONNREFUSED - Connection refused by server".
Error: Could not connect to server

The same error appears after running the ftp code if he tries to connect to the ftp site with FileZilla.

...But if he waits 5 minutes after this error appears he can logon to the ftp site manually or through FileZilla without any problems and continue downloading images.

...if he then runs the FTP code again, then tries to logon to the ftp site manually (or via FileZilla) the error has returned (again only for 5 minutes).

I've done a lot of Googling for answers and have advised my customer to:
1.) Turn off AV and Windows firewalls.
2.) In IE goto Tools>Internet Options>Advanced and uncheck 'Enable FTP folder view (outside of internet explorer)' and check 'Use passive FTP (for firewall and DSL modem compatibility)'.

..still no luck.

It looks like the VBA FTP code is causing this error, but it works fine on my laptop?

Please help, any advise or pointers would be much appreciated.
Thanks,
K

 

[PHV]

the VBA FTP code is causing this error
Which code ?

Hopefully you don"t try to connect 2,302 times to the ftp server but once to get 2,302 files.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[knifey]

Hi PHV,
You were right, sometimes it's the most stupid obvious thing. My current code is connecting and disconnecting 2302 times. I will amend this first thing tomorrow and have my customer run another test.
But this dosen't explain why it works fine on my laptop?? Would it be something to do with the fact my laptop has 4GB RAM whereas my customers laptop has 8GB. Would the difference in speed cause his code to run too fast and possibly try to connect when it hasn't yet finished disconnecting?
I have pasted the code I am using below (heavily amended).
Happy New Year,
K

Calling from a loop:

Application.StatusBar = "Downloading image " & r1 & " of " & wb1EndRowImageURLList & "."
aVal = wb1.Worksheets("Image URL List").Rows(r1).Columns("A").Value
fName = getFilenameFromURL2(aVal)
newFName = vendorNumber & "-" & fName
DoEvents
Call API_DoEvents
Sleep 10
Call UploadOrDownloadViaFTP(fName, newFName, "Download")

FTP code in seperate module:

'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 UploadOrDownloadViaFTP(fName, newFName, UploadOrDownload)  'THIS WORKS

'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 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
Dim wb1 As Workbook
Dim imageFolder As String

'Dim localFile As String

Const ASCII_TRANSFER = 1
Const BINARY_TRANSFER = 2

Set wb1 = ThisWorkbook
imageFolder = wb1.Worksheets("Admin").Range("G13").Value

ServerName = "portal.capel.net"
UserName = "********"
Password = "********"
localFile = imageFolder & "\" & newFName
hostFile = "\\Large_Images\" & fName

UploadOrDownloadViaFTPSuccess = False
RetVal = False
INet = InternetOpen("MyFTP Control", 1&, vbNullString, vbNullString, 0&)
If INet > 0 Then
    DoEvents
    Call API_DoEvents
    Sleep 1
    INetConn = InternetConnect(INet, ServerName, 0&, UserName, Password, 1&, 0&, 0&)
    DoEvents
    Call API_DoEvents
    Sleep 1
    If INetConn > 0 Then
        If UploadOrDownload = "Download" Then
            DoEvents
            Call API_DoEvents
            Sleep 10
            Success = FtpGetFile(INetConn, hostFile, localFile, BINARY_TRANSFER, 0&, 0&, 0&)
            DoEvents
            Call API_DoEvents
            Sleep 10
        ElseIf UploadOrDownload = "Upload" Then
            Success = FtpPutFile(INetConn, hostFile, localFile, BINARY_TRANSFER, 0&)
        Else
            MsgBox ("Upload or download not set error.")
        End If
        RetVal = InternetCloseHandle(INetConn)
    End If
    RetVal = InternetCloseHandle(INet)
End If

If Success <> 0 Then
'  MsgBox ("Upload process completed")
    UploadOrDownloadViaFTPSuccess = True 'Global var
Else
'  MsgBox "FTP File Error!"
    UploadOrDownloadViaFTPSuccess = False 'Global var
End If
Set wb1 = Nothing
End Sub

 

[SkipVought]

Why is one working and not the other?

Hint: sleep x during an asynchronous process.

Question: would you wait at each traffic light (red or green) exactly x seconds and then proceed?

Suggestion: look for an indicator of the existence of the file being xferred TO complete (green).



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[knifey]

Hi Skip,,
'Why is one working and not the other?'
>>I don't know? I've tested on 2 pc's in London, England and everything is ok. But my customer in the US get's varied results with different pc's he uses (i.e. different amounts of jpg's downloaded successfully).

'Hint: sleep x during an asynchronous process.'
'Question: would you wait at each traffic light (red or green) exactly x seconds and then proceed?'
>>I don't really know how to use sleep. I have used it here as I'm at the point of giving up!

'Suggestion: look for an indicator of the existence of the file being xferred TO complete (green).'
>>This is exactly what I need but I don't know how to put this into code (e.g. stop the program from running when each jpg download starts, then continue when it has completed). Could you possibly point me in the right direction?

Happy new year,
Roy
P.S. I rewrote my code as per PHV's post and it's much faster. But my customer still has the same problem.

 

[SkipVought]

I scrape screens using an IMS terminal emulator (Attachmate Extra TN 3270 emulator)

Each IMS screen has a cursor rest location; some row, column coordinate where the cursor ends up once the IMS (my asynchronous system) system has returned control to the emulator.

1) I issue the Send Keys command to the IMS system
2) I MOVE the cursor away from the rest coordinates
3) in a loop I WAIT for the cursor to return to the rest coordinates.

You might check for the existence of the file that your FTP is creating. Once it exists, you ought to be able to proceed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}