Re: Ftp from within excel

• Previous message: Don Guillett: "Re: solved"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 3 Sep 2004 14:08:31 +0000 (UTC)

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
"Rich Cooper" <richard.cooper@gopds.net> wrote in message
news:OJLGlixeEHA.140@TK2MSFTNGP12.phx.gbl...
> 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
>
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.746 / Virus Database: 498 - Release Date: 31/08/2004

• Previous message: Don Guillett: "Re: solved"
• Messages sorted by: [ date ] [ thread ]