FTP Files with Batch File Using VBA Code

[juricta]

I created a POS program that uploads specific files in specific folders to an FTP site. I am looking for a backup way to do this in the event that my POS errors out. FileZilla is an excellent way BUT my users are not extremely computer literate even for FileZilla!!

Is there a way to create a batch file that will, at the touch of a button (VBA code), do the following:
1.Open FileZilla and Logon
2.Upload SPECIFIC files from a SPECIFIC folder
3.Download SPECIFIC files from to SPECIFIC folder
4.Download FTP report
5.Close FTP connection
6.CLOSE FileZilla

Download files will AWAYS be located in same folder and have SAME name (newprice.txt, new_pricelist.txt, etc.) and be downloaded to the SAME local folder each time
Upload files have a name that ends in 6 character date (013118 for 31Jan18). Everything preceding the date will ALWAYS be the same (Sales013118.txt, Sales020118, Inv013118, etc.)
On local machine folders - all files will be uploaded then after connection closed those files will be moved to storage (this already works)
ALL Uploads are FTP'd to the SAME remote folder regardless of their subject.

[boco]

No, FileZilla cannot be scripted nor automated.

Note that there are languages (AutoIt3 / AutoHotKey) that can operate the GUIs of other programs automatically. In theory, you could reach your goal that way. However, it is probably much easier to just use a dedicated command line FTP for that job (for example: lftp).

[ronczap]

You might be able to use the FTP command line program in some sort of kludge manner or even a batch file...

[ronczap]

I did a little research and this worked for me.

I created a FTP command file with notepad on my E: drive called test.ftp

Code: Select all

open localhost
user
password
lcd "E:\temp"
cd Ron
recv welcome.htm
disconnect
quit

where user and password are obviously your username and password
Instead of "localhost" you can use an ip address or url (you can also include a space followed by the port if not using 21)
The LCD command sets the working directory on the local computer
The CD command sets working directory on the remote FTP server to "Ron"
The RECV command transfers the file

To execute the commands in this file I entered "ftp -s:test.ftp" from a command prompt at E:\

Here is the output:

E:\>ftp -s:test.ftp
ftp> open localhost
Connected to Czapala7.
220 Welcome to Ron's FTP server!
User (Czapala7:(none)):
331 Password required for user

230 Logged on
ftp> lcd "E:\Temp"
Local directory now E:\temp.
ftp> cd ron
250 CWD successful. "/ron" is current directory.
ftp> recv welcome.htm
200 Port command successful
150 Opening data channel for file download from server of "/ron/welcome.htm"
226 Successfully transferred "/ron/welcome.htm"
ftp: 282 bytes received in 0.00Seconds 282000.00Kbytes/sec.
ftp> disconnect
221 Goodbye

ftp> quit

E:\>

You could use VBA and the FileSystemObject to create the command file and the Windows Script Host WshShell run method to execute it

[juricta]

Ronczap,

Thank you. I'll try this and see how it works.

[boco]

Please note that the built-in Windows FTP client is ancient and does not support Passive mode, which is essential in a world of routers and firewalls. Additionally, even the supported Active (PORT) mode can't be configured at all, which does make it virtually worthless if the client is behind NAT. And third, it doesn't support any FTP over TLS encryption whatsoever.

[ronczap]

boco,

Thanks - good information. It is a rude and crude approach but can probably be useful in some situations.

I had an old version of another FTP server and found out about FileZilla recently - it is impressive!

Haven't tried setting up TLS encryption yet - I'll have to look into it when I have time.

[juricta]

I have code that works for FTP but if there is a glitch or the code errors out then my customer is left high and dry. I was trying to find a way to use FileZilla as a backup plan but would need a way to automate the process. My customers are NOT very computer literate or do not want to have to do anything. Also, this process usually runs after duty hours

If I could have called FileZilla from my code and use hard code variables then the customer would not have to do anything and uploads/downloads would continue.

Still, I sincerely thank ALL those who have contributed to my plight.

[ronczap]

