Ftp from within excel

[Rich Cooper]

Hey i have figured out how to have excel connect to an ftp by creating a
txt file with ftp commands and a batch file to execute them. I want to know
if there is a way to tell excel to keep the communication open and download
a file when it is ready. What happens is excel uploads a file, a process
program on my server extracts data from the csv excel uploaded and puts that
data into a sql server. Then sql generates a new csv and puts it into a
directory for it to be downloaded. I want to know how to tell the ftp
connection when that file is done and then to start download. I can tell it
to download a file but i have to know that file is there and ready for
download.

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")

'' Create text file with FTP commands
Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open ip goes here"
Print #lInt_FreeFile01, "username"
Print #lInt_FreeFile01, "password"
'Print #lInt_FreeFile01, "cd upload"
Print #lInt_FreeFile01, "cd download"
Print #lInt_FreeFile01, "binary"
'Print #lInt_FreeFile01, "send c:\test.txt"
Print #lInt_FreeFile01, "get pop.doc"
Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01

'' Create Batch program
Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
Print #lInt_FreeFile02, "Echo ""Complete"" > " & strDirectoryList &
".out"
Close #lInt_FreeFile02

'' Invoke Directory List generator
Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus
'Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop

Application.Wait (Now + TimeValue("0:00:03"))

'' Clean up files
If Dir(strDirectoryList & ".bat") <> "" Then Kill (strDirectoryList &
".bat")
If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList &
".out")
If Dir(strDirectoryList & ".txt") <> "" Then Kill (strDirectoryList &
".txt")

bye:

Exit Sub

Err_Handler:
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " &
Err.Description, vbCritical
Resume bye

End Sub

 

[Dave Peterson]

I have no idea, but if you don't get an idea here, you may want to try a VB
newsgroup.

Any chance you could just use application.wait or application.ontime?

 

[Dave Peterson]

And you may want to limit your crossposting to just a couple of newsgroups that
would actually apply????

 

[Amedee Van Gasse]

Hey i have figured out how to have excel connect to an ftp by
creating a txt file with ftp commands and a batch file to execute
them. I want to know if there is a way to tell excel to keep the
communication open and download a file when it is ready. What
happens is excel uploads a file, a process program on my server
extracts data from the csv excel uploaded and puts that data into a
sql server. Then sql generates a new csv and puts it into a
directory for it to be downloaded. I want to know how to tell the
ftp connection when that file is done and then to start download. I
can tell it to download a file but i have to know that file is there
and ready for download.

Since you don't know when your new file will be ready, perhaps you
should use the FTP GET command in a loop? Try GETting it until you get
a local file? If your file is not ready yet, your FTP connection won't
do a thing, it will just say that the file is not there.

But wouldn't it be easier to connect to the SQL server directly? Excel
can do that!

 

[Rich Cooper]

how can excel connect directly to sql server?

Since you don't know when your new file will be ready, perhaps you
should use the FTP GET command in a loop? Try GETting it until you get
a local file? If your file is not ready yet, your FTP connection won't
do a thing, it will just say that the file is not there.

But wouldn't it be easier to connect to the SQL server directly? Excel
can do that!

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?
Please don't thank me in advance. Thank me afterwards if it works or
hit me in the face if it doesn't. ;-)

 

[Harry]

Rich,

I have implemented something similar to this by creating a temporary file,
ie when the sql server generated a csv file, this process is completed, the
last thing it would do is create a 0 byte file with a static name. My VBA
program checks if this file exist, if it does it transfer File to the
system, and delete the 0 byte file.
Hari