Macro to send file to FTP site

[ronbowalker]

Hello everyone, I am creating and saving an XML file on my hard drive and then having to move the file/s (or the folder) to my webserver by using Filezilla. Is there a way to move this as part of my VB code while creating and saving the xml file in my code?

My hard drive (c folder : Files\XMLFiles\
Files saved in it are : FileOne.xls, and FileTwo.xls

I am actually moving 12 files right now, I just listed a few for simplicity. And the folder on my FTP site is : DataFiles

I would really appreciate help in making this easier. I am trying to understand the few posts I have seen in regards to this. But they are over my head I am afraid.

 

[DBCox]

Hey guys,

I know I am digging up a really old post here, but I found it while searching for a similar solution.

I am trying to download a file from an FTP site. I have played with it quite a bit, but for some reason, the downloaded file goes to "My Documents" regardless of what lcd I set.

Here is the text file I have created:

!REM upload files
open ftp.mysite.com
user dcox password
!REM turn off interactive mode
prompt
lcd C:\Users\David Cox\Documents\TotalOutdoorsman\Site\Inventory\
get fulfillment-inv.txt


I removed the "bye" so that I could read the prompts. Everything appears correct. It says it downloaded, which it did, but to the wrong directory... Any help will be appreciated.

 

[FormR]

Here is the text file I have created:

!REM upload files
open ftp.mysite.com
user dcox password
!REM turn off interactive mode
prompt
lcd C:\Users\David Cox\Documents\TotalOutdoorsman\Site\Inventory\
get fulfillment-inv.txt

Just a guess but should this line be like this:

lcd "C:\Users\David Cox\Documents\TotalOutdoorsman\Site\Inventory\"

Try:
lcd "C:\Files\XMLFiles\"
double quotes are required if the folder path contains spaces (but does no harm if not). The same applies to the mput file spec.

 

[DBCox]

FormR,

Thank you for the info. I tried quotes and double quotes in every combination I could think of, including:

lcd "C:\Users\David Cox\Documents\TotalOutdoorsman\Site\Inventory\"
lcd ""C:\Users\David Cox\Documents\TotalOutdoorsman\Site\Inventory\""
lcd Chr(34) & "C:\Users\David Cox\Documents\TotalOutdoorsman\Site\Inventory\" & Chr(34)
lcd " Chr(34) C:\Users\David Cox\Documents\TotalOutdoorsman\Site\Inventory\ & Chr(34)"

I find it odd that it stops at Documents. At that point, it has made it past the space between "David" and "Cox". Perhaps it is just defaulting to "Documents"?

It is also odd that it doesn't have a problem finding the batch file in the "C:\Users\David Cox\Documents\TotalOutdoorsman\Site\Inventory\" directory...

It has too be something minor I am overlooking, or something I do not understand about the batch functionality.

Is it possible to do this without the batch file? In other words, can I incorporation the text from the batch file directly into the VBA code?

Any help will be appreciated!

 

[xenou]

It seems from a quick test ftp does not recognize a trailing backslash.

<img alt="screenshot" src="http://northernocean.net/etc/mrexcel/20120706_capture2.jpg" />

 

[DBCox]

It seems from a quick test ftp does not recognize a trailing backslash.

That was it! Thanks for your help!

 

[AlwaysBeProgramming]

No need for .bat and .out files with the code below:

Public Sub Ftp_Test()

    Dim FTPcommand As String
    Dim wsh As Object
    
    FTPcommand = "ftp -n -s:" & Chr(34) & "C:\FTP_commands.txt" & Chr(34)
    Set wsh = CreateObject("WScript.Shell")
    wsh.Run FTPcommand, 5, True
    
End Sub

Use Notepad to create C:\FTP_commands.txt containing:

!REM upload files
open ftp.yourserver.com
user yourUsername yourPassword
lcd "C:"
cd DataFiles
binary
!REM turn off interactive mode
prompt
mput File*.xml
bye

Once you've proved that this works, create C:\FTP_commands.txt on the fly using VBA code (see the posted link), perhaps prompting for username and password with InputBox instead of hardcoding them, and then delete the file afterwards using the Kill function.

I tried your method and it ran without errors. However, the file does not appear on the server it uploads to. The file name includes a random number that should get passed to the program correctly. However, I know this is not the issue because I have looked in every directory using FileZilla. I do not know what the problem is. I have the program automated to create the file and delete it using VBA.

 

[xenou]

Have you tested the commands at a command prompt one at a time (not running a script - just enter each command and check the results for each line?).