Excel VBA Upload with FTP

[Christiaan]

Hello every one!

Using Excel VBA I am trying to upload files to a server.
In order to do so the macro creates a command-file called 'FTP_batch.txt' and when the subroutine finishes, it kills that file again.

When I run the command prompt and copy/ paste the commands from the 'FTP_batch.txt' manually, everything works like a charm. However, when the wsShell object runs it, nothing happens... All file locations are generated correctly. What am I missing here?!

This is the code I have (I changed private information).

Sub FTPupload2()


Dim LocalLocation As String
Dim LocalUser As String
Dim FTPfilePath As String
Dim FTPserver As String
Dim FTPuser As String
Dim FTPpass As String
Dim wsShell As Object


LocalUser = Worksheets("References").Range("A6").Value
LocalLocation = ("""C:/users/" & LocalUser & "/Documents/Client Files/Upload Data Files/FTP_Test""")
FTPfilePath = (Environ("userprofile") & "\Documents\Client Files\FTP_batch.txt")


FTPserver = "192.68.1.z26"
FTPuser = "ftpUser"
FTPpass = "ftpUser"


Open FTPfilePath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 


'Print the ftp-commands
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "user " & FTPuser
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , FTPpass
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "cd //"
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "cd ""raw_data_01/data/Client/FTP_Test"""
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "lcd "
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "lcd " & LocalLocation
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "ascii"
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "put UploadTest5.txt"
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "get *.txt"
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "close"
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "quit"
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 


Set wsShell = CreateObject("wscript.shell")
wsShell.Run "ftp -n -i -s: " & LocalLocation & "\FTP_batch.txt" & FTPserver, vbMaximizedFocus, True


Kill FTPfilePath


End Sub

This is what the FTP_batch.txt looks like:

user ftpUser
ftpUser
cd //
cd "raw_data_01/data/Client/FTP_Test"
lcd 
lcd "C:/users/Administrator/Documents/Client Files/Upload Data Files/FTP_Test"
ascii
put *.txt
get *.txt
close
quit

Any suggestions greatly appreciated!

 

[Norie]

What happens if you output this string,

"ftp -n -i -s: " & LocalLocation & "\FTP_batch.txt" & FTPserver

to the immediate window, copy it, open a CLI, paste the copied text and then press enter?

i.e. run the above at the command line.

PS Shouldn't there be spaces between FTP_batch.txt and the address for the ftp server?

 

[Christiaan]

hey Norie, that gives a compile error in the immediate window: Expected: line number or label or statement or end of statement

 

[Christiaan]

ftp -n -i -s C:/users/administrator/documents/client files/upload data files/ftp_test\ftp_batch.txt 192.168.1.26

So there are all forward / and 1 \... So I changed them all to \ (since it is should be local, right?) and still no go...
ftp -n -i -s: C:\users\administrator\Documents\Client Files\FTP_batch.txt 192.168.1.26

 

[Norie]

To output the string you would use either this directly in the Immediate Window,

?"ftp -n -i -s: " & LocalLocation & "\FTP_batch.txt" & FTPserver

or this in the body of the code.

Debug.Print "ftp -n -i -s: " & LocalLocation & "\FTP_batch.txt" & FTPserver

 

[Christiaan]

Hey Norie. I found the issue(s):
First of all, the local address should contain \ instead of /. The FTP swallows / windows doesn't.
Second the batch file location and name had to be embraced with " ". After those changes it went just beautifully.

Thanks for reaching out though!