CODEWITHSUNDEEP
excelvbabatch-fileftp
Jon Craigs
Jon Craigs

Reputation: 109

Odd FTP behavior when calling batch file from VBA on Windows Server 2012 R2

I have a basic .bat file that connects to an FTP server, saves the list of CSV files, and then downloads all the CSV files from the FTP server. I am using VBA to call the shell in order to run the .bat file. On my work laptop (Windows 10) everything runs fine, but if I run it on Windows Server 2012 R2, the .bat file gets stuck because the FTP server is giving the error:

425 Can't open data connection for transfer of "/*.csv

I'm using a PC running FileZilla server to test with, and I also have access to my client's FTP server (not sure what they are running).

This is what I've tried:

On both Windows 10 and Windows Server 2012 R2 - Firewall disabled, 64bit OSes, Excel 2010 32-bit.

On Windows 10 laptop:

On Windows Server 2012 R2 server:

The Problem:

(000046)9/21/2015 10:36:11 AM - test (10.32.0.75)> 150 Opening data channel for directory listing of "/.csv"
(000046)9/21/2015 10:36:22 AM - test (10.32.0.75)> 425 Can't open data connection for transfer of "/.csv"
(000046)9/21/2015 10:36:26 AM - test (10.32.0.75)> disconnected.

It only seems to do this when I try executing the batch file using VBA on the Server 2012 R2 machine. I'm at a loss ... any ideas?

Batch file code: 

@echo off
REM Enter the username
echo user test> ftpcmd.dat

REM Enter the password
echo test>> ftpcmd.dat

REM Change the local computers' directory
echo lcd D:/XLRX/FTP/FTP_Tickets>> ftpcmd.dat

REM Get a list of the csv files we're about to copy
echo ls *.csv D:/XLRX/FTP/TESTCopiedCSV.txt>> ftpcmd.dat

REM Download all the csv files to the local directory
echo mget *.csv>> ftpcmd.dat

REM Remove the files we just downloaded from the FTP server

REM Close the connection
echo quit >> ftpcmd.dat

REM use -d for debugging, -i for preventing user interaction questions
ftp  -i -n -s:ftpcmd.dat xxx.xxx.xxx.xxx

REM Clean Up 
del ftpcmd.dat

REM  Close the command window
EXIT

VBA Code:

'Call the batch file to pull down the FTP tickets to the local server
sToday = Format(Now, "yyyymmdd_hhmm")

''-----------------------------------TEST CODE--------------------------------------''
''The following line works from the Windows RUN prompt on the EnerVest server:
    ''cmd /k "cd /d d:\xlrx\FTP && TESTGetFTPTickets.bat" >> D:\XLRX\FTP\FTP_Logs\TEST.log

If sTesting = "NO" Then
    sFTPLogName = sToday & ".log"     'Sets the FTP log filename
    sCMD = "cmd /k " & """cd /d D:\xlrx\FTP && GetFTPTickets.bat"""
    Else
    sFTPLogName = "TEST_" & sToday & ".log"   'Sets the FTP log filename if testing
    sCMD = "cmd /k " & """cd /d D:\xlrx\FTP && TESTGetFTPTickets.bat"""
End If

sLog = ">> " & sFTPLogFolder & "\" & sFTPLogName
vArguments = Array(sCMD, sLog)     'New Code 9/20/2015

sShell = Join(vArguments, " ")                  'Joins the above arguments into a string separated by " " (spaces)


'Call the Shell (command line) and use the sShell
Call Shell(sShell)

Upvotes: 5

Views: 1091

Answers (2)

Martin Prikryl
Martin Prikryl

Reputation: 202292

There's obviously problem establishing an active mode connection from the server back to the client. I cannot tell why it does not work (a local policy that prevents an Excel and its child processes from opening a listening port?). But it's actually nearly a miracle that it works on Windows 10.

See my article on FTP connection modes to understand why active mode can hardly work nowadays due to ubiquitous firewalls/NATs/proxies.

You should better use a passive mode. But Windows ftp.exe does not support it.

Use any other command-line FTP client. All others do support the passive mode.

For example an equivalent batch file using WinSCP scripting:

@echo off
winscp.com /log=c:\path\log.log /command ^
    "open ftp://user:[email protected]" ^
    "lcd D:\XLRX\FTP\FTP_Tickets" ^
    "get *.csv" ^
    "exit"

WinSCP defaults to the passive mode.

See a guide for converting Windows ftp.exe script to WinSCP.

(I'm the author of WinSCP)

Upvotes: 1

Jon Craigs
Jon Craigs

Reputation: 109

So I've tried using the "runas" option as well...no dice. Unfortunately I'm not allowed to use another program to connect to the server (although I love WinSCP). I also tried using a vb script to call the batch file, but I get the same behavior on the FTP server.

What I did as a workaround is to add the batch file as a scheduled task in Task Scheduler, and just have it run every 5 mins. Not the greatest solution, but it will have to work until another method is able to. Thank you everyone for your help!

Upvotes: 2

Related Questions