VBA代码在调用外壳时不执行FTP下载或写入日志

Question

我使用 ray 的答案开发了一个过程，该过程将脚本文件写入目录，然后在 Shell 中运行脚本。

该代码不会引发任何错误，但不会下载文件或将 FTP 日志写入我指定的文本文件。

起初的问题是我试图为目标文件和 ftp 日志指定一个目录，正如 jacouh 在这里指出的那样，因此脚本不会在 DOS 中运行。

我相信，我纠正了该错误，现在可以使用 Shell 命令中使用的确切命令行在 DOS 窗口中运行脚本。但是，它仍然无法在 VBA 中运行。

两个问题：

1. 有没有办法在VBA中查看日志文件？
2. 有人看到我可能做错了什么吗？

这是代码：

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:UsersWWTP ComputerDocumentsDMR'sftpDownloads"
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

谢谢。

Answer 1

如果Shell部分是唯一的问题，您可以做的是创建一个批处理文件来执行您的FTP命令。像这样：

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"