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