FTP from VB/Excel Macro

You could probably use a shell command if you can execute a DOS Command to do it.

Someone else may know if this is allowed from a macro

I have a project to do this(In VB, you could easily transform to VBA). Post your email and i will send it to you.

I've done this with an Excel macro, the code is below:

Private Sub CommandButton1_Click()
    'Save the Excel Spreadsheet as a text file
    Sheet1.SaveAs "C:\NETVIEW.ESCON2.TXT", xlText
   
    'Build the script file for the ftp process
    Open Application.Path & "\ftp.prm" For Output As #1
        Print #1, "open 255.255.255.255"
        Print #1, Me.txtUserID.Text
        Print #1, Me.txtPassword.Text
        Print #1, "send "; "C:\myfile.txt"
    Close (1)
    'FTP the file
    Shell ("ftp.exe -s:" & Application.Path & "\ftp.prm")
    'Kill the script file to erase the user id and password
    Kill Application.Path & "\ftp.prm"
    Unload Me
End Sub


This prompts the user for a user id and password, and then builds a parm file for the ftp process to use on the the users desktop.  This was the simpilist way I found to automatically execute the ftp of a file the host system.

My email is jsayre@us.dhl.com

Just sent it!

jjmartin,
I have posted a similar example on an old question...
I would like to "decorate a little", may I?
Private Sub SendFTPFile(sServer as string, FileName as string)
   
   'Build the script file for the ftp process
   Open Application.Path & "\ftp.prm" For Output As #1
       Print #1, "open " & sServer
       Print #1, Inputbox("Please Type UserName")
       Print #1, Inputbox("Please Type Password")
       Print #1, "send " & filename
   Close (1)
   'FTP the file
   Shell ("ftp.exe -s:" & Application.Path & "\ftp.prm")
   'Kill the script file to erase the user id and password
   Kill Application.Path & "\ftp.prm"
   Unload Me
End Sub

This did not work:

Open Application.Path & "\ftp.prm" For Output As #1
      Print #1, "open nnn.nn.nn.com"
      Print #1, "prism"
      Print #1, "letmein"
      Print #1, cdPath
      Print #1, "send " & strTxtBox5
 Close (1)

which becomes something like this:

open "text name of host"
Valid User name
Valid Password
cd /eureka/dev/
send c:\TMP\SyncName.sql


It did kick off FTP, but no files were created.  What am I missing? I also
got rid of the cd, did not work

Prm file is created as :

C:\Program Files\Microsoft Office\Office\ftp.prm

One problem with the suggested code is that the shell command is not synchronous. This means that it will run the next line of code (delete the script) before the upload is complete. You will need to use another function that will run the script synchronously, like ShellWait, which can be added as per the following link

http://www.geocities.com/CapeCanaveral/6740/shellwat.zip

Zaphod.

I think what we are missing is a vbcrlf constant on every line of the script, like:

process
  Open Application.Path & "\ftp.prm" For Output As #1
      Print #1, "open " & sServer & vbcrlf
      Print #1, Inputbox("Please Type UserName") & vbcrlf
      Print #1, Inputbox("Please Type Password") & vbcrlf
      Print #1, "send " & filename & vbcrlf

  Close #1

The Print # function puts each one on a new line automatically and in dowlands previous post he mentioned that the output was correctly line formatted.

What I would suggest is that you remove the line that deletes the script and see if that works.

If it doesn't then try to run the script manually form a command line:

ftp -s:ftp.prm

is the syntax if I remember correctly.

Let us know how that goes...

Zaphod.

All that  vbCrLf  did was add one extra line between the commands.  I think the
print command puts line feed or return on.

Z_B's suggestion that I try to run the script from the command line worked.
So the script seems to be valid.  I also deleted the "Kill", but still nothing
has worked from the VBA

Little modification:
(Note extension
Open Application.Path & "\ftp.bat" For Output As #1
     Print #1, "ftp " & sServer
     Print #1, "userid")
     Print #1, "Passwword")
     Print #1, "send " & filename
     Print #1, "close "
     Print #1, "quit"
 Close #1

shell "command.com /c " & Application.Path & "\ftp.bat"