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. 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 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 Software 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.
Each of the variables below will be chosen using DLOOKUP from a table (this part is easy) local Folder=c:\juricta\OLCC - where to store the downloaded FTP file localFileName=inventory.txt - name I want downloaded file to be called on my computer ftpFolder=Liquor (1 sub-directory below root for site)(FULL folder=www.randdcomputers.com/Liquor) ftpFileName=Test3.txt ftpSite=www.randdcomputers.com (FILE WANTED is in www.randdcomputers.com/Liquor) ftpUserName=juricta ftpPassword=Dltbgyd!982
'CODE for download ' Shell variant ' Requires reference to Microsoft Shell Controls and Automation Dim myShell As Shell32.Shell Dim localFolder As Shell32.Folder ' folder on PC Dim ftpFolder As Shell32.Folder
Set myShell = New Shell Set localFolder = myShell.NameSpace("C:\juricta\OLCC") Set ftpFolder = myShell.NameSpace("File Transfer Protocol://juricta:Dltbgydemail@example.com/Liquor/") localFolder.CopyHere ftpFolder.Items.Item("Test3.txt") Anyone have either an explanation or BETTER yet, and easier way to do this??