Macro to send file to FTP Error??

[excel_2009]

Hi excel gurus,

I've the following coding that basically moves a file into an FTP server, i've adapated it to my needs but I keep getting an error: Error 5, Description: Invalid procedure call or argument.

here is the coding:

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 serverdev001.testingserver.lonhq"
    Print #lInt_FreeFile01, "test"
    Print #lInt_FreeFile01, "testing"
    Print #lInt_FreeFile01, "cd /home/"
    Print #lInt_FreeFile01, "binary"
    Print #lInt_FreeFile01, "send " & ThisWorkbook.Path & "\image.png targetdir/image.png"

    '' 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

Please can someone help me?

Thank you

 

[excel_2009]

anyone?

 

[Firefly2012]

And on which line do you get this error?

 

[excel_2009]

And on which line do you get this error?

Hello firefly,

Thank you for replying, unfortunately it doesnt specify the line. The error appears and when i select Ok it doesnt debug the coding

 

[Firefly2012]

Comment out this line in your code above and re-run it and then tell me which line errors:

On Error GoTo Err_Handler

 

[excel_2009]

it highlights this line:

    Shell (strDirectoryList & ".bat"), vbHide '', vbMinimizedNoFocus

 

[Firefly2012]

What Operating System are you using?

 

[excel_2009]

Windows 7 (the code i'm using is 7 years old, not sure if I can link to it but here it is: http://www.bygsoftware.com/Excel/VBA/ftp.htm )

 

[Firefly2012]

I'm struggling to see why the code would error. Perhaps try amending the line to:

Shell """" & strDirectoryList & ".bat" & """", vbHide

and then re-running it.

 

[excel_2009]

I've adapt the coding to what you've suggested, the error disappears but unfortunately the file does not move to the ftp server