PDA

View Full Version : How to: Get file from FTP server by using Mac Excel?



Knud
11-20-2008, 07:47 AM
Hi All,

I need to retrieve a file from a FTP server (I don't have access any other way). I have tried different things, but I can't get it to work (see my code below). I get an "Error 68 - Device unavailable".

Sub RetrieveFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
'If an error occurs go to code in the bottom
On Error GoTo Err_Handler
lStr_Dir = ThisWorkbook.Path
lInt_FreeFile01 = FreeFile
lInt_FreeFile02 = FreeFile
'
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, "xxx.xx.xx.xxx"
Print #lInt_FreeFile01, "account_name" 'I got this from IT-department
Print #lInt_FreeFile01, "account_password" 'I got this from IT-department
Print #lInt_FreeFile01, "/out" 'I got this from IT-department
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "recv \purchaseorders.txt " & ThisWorkbook.Path & "\purchaseorders.txt"

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


My code follows the example on the webpage: http://www.bygsoftware.com/Excel/VBA/ftp.htm

The problem is I'm not used to Mac Excel, normally I use the PC-based/windows-based MS Excel and I can not setup up the macro on a PC, as I don't have access with the PC to the server.

Can anybody help me? Please!!!:help

Regards,
Knud