FTP odd behavior when invoking a batch file from VBA on Windows Server 2012 R2

Question

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

425 Unable to open connection for transmission "/*.csv

I use a computer with a FileZilla server for testing, and I also have access to my client's FTP server (not sure if they work).

Here is what I tried:

Both on Windows 10 and Windows Server 2012 R2 - Firewall disabled, 64-bit OS, 32-bit Excel 2010.

On a Windows 10 laptop:

• Running a batch file from the command line works fine
• Running a command prompt from the Windows Run window (Winkey + R) used by my VBA code works fine
• Running a batch file as a task using the task scheduler works great
• Running UBA, which calls the shell to run the .bat file, works fine

On Windows Server 2012 R2:

• Running a batch file from the command line works fine
• Running a command prompt from the Windows Run window (Winkey + R) used by my VBA code works fine
• Running a batch file as a task using the task scheduler works great

Problem:

• Starting a VBA module that invokes a shell to run a .bat file, the package gets stuck. Watching the FTP server, the batch file completes the login and then shows error 425:

(000046) 9/21/2015 10:36:11 AM - test (10.32.0.75)> 150 Open the data channel for the directory list "/.csv"
(000046) 9/21/2015 10:36:22 AM - test (10.32.0.75)> 425 Unable to open connection for transmission "/.csv"
(000046) 9/21/2015 10:36:26 AM - test (10.32.0.75)> disabled.

This only seems to happen when I try to execute a batch file using VBA on a 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 /dd:\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 tried using the "runas" option, and also ... without dice. Unfortunately, I am not allowed to use another program to connect to the server (although I love WinSCP). I also tried using a vb script to invoke the batch file, but on the FTP server I get the same behavior.

What I did as a workaround was to add the batch file as a scheduled task in the Task Scheduler and just run it every 5 minutes. Not the biggest solution, but it should work until another method is capable. Thank you all for your help!

Answer 2

Obviously, the problem is establishing an active mode connection from the server back to the client. I can’t say why this doesn’t work (local policy that prevents the Excel listening port and its child processes from opening?). But actually it is almost a miracle that it works on Windows 10.

See my article on FTP connection modes to see why active mode is unlikely to work these days because of the ubiquitous firewalls / NAT / proxies.

---

Better to use passive mode. But Windows ftp.exe does not support it.

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

For example, the equivalent batch file using WinSCP scripts :

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

WinSCP uses passive mode by default.

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

(I am the author of WinSCP)