While Googling around the other day I noticed that lots of people are searching for a way to FTP files with VBScript. After looking for a while at the solutions to do this, it was clear that no real easy, free way of FTP uploading and downloading files was currently available. There are downloadable components that would present a programmable API. But these are costly, and you’d have to install them. So seeing the need I decided to whip up a couple of functions that would preform some basic uploading and downloading.
Pretty straight forward, you have to supply the credentials to connect to the machine, the IP address or DNS name for the machine and then the source and destination locations. Example of syntax:
Wscript.Echo FTPDownload("192.168.1.1", "domain\user", "password", "C:\", "\", "*")
When using the download function, if you don’t specify a location it will default to the working directory of the script. If for some reason there is a problem transferring the file the functions will return the error message. If they are successful, they will return “true”.
Update: I just made some corrections to this per the suggestions of some emails and comments I received. I retested this script, since it’s been quite some time since I’ve had the chance to use it, and it’s working like a charm. Please continue to report any issues you might have and thank you for your feedback.
Function FTPUpload(sSite, sUsername, sPassword, sLocalFile, sRemotePath)
'This script is provided under the Creative Commons license located
'at http://creativecommons.org/licenses/by-nc/2.5/ . It may not
'be used for commercial purposes with out the expressed written consent
'of NateRice.com
Const OpenAsDefault = -2
Const FailIfNotExist = 0
Const ForReading = 1
Const ForWriting = 2
Set oFTPScriptFSO = CreateObject("Scripting.FileSystemObject")
Set oFTPScriptShell = CreateObject("WScript.Shell")
sRemotePath = Trim(sRemotePath)
sLocalFile = Trim(sLocalFile)
'----------Path Checks---------
'Here we willcheck the path, if it contains
'spaces then we need to add quotes to ensure
'it parses correctly.
If InStr(sRemotePath, " ") > 0 Then
If Left(sRemotePath, 1) <> """" And Right(sRemotePath, 1) <> """" Then
sRemotePath = """" & sRemotePath & """"
End If
End If
If InStr(sLocalFile, " ") > 0 Then
If Left(sLocalFile, 1) <> """" And Right(sLocalFile, 1) <> """" Then
sLocalFile = """" & sLocalFile & """"
End If
End If
'Check to ensure that a remote path was
'passed. If it's blank then pass a "\"
If Len(sRemotePath) = 0 Then
'Please note that no premptive checking of the
'remote path is done. If it does not exist for some
'reason. Unexpected results may occur.
sRemotePath = "\"
End If
'Check the local path and file to ensure
'that either the a file that exists was
'passed or a wildcard was passed.
If InStr(sLocalFile, "*") Then
If InStr(sLocalFile, " ") Then
FTPUpload = "Error: Wildcard uploads do not work if the path contains a " & _
"space." & vbCRLF
FTPUpload = FTPUpload & "This is a limitation of the Microsoft FTP client."
Exit Function
End If
ElseIf Len(sLocalFile) = 0 Or Not oFTPScriptFSO.FileExists(sLocalFile) Then
'nothing to upload
FTPUpload = "Error: File Not Found."
Exit Function
End If
'--------END Path Checks---------
'build input file for ftp command
sFTPScript = sFTPScript & "USER " & sUsername & vbCRLF
sFTPScript = sFTPScript & sPassword & vbCRLF
sFTPScript = sFTPScript & "cd " & sRemotePath & vbCRLF
sFTPScript = sFTPScript & "binary" & vbCRLF
sFTPScript = sFTPScript & "prompt n" & vbCRLF
sFTPScript = sFTPScript & "put " & sLocalFile & vbCRLF
sFTPScript = sFTPScript & "quit" & vbCRLF & "quit" & vbCRLF & "quit" & vbCRLF
sFTPTemp = oFTPScriptShell.ExpandEnvironmentStrings("%TEMP%")
sFTPTempFile = sFTPTemp & "\" & oFTPScriptFSO.GetTempName
sFTPResults = sFTPTemp & "\" & oFTPScriptFSO.GetTempName
'Write the input file for the ftp command
'to a temporary file.
Set fFTPScript = oFTPScriptFSO.CreateTextFile(sFTPTempFile, True)
fFTPScript.WriteLine(sFTPScript)
fFTPScript.Close
Set fFTPScript = Nothing
oFTPScriptShell.Run "%comspec% /c FTP -n -s:" & sFTPTempFile & " " & sSite & _
" > " & sFTPResults, 0, TRUE
Wscript.Sleep 1000
'Check results of transfer.
Set fFTPResults = oFTPScriptFSO.OpenTextFile(sFTPResults, ForReading, _
FailIfNotExist, OpenAsDefault)
sResults = fFTPResults.ReadAll
fFTPResults.Close
oFTPScriptFSO.DeleteFile(sFTPTempFile)
oFTPScriptFSO.DeleteFile (sFTPResults)
If InStr(sResults, "226 Transfer complete.") > 0 Then
FTPUpload = True
ElseIf InStr(sResults, "File not found") > 0 Then
FTPUpload = "Error: File Not Found"
ElseIf InStr(sResults, "cannot log in.") > 0 Then
FTPUpload = "Error: Login Failed."
Else
FTPUpload = "Error: Unknown."
End If
Set oFTPScriptFSO = Nothing
Set oFTPScriptShell = Nothing
End Function
Function FTPDownload(sSite, sUsername, sPassword, sLocalPath, sRemotePath, _
sRemoteFile)
'This script is provided under the Creative Commons license located
'at http://creativecommons.org/licenses/by-nc/2.5/ . It may not
'be used for commercial purposes with out the expressed written consent
'of NateRice.com
Const OpenAsDefault = -2
Const FailIfNotExist = 0
Const ForReading = 1
Const ForWriting = 2
Set oFTPScriptFSO = CreateObject("Scripting.FileSystemObject")
Set oFTPScriptShell = CreateObject("WScript.Shell")
sRemotePath = Trim(sRemotePath)
sLocalPath = Trim(sLocalPath)
'----------Path Checks---------
'Here we will check the remote path, if it contains
'spaces then we need to add quotes to ensure
'it parses correctly.
If InStr(sRemotePath, " ") > 0 Then
If Left(sRemotePath, 1) <> """" And Right(sRemotePath, 1) <> """" Then
sRemotePath = """" & sRemotePath & """"
End If
End If
'Check to ensure that a remote path was
'passed. If it's blank then pass a "\"
If Len(sRemotePath) = 0 Then
'Please note that no premptive checking of the
'remote path is done. If it does not exist for some
'reason. Unexpected results may occur.
sRemotePath = "\"
End If
'If the local path was blank. Pass the current
'working direcory.
If Len(sLocalPath) = 0 Then
sLocalpath = oFTPScriptShell.CurrentDirectory
End If
If Not oFTPScriptFSO.FolderExists(sLocalPath) Then
'destination not found
FTPDownload = "Error: Local Folder Not Found."
Exit Function
End If
sOriginalWorkingDirectory = oFTPScriptShell.CurrentDirectory
oFTPScriptShell.CurrentDirectory = sLocalPath
'--------END Path Checks---------
'build input file for ftp command
sFTPScript = sFTPScript & "USER " & sUsername & vbCRLF
sFTPScript = sFTPScript & sPassword & vbCRLF
sFTPScript = sFTPScript & "cd " & sRemotePath & vbCRLF
sFTPScript = sFTPScript & "binary" & vbCRLF
sFTPScript = sFTPScript & "prompt n" & vbCRLF
sFTPScript = sFTPScript & "mget " & sRemoteFile & vbCRLF
sFTPScript = sFTPScript & "quit" & vbCRLF & "quit" & vbCRLF & "quit" & vbCRLF
sFTPTemp = oFTPScriptShell.ExpandEnvironmentStrings("%TEMP%")
sFTPTempFile = sFTPTemp & "\" & oFTPScriptFSO.GetTempName
sFTPResults = sFTPTemp & "\" & oFTPScriptFSO.GetTempName
'Write the input file for the ftp command
'to a temporary file.
Set fFTPScript = oFTPScriptFSO.CreateTextFile(sFTPTempFile, True)
fFTPScript.WriteLine(sFTPScript)
fFTPScript.Close
Set fFTPScript = Nothing
oFTPScriptShell.Run "%comspec% /c FTP -n -s:" & sFTPTempFile & " " & sSite & _
" > " & sFTPResults, 0, TRUE
Wscript.Sleep 1000
'Check results of transfer.
Set fFTPResults = oFTPScriptFSO.OpenTextFile(sFTPResults, ForReading, _
FailIfNotExist, OpenAsDefault)
sResults = fFTPResults.ReadAll
fFTPResults.Close
'oFTPScriptFSO.DeleteFile(sFTPTempFile)
'oFTPScriptFSO.DeleteFile (sFTPResults)
If InStr(sResults, "226 Transfer complete.") > 0 Then
FTPDownload = True
ElseIf InStr(sResults, "File not found") > 0 Then
FTPDownload = "Error: File Not Found"
ElseIf InStr(sResults, "cannot log in.") > 0 Then
FTPDownload = "Error: Login Failed."
Else
FTPDownload = "Error: Unknown."
End If
Set oFTPScriptFSO = Nothing
Set oFTPScriptShell = Nothing
End Function
Thank you very much for taking the time to “whip” this up. I have been trying to use it in an application but have hit a problem and research would seem to show that there is no solution using the Windows built-in http://ftp.exe.
The issue is that I need to use passive mode and, even though you could add ‘quote pasv’ to the command script, http://ftp.exe does not support passive mode.
Thank you very much for the trouble but this is not a workable solution for using passive mode.
Regards
I have used WinSCP in the past as a replacement for the limited functionality of the built in ftp client. You might see if that will work for your purposes.
Don’t know what happened there. There shouldn’t be http:// in front of the “ftp.exe” as I was referring to the command.
After more than 15 hours of almosts and no-go’s, I’ve hit finally hit gold. THANK YOU!
Can you give an example of how to use this script for FTP Upload file?
how to call the function?
ty
How can I contact you for commercial usage?
Torsten
natemrice@gmail.com
can you please explain to me how to call the following function?
Wscript.Echo FTPUpload()
Thank you so much, saved me heaps of time and worked straight away.
Usage for calling UploadFTP is:
Wscript.Echo FTPDownload(“192.168.1.1”, “domain\user”, “password”, “C:\file.txt”, “\remote_dir\”)
Oops my last comment was a bit premature… There’s an issue in the script when input file contains spaces e.g. “C:\Some Folder\file.txt”.
The following statement returns false-
oFTPScriptFSO.FileExists(sLocalFile)
And therefore the function exits with value “Error: File Not Found.”
The reason seems that it doesn’t like the string to have quotation marks in it. The quotation marks were added previously by the section commented –
‘Here we willcheck the path, if it contains
‘spaces then we need to add quotes to ensure
‘it parses correctly.
So I’ve moved this section to after the path checks and everything seems to work fine.
The path checks look like this now:
‘———-Path Checks———
‘Check to ensure that a remote path was
‘passed. If it’s blank then pass a “\”
…
‘Check the local path and file to ensure
‘that either the a file that exists was
‘passed or a wildcard was passed.
…
‘Here we willcheck the path, if it contains
‘spaces then we need to add quotes to ensure
‘it parses correctly.
…
‘——–END Path Checks———
I saw your comment, but I found it too complicated (I am not very good at programming). What I did is make a copy of the file and save it in the C: drive, so there is no possible way there could be a space. However, the macro is not permitted to put files inside the C: drive, so I first made a folder. To not get errors if you have to upload the file many times, we have to check if the directory exists, or doesn’t (this is the if statement). If it doesn’t exist, it makes a folder; if it does, it just goes on to create a SaveCopyAs.
———————-
Public Sub DuplicateFile()
If Len(Dir(“C:\Folder\YourFile”, vbDirectory)) = 0 Then
MkDir “C:\Folder”
End If
ActiveWorkbook.SaveCopyAs “C:\Folder\TempNameOfFile” (or it can be your normal file’s name)
End Sub
———————-
Now, this function is called before the script for the upload/download (Wscript.Echo FTPDownload(“192.168.1.1”, “domain\user”, “password”, “C:\file.txt”, “\remote_dir\”)
And of course, the “C:\file.txt” should be the “C:\Folder\TempNameOfFile”.
What do you think about my solution?
okay this script is the first uploaded script worked fine for me the problem its just upload the file name and show unknown error
any idea about how to fix this
Thnx
Hello, I don’t know how long you make me win but I just want to give you BIG thanks…
Very nice job !
If you want to use these scripts to access a server on the web behind a box, you must use the passive mode.
Unlike a widely held idea http://ftp.exe works very well in passive mode. Just add in the elementary commands, the command ‘quote pasv’ (here : sFTPScript = sFTPScript & “quote pasv” & vbCRLF, behind : sFTPScript = sFTPScript & “binary” & vbCRLF)
You must also remember to allow ‘C:\windows\System32\ftp.exe’ into your firewall.
It is also necessary to modify the error detection at the end of the functions, because the numbers and the comments of the answers can vary according to the server, especially if, like me, you use a language other than English.
Finally, and this is a personal opinion, I find that the Hungarian notation over-use in the scripts is harmful to the reading of the code, why not use the Csharp or Java – Best Practices – coding guidelines.
I am french, excuse me if my English is sometimes incorrect.
how to set auto upload from ftp client to ftp server and rename the folder in the server directory with the script?
thank you