Hi All -
I've been playing with using VBA to create a .bat file which is then called to get a file off an FTP.
Right now - the VBA creates a .txt file and a .bat file. If I run the .bat file manually once it's been created, it works. BUT - when VBA calls the .bat file, I can see it open and appear to run, but the file I'm telling it to get is not got.
I've tried a few different versions of the Shell, the one I'm using now is the only one I've tried that actually gets the .bat file to open. But maybe I'm missing something?? I've spent many hours on google and searching the mrexcel boards, and I've found lots of stuff that says my code should work and nothing to tell me what's wrong!
Here's what I have... and help would be much appreciated!
I've been playing with using VBA to create a .bat file which is then called to get a file off an FTP.
Right now - the VBA creates a .txt file and a .bat file. If I run the .bat file manually once it's been created, it works. BUT - when VBA calls the .bat file, I can see it open and appear to run, but the file I'm telling it to get is not got.
I've tried a few different versions of the Shell, the one I'm using now is the only one I've tried that actually gets the .bat file to open. But maybe I'm missing something?? I've spent many hours on google and searching the mrexcel boards, and I've found lots of stuff that says my code should work and nothing to tell me what's wrong!
Here's what I have... and help would be much appreciated!
Code:
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 myftp"
Print #lInt_FreeFile01, "myusername"
Print #lInt_FreeFile01, "mypassword"
Print #lInt_FreeFile01, "cd source/myfolder"
Print #lInt_FreeFile01, "binary"
Print #lInt_FreeFile01, "get constructionBG.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
' Call the batch file
Call Shell(Environ$("COMSPEC") & " /c " & strDirectoryList & ".bat", vbNormalFocus)
' Wait for completion
Do While Dir(strDirectoryList & ".out") = ""
DoEvents
Loop
'' 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