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.

 

[hobgoblinvasya]

hi. does this site help?
http://www.bygsoftware.com/Excel/VBA/ftp.htm

 

[ronbowalker]

I read this, but I am not familiar with the files he created....

First, I can do a .txt file, but what are the commands to put in it?

• First is a text file (.txt) that contains the commands processed by FTP,

Second, Not familiar with .bat or what it should have in it.

• Second is a batch file (.bat) that runs the FTP command

Third, .out is out of my grasp as well.

• Third is a marker file (.out) that is created by the batch when FTP has finished processing. When this is created, our VBA code detects it and knows it can then carry on with its tidying up routine.

Is there anything that can clarify these?

Thanks for the reply hobgoblinvasya. It's a place to start.

 

[ronbowalker]

Okay now I see it...
So this will create the 3 files.

Can someone just walk me thru the definitions of these lines of code?

This is a little different coding. Can we start here?

Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile
'' ANW 07-Feb-2003 :
strDirectoryList = lStr_Dir & "\Directory"
'' Delete completion file
If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")
'''''''''''''''''''''
Files I use:
FileOne.xml
FileTwo.xml

File located on:
c:

FTP site folder:
DataFiles

 

[John_w]

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.

 

[ronbowalker]

This is way cool...thanks a million John_w.

I will give this a go.

<TABLE style="WIDTH: 380pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=506 border=0 x:str><COLGROUP><COL style="WIDTH: 380pt; mso-width-source: userset; mso-width-alt: 18505" width=506><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>!REM upload files</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>open ftp.mywebsite.com</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>user ron password</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>lcd "C:\"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>cd DataFiles</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>binary</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>!REM turn off interactive mode</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>prompt</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>mput FileOne*.xml</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 380pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=506 height=17>bye</TD></TR></TBODY></TABLE>

Does this look right? I will do my best to get it to work.

 

[ronbowalker]

I must say..... this works very slick people!

Fast and to the point. The only hitch was that the folder on the server end (DataFiles) did not get the FileOne.xml file. It just went to the server outside the folder. Can this be fixed? DataFolder is where all my files for this are located. Maybe I should have put a \ after the DataFolder entry on the .txt command.....

Thanks for the awsome help John.w.....you are the man.

 

[John_w]

Try:

cd DataFiles/

The forward slash works on a Unix server, possibly also a Windows server.

You initially said that your local files were in C:\Files\XMLFiles\, in which case the lcd command should be:

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.

If you delete or comment out the 'bye' line, the ftp command window will stay open, allowing you to see the commands that have executed, see any errors etc. and type in more ftp commands. E.g. pwd displays the current remote directory. Remember to type 'bye' to log off and end the running VBA code.

 

[ronbowalker]

I am in awwww......

It works with DataFiles/

Thanks again sir, you just made my life a lot better!

 

[jox101]

This is a great help for me. Thank you very much.

I just want to clarify how the username/password will be place as variable since it is written in the notepad and not in the macro?

Thanks in advance.