0

I used ray's answer to develop a procedure that writes a script file to a directory and then runs the script in a Shell.

The code throws no errors but does not download the file or write the FTP log to the text file I specify.

At first the problem was that I tried to specify a directory for the target file and ftp log as pointed out here by jacouh, so the script would not run in DOS.

I corrected that error, I believe, and can now run the script in a DOS window using the exact command line that is used in the Shell command. However, it still does not run in VBA.

Two questions:

  1. Is there a way to see the log file in VBA?
  2. Anyone see something obvious I might have done wrong?

Here is the code:

Function getFilesViaFTP(fileToGet As String, fileToWrite As String) As Boolean
Dim rc As Integer
Dim iFreeFile As Integer
Dim sFTPUserID As String
Dim sFTPPassWord As String
Dim sWorkingDirectory As String
Dim sFtpLogFile As String

Dim myCurrentDirectory As String

Const FTP_BATCH_FILE_NAME = "myFTPscript.txt"
Const INCREASED_BUFFER_SIZE = 20480

Dim fileNameForDownload As String
fileNameForDownload = fileToWrite

Dim fullGetCommand As String
Dim fullShellCommand As String

myCurrentDirectory = CurDir()

getFilesViaFTP = False

sWorkingDirectory = "K:\Users\WWTP Computer\Documents\DMR's\ftpDownloads\"

sFtpLogFile = "ftp_Log" & "_" & year(Date) & "_" & month(Date) & "_" & day(Date) & ".txt"


On Error GoTo EncounteredErrorInProc

'Kill FTP process file if it exists
If Dir(sWorkingDirectory & FTP_BATCH_FILE_NAME) <> "" Then
    Kill sWorkingDirectory & FTP_BATCH_FILE_NAME
End If

If Dir(sWorkingDirectory & sFtpLogFile) <> "" Then
    Kill (sWorkingDirectory & sFtpLogFile)
End If

Dim i As Integer
Dim whereIsDot As Integer
i = 1

'Change target file name if one already exists
Do While Dir(sWorkingDirectory & fileNameForDownload) <> ""
    whereIsDot = InStr(fileToWrite, ".")
    fileNameForDownload = Left(fileToWrite, whereIsDot - 1) & "_" & i & ".txt"
    Debug.Print fileNameForDownload
Loop

fullGetCommand = "get " & fileToGet & " " & fileNameForDownload

Debug.Print fullGetCommand
Debug.Print sFtpLogFile

'Create FTP process file
iFreeFile = FreeFile
Open sWorkingDirectory & FTP_BATCH_FILE_NAME For Output As #iFreeFile
Print #iFreeFile, "open " & FTP_ADDRESS
Print #iFreeFile, FTP_USERID
Print #iFreeFile, FTP_PASSWORD
Print #iFreeFile, "lcd " & sWorkingDirectory
Print #iFreeFile, fullGetCommand
Print #iFreeFile, "quit"
Close #iFreeFile

'Shell command the FTP file to the server
ChDir sWorkingDirectory
Debug.Print "Current Directory = " & CurDir()
fullShellCommand = "ftp -i -w:20480 -s:" & FTP_BATCH_FILE_NAME & ">" & sFtpLogFile
Debug.Print fullShellCommand

Shell fullShellCommand

getFilesViaFTP = True
ChDir myCurrentDirectory
Debug.Print "New Current Directory = " & CurDir()

GoTo NoErrorsInProc

EncounteredErrorInProc:
    MsgBox "Err", Err.Name

 NoErrorsInProc:

Exit Function
End Function

Thanks.

Community
  • 1
  • 1
PhillipOReilly
  • 609
  • 12
  • 28
  • So your specific issue is the part where you execute the `Shell` command? The rest of the code is working perfectly? – L42 Mar 07 '18 at 02:14
  • Appears to be. VBA provides no log when it runs Shell. I will try your suggestion below. Thanks. – PhillipOReilly Mar 07 '18 at 04:18

1 Answers1

0

If the Shell part is the only problem, what you can do is to create a batch file which will execute your FTP command. Something like:

Dim ff As Integer

ff = FreeFile()
Open "runFTP.bat" For Output As #ff
Print #ff, fullshellcommand '/* what you generated in your code */
Close #ff

Shell "runFTP.bat"
L42
  • 19,427
  • 11
  • 44
  • 68