How to get vba code to read contents of FTP server

[dhoz]

Hi everyone,

I am writing a code in vba (Excel) that connects to a ftp server. Everything works fine except when I try to list the contents of the remote directory. I would like my program to read contents of the remote directory to later decide which file to download.

Is there a way to do this? I tried writing the output of the dir or ls command to a text file but didn't succeed. Any suggestions?

Many thanks
D

 

[Chance1234]

what code have you got so far ?

Chance,

F, G + 2MSTG

 

[dhoz]

Here is the code I wrote:

Option Explicit

Sub getFTPFile()
Dim strDirectoryList As String
Dim Str_Dir As String
Dim Int_FreeFile01 As Integer
Dim Int_FreeFile02 As Integer

Dim sPATH As String
Dim sSITE As String
Dim sUSER As String
Dim sPASS As String
Dim sDIR As String
Dim sFILE As String

sPATH = Sheets(1).Cells(10, 4).Value 'local computer path
sSITE = Sheets(1).Cells(7, 4).Value 'ftp server address
sUSER = Sheets(1).Cells(8, 4).Text 'username
sPASS = Sheets(1).Cells(9, 4).Value 'password
sDIR = Sheets(1).Cells(11, 4).Value 'remote directory
sFILE = Sheets(1).Cells(14, 4).Value 'file to download

On Error GoTo Err_Handler
Str_Dir = sPATH
Int_FreeFile01 = FreeFile
Int_FreeFile02 = FreeFile

strDirectoryList = Str_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 #Int_FreeFile01
Print #Int_FreeFile01, "open " & sSITE
Print #Int_FreeFile01, sUSER
Print #Int_FreeFile01, sPASS
Print #Int_FreeFile01, "cd " & sDIR
Print #Int_FreeFile01, "hash"
Print #Int_FreeFile01, "prompt"
Print #Int_FreeFile01, "binary"
Print #Int_FreeFile01, "get " & Str_Dir & "\" & sFILE

Print #Int_FreeFile01, "bye"
Close #Int_FreeFile01

' Create Batch program
Open strDirectoryList & ".bat" For Output As #Int_FreeFile02
Print #Int_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"

Print #Int_FreeFile02, "Echo ""Complete"" > " & strDirectoryList & ".out"
Close #Int_FreeFile02

' Invoke Directory List generator
Shell (strDirectoryList & ".bat"), vbNormalFocus ', 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

 

[Chance1234]

Ah i was thinking of the same code,

have you looked at using wininet ?

faq705-5904
faq705-6533



Chance,

F, G + 2MSTG

 

[PHV]

dir remote-directory local-file
Print a listing of the directory contents in the
directory, remote-directory, optionally placing the output
in local-file.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhoz]

Thanks a lot for your very fast replies. The links seem to point me in the right direction
Many thanks,
D

 

[strongm]

I'd be tempted to use the Microsoft Shell Controls and Automation library to leverage the fact that the Shell knows how to do FTP ...