VBA - uploading to FTP

[cmhoz]

So... I think I'm half decent with VBA, but I'm a novice at using Shell, creating .bat files, etc.

I'm using the code below to try and upload a file to an FTP... but it's getting stuck in the 'do while' loop.

I've spent a few hours playing around, and learning about syntax, etc. But I'm in over my head. Is there some good reason it would get stuck in the loop? Or some reason why uploading this way would take significantly longer than if I just dumped the file onto the FTP using something like Filezilla (which is the length of time on which I'm basing my assumption that the code is stuck and not just still working)?

Any ideas??

_________________________________
<code>

Private Sub UserForm_Activate()

Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer
Dim repid As String


'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, "open ftp.mysite.net.au"
Print #lInt_FreeFile01, "myname"
Print #lInt_FreeFile01, "mypassword"
Print #lInt_FreeFile01, "cd /myfolder"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "put " & ThisWorkbook.FullName
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 Environ("COMSPEC") & " " & strDirectoryList & ".bat", vbHide
'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:

Unload Me
MsgBox "Complete."

Exit Sub

Err_Handler:
Unload Me
MsgBox "Error : " & Err.Number & vbCrLf & "Description : " & Err.Description, vbCritical
Resume bye

End Sub


</code>

 

[xenou]

Don't know if these are helpful or not but some related posts:
http://www.mrexcel.com/forum/showthread.php?t=8084
http://www.mrexcel.com/forum/showthread.php?t=419946

 

[John_w]

Use:

    Dim pid As Double
    pid = Shell(Environ("COMSPEC") & " /k " & Chr(34) & strDirectoryList & ".bat" & Chr(34), vbNormalFocus)

whilst developing/debugging your program. Then you can see why the command might be failing. Also notice the double quotes (Chr(34)) around the filename, which you will certainly need if the file path or file name contains spaces. For the same reason you should also put double quotes around the filenames in your .txt, .bat and .out files.

However a better method is the Run method in Windows Script Host - http://msdn.microsoft.com/en-us/library/d5fk67ky(VS.85).aspx, passing True for the WaitOnReturn argument. Then you don't need the .bat and .out files, nor the completion checking loop, just the .txt file containing ftp commands.