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]

I'm not sure if it makes a difference, don't see how it would.. but the excel file with the coding is within a folder on my desktop, as is the image etc

 

[Firefly2012]

I'm afraid I have no means to test or verify the ftp code will work as I have no ftp server to load to/from. I suggest you execute the code via hitting F8 in the VBE so you can see each line executed and verify that the code runs through to completion. If it does, then I would assume that it is the ftp coding itself which is failing.

 

[excel_2009]

Hi Firefly, I appreciate your assistance on this topic, i've pressed F8 (held it down) and it stops at this section of the coding :

Shell """" & strDirectoryList & ".bat" & """", vbHide
  '  Wait for completion
    Do While Dir(strDirectoryList & ".out") = ""
        DoEvents
    Loop

or am I doing it wrong?

 

[Firefly2012]

That is 4 individual lines of coding - I suspect it is entering the Do While... Loop and not exiting? So by hitting F8 it repeatedly executes the DoEvents line?

 

[excel_2009]

yeah, if i open up the coding and hold F8 it keeps looping that section and doesn't go any further

 

[Firefly2012]

Have you tried running the ftp commands in a command console to see if what you are trying to execute via the batch command does acually work?

 

[excel_2009]

I'm sorry firefly, how would I do that?

 

[Firefly2012]

All of the commands listed here:

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"

Have you changed any of the above coding from where you got this from originally ie the server name, the "cd /home/" line, the image location?

 

[excel_2009]

yeah i've changed it to my directory, user name password etc:

Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
    Print #lInt_FreeFile01, "open testingserver.test.laq"
    Print #lInt_FreeFile01, "username1"
    Print #lInt_FreeFile01, "password1"
    Print #lInt_FreeFile01, "cd home/testdir"
    Print #lInt_FreeFile01, "binary"
    Print #lInt_FreeFile01, "send " & ThisWorkbook.Path & "\image.gif targetdir/image.gif"

(I've changed the username and pass just to be safe!)

 

[Firefly2012]

Do you have a directory named "targetdir" on your target ftp server?