how to use FTP in VBA

[ymeyaw]

dear all experts,

I need your help on using FTP in VBA.I had a file in unix which stored
at
/home/daniel/dailyreport.txt. I wish to use VBA code to import this txt
file and put into excel worksheet.

The daily report.txt is somthing like this:
name ip
david 100.24.56.78
richard 100.33.44.56


How can this be done? Currently i had to use another ftp software just
to do that.

 

[Leith Ross]

Hello Ymeyaw,

Here is some code that should help...


Code:
--------------------

'Code from http://www.bygsoftware.com/Excel/VBA/ftp.htm

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 yoursite.com"
Print #lInt_FreeFile01, "account_name"
Print #lInt_FreeFile01, "account_password"
Print #lInt_FreeFile01, "cd source/uploads"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "send " & ThisWorkbook.Path & "\Picture.gif targetdir/Picture.gif"

'' To receive a file, replace the above line with this one
''Print #lInt_FreeFile01, "recv \Picture.gif " & ThisWorkbook.Path & "\Picture.gif"

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

 

[ymeyaw]

hi Leith Ross,

Thanks for ur greatest help.This is really helpful. I did studied the
code below, there is one line which i dont really know why the owner
what to do this way?

Print #lInt_FreeFile02, "Echo ""Complete"" > " & strDirectoryList &
".out"