I have an Excel worksheet where the user enters certain data, which I want to store in a text file and upload to a Portable using File Transfer Protocol. One site suggested adding a reference to "Microsoft Internet Transfer Control" and then define an "Inet" object to do the File Transfer Protocol. However, I am unable to find a reference with this name in "Tools -> References" in the VB Editor. Does anyone know of a solution for this problem? Thanks in advance.edited Jul 9 at 19:34 Community♦ asked May 26 '10 at 15:58 Anindya 1 Answer Vote count: 5 accepted
Here is a solution I found by doing some Google search -Public Sub FtpSend() 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 Portable 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" End Sub
Original source: http://www.access-programmers.co.uk/forums/showthread.php?t=184692edited May 3 '13 at 0:13 Simon MᶜKenzie answered Jun 1 '10 at 11:16 Anindya
To get started do the following command at a command line:
This will show you the format for the date in that variable on your system. Mine looks like this:
Then, in the bat file, you can use the "substring" and "concatenate" capability to rearrange the pieces anyway you want. So to get YYYYMMDD from my format, I would do:
There are other ways to chop up the formatted date, but this is one of the easiest.
Help on the substring capability can be found via SET /? at a prompt. Just keep in mind the offset is zero based, so the first character is offset 0, not 1.