none
Batch file for FTP connection fails only when called from VBA on Windows Server 2012 R2 RRS feed

  • Question

  • I have a batch file that connects to an FTP server using FTP.exe, it gets the list of csv files, and downloads all the csv files.  This batch works from the command line and from VBA (using the Shell) in Excel 2010 running on Windows 7, 8, 10.

    On Windows Server 2012 R2 though, the batch will work from the command line, task scheduler, etc... EXCEPT when called from VBA in Excel 2010.  The batch file does start to run, and the FTP connection is made, but when the request to list the files is sent, the FTP server pauses and then returns the error "425 Can't open data connection for transfer of "/*.csv" "

    I've set the VBA code to just open a CMD window, and if I run the batch there, it does the same thing (won't transfer anything), so I'm pretty certain it has something to do with the Server 2012 R2 OS and VBA.

    I have the Firewalls on both the server and the client turned off, and have tried passive mode...


    REM 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 Windows Server 2012 R2:
            ''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)

    • Edited by JonPK Monday, September 21, 2015 11:50 PM added code
    Monday, September 21, 2015 11:47 PM

All replies

  • Try this:

            sCMD = "cmd /c cmd /k " & """cd /d D:\xlrx\FTP && GetFTPTickets.bat"""

    I've had to use this for some batch programs.  For FTP I use wininet.dll.  It is what IE uses.  I have found it very reliable.  Here is an upload example:

    Wininet.dll


    • Edited by mogulman52 Tuesday, September 22, 2015 12:19 AM
    Tuesday, September 22, 2015 12:07 AM
  • Thanks for the suggestion; unfortunately I get the same behavior on the FTP server.  I've had a few coworkers research the issue and it's looking like Windows Server 2012 will not let VBA run the shell with admin rights, so the proper ports aren't being opened for getting data back from the FTP server.  We've tried 'runas' and everything else (having another batch / script call the initial one) with no luck.

    Wednesday, September 23, 2015 12:41 PM
  • Try using wininet.dll for FTP.  If it has IE on it then wininet.dll should already be there.
    Wednesday, September 23, 2015 12:52 PM