I have an Excel worksheet where the user enters certain data, which I want to store in a text file and upload to a server using FTP. Does anyone know of a solution for this problem?
The following VBA code is one solution for this problem.
Public Sub SendFileViaFTP()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long
vPath = ThisWorkbook.Path
vFile = "YourFile.csv"
vFTPServ = "**"
'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "\FtpComm.txt" For Output As #fNum
Print #1, "user ****" ' your login and password"
Print #1, "cd TargetDir" 'change to dir on server
Print #1, "bin" ' bin or ascii file type to send
Print #1, "put " & vPath & "\" & vFile & " " & vFile ' upload local filename to server file
Print #1, "close" ' Close connection
Print #1, "quit" ' Quit FTP program Close
Shell "ftp -n -i -g -s:" & vPath & "\FtpComm.txt " & vFTPServ, vbNormalNoFocus
SetAttr vPath & "\FtpComm.txt", vbNormal
Kill vPath & "\FtpComm.txt"
Note: Some variable must change by youself.