将文件从FTP下载到共享驱动器

Question

我有一个Excel宏，可以将文件从WinSCP FTP客户端拉到我的本地C:\users\documents文件夹，但是，当我将文件路径更改为G:驱动器中的文件夹时，该shell不再自动运行由我的代码创建的脚本。我的代码创建了脚本，然后我必须手动去cmd并运行"ftp -s:" & sWorkingDirectory & FTP_BATCH_FILE_NAME以使文件从FTP传输到G:。

这是我的代码到目前为止，有没有人有什么我应该做的建议？

Option Explicit 
Const FTP_ADDRESS = "ip address" 
Const FTP_USERID = "user" 
Const FTP_PASSWORD = "pw" 

Sub GetFile() 
    If Not GetFtpFile_F() Then 
     MsgBox "Error getting ftp file" 
    Else 
     MsgBox "Received" 
    End If 
End Sub 


Function GetFtpFile_F() As Boolean 
    Dim rc As Integer 
    Dim iFreeFile As Integer 
    Dim sFTPUserID As String 
    Dim sFTPPassWord As String ' 
    Dim sWorkingDirectory As String 
    Dim sFileToGet As String 

    Const FTP_BATCH_FILE_NAME = "PULLSCRIPT" 

    GetFtpFile_F = False 

    sWorkingDirectory = "G:\...\...\folder\" 
    sFileToGet = "file." & Format(Now(), "yyyyMMdd") 

    On Error GoTo FtpNECAFile_EH 

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

    '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, "cd " & FTP_USERID 
    Print #iFreeFile, "mget " & sFileToGet 
    Print #iFreeFile, "y" 
    Print #iFreeFile, "quit" 
    Close #iFreeFile 

    'Shell command the FTP file to the server 
    Shell "ftp -s:" & sWorkingDirectory & FTP_BATCH_FILE_NAME 

    GetFtpFile_F = True 

    GoTo FtpNECAFile_EX 

FtpNECAFile_EH: 
    MsgBox "Err", Err.Name 

FtpNECAFile_EX: 

    Exit Function 
End Function 

Answer 1

只是做更正如下图所示 它的工作对我来说，希望能为你工作，以及

Print #iFreeFile, "open " & "111.111.1111.111" 
    Print #iFreeFile, "[email protected]" 
    Print #iFreeFile, "********" 
    Print #iFreeFile, "cd " & "/data" 
    Print #iFreeFile, "lcd D:\mydata\test" 
    Print #iFreeFile, "mget *.csv" 
    Print #iFreeFile, "y" 
    Print #iFreeFile, "close" 
    Print #iFreeFile, "quit" 
    Close #iFreeFile 
    or 
    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, "cd " & FTP_USERID 

    Dim FTP_mypath As string 
    FTP_mypath ="D:\mydata\test" 
    Print #iFreeFile, "lcd " & FTP_Mypath 
    Print #iFreeFile, "mget " & sFileToGet 
    Print #iFreeFile, "y" 
    Print #iFreeFile, "quit" 
    Close #iFreeFile