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

Question

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:

• Running batch file from command prompt works fine
• Running the command string from the Windows Run window (Winkey+R) that my VBA code is using, works fine
• Running the batch file as a task via the Task Scheduler, works fine
• Running the VBA sub that calls the shell to run the .bat file, works fine

On Windows Server 2012 R2 server:

• Running batch file from command prompt works fine
• Running the command string from the Windows Run window (Winkey+R) that my VBA code is using, works fine
• Running the batch file as a task via the Task Scheduler, works fine

The Problem:

• Running the VBA sub that calls the shell to run the .bat file, batch gets hung up. Watching the FTP server, the batch file completes the log in, and then shows the error 425:

(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)

Answer 1

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!

Answer 2

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)