I have code that works for FTP but if there is a glitch or the code errors out then my customer is left high and dry. I was trying to find a way to use FileZilla as a backup plan but would need a way to automate the process. My customers are NOT very computer literate or do not want to have to do anything. Also, this process usually runs after duty hours

If I could have called FileZilla from my code and use hard code variables then the customer would not have to do anything and uploads/downloads would continue.

Still, I sincerely thank ALL those who have contributed to my plight.

Well here is another approach. Copy this code and save it as "RunFtp.vbs" (or whatever) on your desktop.
It creates a file called "ftpcmd.ftp" in your Windows temporary folder with the FTP commands in it.
It executes FTP capturing the output from FTP
displays it and deletes the ftpcmd.ftp file.

Doubleclick the vbscript icon to run it.

You can change the constants for your needs and if you desire you could prompt for the name of the file to download using the InputBox function
or make other tweaks

You could schedule this script to run with Windows Task Scheduler using "wscript.exe "<path>\"RunFtp.vbs" (comment out the "wscript.echo stdout" line)

You can use Schtasks.exe in a batch file to schedule the task
**EDIT**
SCHTASKS /Create /SC DAILY /ST 22:00 /TN RunFTP /RU SYSTEM /TR "wscript.exe C:\WSH\RunFtp.vbs"
This will run the script every day at 10pm whether the user is logged on or not

THE BELOW SCRIPT HAS SECURITY VULNERABILITIES, DO NOT USE!!!

Code: Select all

Option Explicit
  Const TemporaryFolder = 2
  Const ForWriting = 2
  const ftpsite ="localhost"
  const uname="username"
  const psw="userpsw"
  const localdir = "E:\temp"
  const remotedir = "ron"
  const recvfile = "welcome.htm"

  Dim WSHShell, fso, ts, tmp, fname, result, oExec, stdout

  Set WSHShell = wscript.CreateObject("WScript.Shell")
  Set fso = CreateObject("Scripting.FileSystemObject")
  tmp = fso.GetSpecialFolder(TemporaryFolder)              'get windows temp folder path
  fname = fso.Buildpath(tmp, "ftpcmd.ftp")                   

  if fso.folderexists(localdir) then
  else
'    msgbox localdir & vbcrlf & " folder does not exist",vbCritical, "Error"
    err.raise vbObjectError + 1069,,localdir & " folder does not exist"
    wscript.quit
  end if
  fso.CreateTextFile fname, True, True
  Set result = fso.GetFile(fname)
  Set ts = result.OpenAsTextStream(ForWriting)
  ts.writeline "open " & ftpsite
  ts.writeline uname
  ts.writeline psw
  ts.writeline "lcd " & chr(34) & localdir & chr(34)
  ts.writeline "cd " & chr(34) & remotedir & chr(34)
  ts.writeline "recv " & chr(34) & recvfile & chr(34)
  ts.writeline "disconnect"
  ts.writeline "quit"
  ts.Close

  Set oExec = WshShell.Exec("ftp.exe -s:" & chr(34) & fname & chr(34))
  If Not oExec.StdErr.AtEndOfStream Then
    err.raise vbObjectError + 1070,,oExec.StdErr.ReadAll
    wscript.quit
  End If

  stdout = ""
  Do While Not oExec.StdOut.AtEndOfStream
     stdout = stdout & oExec.StdOut.ReadLine() & vbcrlf
  Loop

' leave this commented out if running in a scheduled task  
'  wscript.echo stdout
  
  On Error Resume Next
  fso.deletefile fname
  Set fso = Nothing
  Set WSHShell = Nothing
  wscript.quit

THE ABOVE SCRIPT HAS SECURITY VULNERABILITIES, DO NOT USE!!!

scriptoutput.jpg (12.48 KiB) Viewed 43762 times

[Ravikr1]

Hey, Thanks for the information and code. I'll surely try this.

[botg]

Don't use that script, it has trivially exploitable security vulnerabilities.

[juricta]

Gotcha on the security issue. Won't be trying it after all.

Thanks