VBA to FTP file....Grrr....

C

CarpeDiemFL

I have set up an Excel "program" that is sent out to clients. The
enter an order into a specified sheet, and then they click a button t
send the order to us. I have it set up to change the sheet into a CS
txt file, and I've got it set up to send the order to us via e-mail.

I'd like to have the "send" button, instead of sending the txt file vi
e-mail, have it send directly to our ftp site to avoid potentially no
receiving the order if the e-mail system or server is down.

So, basically I'm hoping that (once again) I can beg for some synta
that would send to an ftp site (it does require a user name an
password). All of the other threads and sites have been prett
ambiguous about the solution. Thanks Much!

Jim Carpenter
rookie-wanna-be-XLGur
 
D

DNF Karran

This isn't something I have ever set up but is something I have com
across in an Access Db I maintained a few months ago.

If you look in the windows (or winnt etc) directory for files calle
"ftp" there is a simple dos based ftp programme. This can be controlle
from Excel using Send keys. Other than that, you'll need to find som
ftp software that you can legally give out for free.

i have had a look through the groups and found this
http://www.catalyst.com/

from post

http://tinyurl.com/2tmau

THat may help you more though

Dunca
 
C

CarpeDiemFL

Duncan-

Thanks for your response. Unfortunately, the situation I am in is tha
I'm not really comfortable sending out a program to download onto m
client's machines (the FTP program you had mentioned). Also, I really
really REALLY need to make this as 'user friendly' (read a
"fool-proof") as possible. Is there basic, simple VBA code that I ca
use to FTP the CSV .txt file instead of sending it via e-mail? Thes
are remote computers that are sending my file back to me (my company'
telnet). We do use a user and password login, so I'd need to includ
those parameters in the code).

Thanks for the reply and the help!

Jim Carpente
 
D

DNF Karran

Oh, we might have something there- I might have to do some digging int
what can be done with telnet, winftp etc. The basics will be a sav
command (to the users C:\ drive or let them specify), send via ft
using send keps then ask if user wants to delete data file. It's just
question of what keys to send.

Leve it with me for an hour or so- I assume all of the users are goin
to be on windows PC's?

Dunca
 
D

DNF Karran

Got it!

Open a dos window, start windows ftp, send file, quit app.

Downside here is there is no way to check if file has been sent unles
you run the dir command in ftp then ask the user to tell you whethe
it's there or not. You will also need to pause the code to wait for ft
to process commands allowing lots of spare time for slow ne
connections etc.

The attached has been tested and works on a data upload to geocitie
(yahoo).

The code used is as follows. A userform with password masking has bee
used for security reasons.

MODULE 1

Option Explicit

Sub SendToYahoo()

Dim stCurFile As String

stCurFile = "C:\DataTest.csv"

ActiveWorkbook.SaveAs stCurFile, xlCSV

frmPWInput.Show

SendFile "duncan", frmPWInput.tbPw, stCurFile, 5
"ftp.uk.geocities.com"

Unload frmPWInput

End Sub


MODULE 2


Option Explicit
Option Private Module

Public Sub SendFile(stUserName As String, stPassWord As String
stFilePath As String, _
siWaitTime As Single, stHost As String)

Dim apCMD

apCMD = Shell("cmd", vbNormalFocus)
Application.Wait PauseCode(0.7)
SendKeys "ftp " & stHost & "~", True
Application.Wait PauseCode(2)
SendKeys stUserName & "~", True
SendKeys stPassWord & "~", True
SendKeys "binary~", True
Application.Wait PauseCode(2)
SendKeys "put " & stFilePath & " NewUpload.htm~"
Application.Wait PauseCode(siWaitTime)
SendKeys "bye~"
'sendkeys "Exit~" ''Commented out during testing
End Sub

Private Function PauseCode(WaitTime As Single)
Dim newHour, newMinute, newSecond
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + WaitTime
PauseCode = TimeSerial(newHour, newMinute, newSecond)
End Functio

Attachment filename: ftp.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55090
 
C

CarpeDiemFL

Hi, again, Duncan!

Thanks for your quick response.

Yes, all of the users are using Win machines (varying configurations).

If it helps any, here's the code that I have now that allows them t
send via e-mail:

ActiveWorkbook.SaveAs filename:=Format(Range("a1").Value) & ".txt"
FileFormat:=xlCSV, CreateBackup:=False


ActiveWorkbook.SendMail Recipients:="[email protected]"
Subject:=Format(Range("a1").Value) & "." & Format(Range("c1").Value
"MMDDYY")

The first line names the file something like "4600.txt"
The second line sends the file with a subject line that looks somethin
similar to "4603.051804"

I need to ftp the file into a directory of xx.xx.xxx.xxx/usr/roger

Thanks again!

Jim
 
D

DNF Karran

No problem and if you need any more help setting the system up then
e-mail or send a personal message through the forum.

You can get my mail address from my website by clicking the website
links below my posts.

Duncan
 
Top