ftp using Excel VBA using a Macintosh

[lambda379]

I've used the below VBA code in Windows Excel with success. When I run the same code on a Mac it chokes on the Shell line. Has anybody had any success ftp'ing using VBA on a Mac?

Open "file.txt" For Output As #lInt_FreeFile01
Print #lInt_FreeFile01, "open 'hostname'"
Print #lInt_FreeFile01, "'user'"
Print #lInt_FreeFile01, "'password'"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "send 'file' 'file'
Print #lInt_FreeFile01, "bye"
Close #lInt_FreeFile01
Open "file.scr" For Output As #lInt_FreeFile02
Print #lInt_FreeFile02, "ftp -s:file.txt"
Print #lInt_FreeFile02, "Echo ""Complete"" > " & "file.out"
Close #lInt_FreeFile02
Shell ("file.scr"), vbMinimizedNoFocus
'Wait for completion
Do While Dir("file.out") = ""
DoEvents
Loop

 

[mgl]

You need excel 2011 for the following to work.

Basic principle
1) Use vba to call applescript (using the command "macscript")
2) Let the applescript call shell (terminal)
3) Use the terminal command "curl" to call the ftp.

Maybe there is an easier way. If so i have not found it.

This macro gets a file from server.
I dont have a ftp-server to test the appropriate alterations to make it put, but you can edit it yourself. (try running "man curl" in terminal to get the right command and syntax)

The reason for the relative complexity of this macro is that it had to be distributed across several computers, so the local path couldn't be hardcoded.

Sub GetFileFromFtpMac()
''Get file from ftp-server and place it in same folder as excel-file

'Declarations
Dim ScriptToRun As String
Dim UserPath As String
Dim PathtoHD As String
Dim Login As String
Dim Password As String
Dim FTPpathtofile As String
Dim Localfilename As String

'Variables
'Enclose all in ""
Login = "Login"
Password = "Password"
FTPpathtofile = "ftp://1.1.1.1/folder/ftpfile.file"
Localfilename = "localfilename.file"

'Get path to excel-file
'Alternatively UserPath can be hardcoded
PathtoHD = MacScript("path to startup disk as string")
UserPath = Right(ThisWorkbook.Path, Len(ThisWorkbook.Path) - Len(PathtoHD)) 'Path to excelfile
UserPath = Replace(UserPath, ":", "/") 'POSIX the path
UserPath = "/" & UserPath & "/" & Localfilename ' Add prefix and filename

'construct applescript call
ScriptToRun = "tell application " & Chr(34) & "Finder" & Chr(34) & " to do shell script " & Chr(34) & "curl -u " & Login & ":'" & Password & "' " & FTPpathtofile & " -o " & Chr(34) & " " & Chr(38) & " quoted form of " & Chr(34) & UserPath & Chr(34)

'run applescript
MacScript (ScriptToRun) 'Run Curl command in terminal via applescript...

End Sub

And do post back with your solution!