FTP Transfer Validate

Elevate08 · May 13th, 2011, 08:50 AM

Hello, I have this program set up so that when I click on a button to process all the data it will then send it over to the FTP to backup and then send to a printer to print it out. I was hoping to find a way to confirm the FTP transfer was successful. I was thinking I could either copy it back over to my local directory and then check if file exists or something. Or if there is some easier way one could validate the transfer it would be appreciated. Following is the code for the transfer.

Code:

Sub do_ftp_transfer()

    Dim fn As String
    
    fn = Range("Config!B3").Value & "ftp_temp.txt"
    
    'MsgBox ("Filename=" & fn)
    
    Set oFso = CreateObject("Scripting.FileSystemObject")
    
    Set oFile = oFso.CreateTextFile(fn, True)
    
    nrow = 17
    
    'put temp file together to do ftp transfer
    Do Until Range("'Config'!B" & CStr(nrow)).Value = "bye"
        oFile.WriteLine Range("'Config'!B" & CStr(nrow)).Value & ""
        nrow = nrow + 1
    Loop
        oFile.WriteLine Range("'Config'!B" & CStr(nrow)).Value & ""
        nrow = nrow + 1
    oFile.Close

    'now that we have constructed file to pass to ftp, make it happen
    Call sFTP(fn)
    
    MsgBox ("Flat file sent via FTP to SD&L.")

    'ftp done - now delete temp file
    oFso.DeleteFile (fn)

End Sub

I'm not sure if code can be placed in the following to do a transfer back or not. but posting just in case it could go in this code.

Code:

Sub sFTP(stSCRFile As String)
Dim stSysDir
    stSysDir = Environ$("COMSPEC")
'MsgBox (stSysDir)
    stSysDir = Left$(stSysDir, Len(stSysDir) - Len(Dir(stSysDir)))
'MsgBox (stSysDir & "ftp.exe -s:" & stSCRFile)
    Call Shell(stSysDir & "ftp.exe -s:" & stSCRFile, vbNormalFocus)
End Sub

Any help is appreciated. Thank You!!!

Shasur · May 15th, 2011, 03:52 AM

Hi

If you can check the Size of the file on the FTP server with the Temp file before deleting it, it could be a small confirmation.

Cheers
Shasur

Elevate08 · May 16th, 2011, 11:02 AM

Thank You for your response. However I ended up finding this piece of information that I'll share for future searchers on this topic.

Here is my final code as far as the validating the file was created successfully.
If anyone has any questions regarding this code I will help explain it. Thank you again and here you go.

Code:

Public valBoolean As Boolean
Sub do_ftp_transfer()

    Dim fn As String
    
    fn = Range("Config!B3").Value & "ftp_temp.txt"
    
    'MsgBox ("Filename=" & fn)
    
    Set oFso = CreateObject("Scripting.FileSystemObject")
    
    Set oFile = oFso.CreateTextFile(fn, True)
    
    nrow = 17
    
    'put temp file together to do ftp transfer
    Do Until Range("'Config'!B" & CStr(nrow)).Value = "bye"
        oFile.WriteLine Range("'Config'!B" & CStr(nrow)).Value & ""
        nrow = nrow + 1
    Loop
        oFile.WriteLine Range("'Config'!B" & CStr(nrow)).Value & ""
        nrow = nrow + 1
    oFile.Close

    'now that we have constructed file to pass to ftp, make it happen
    Call sFTP(fn)
    
    'Makes application pause for set duration (using to let transfer complete before checking if transfer was successful)
    Application.Wait (Now + TimeValue("0:00:2"))
    
    'Validating the file was transferred successfully
    Dim valFile As String

        valFile = "1" 'Range("Config!B8")
        valBoolean = True
               
    If FileFolderExists(valFile) Then
        MsgBox "FTP Transfer Successful! Continuing on to Print"
        oFso.DeleteFile (valFile)
        Call increment_serial_number
    Else
        MsgBox "FTP Transfer Failed. Process Ending. Click on button (2) again to retry."
        valBoolean = False
    End If
        oFso.DeleteFile (fn)
End Sub

Sub sFTP(stSCRFile As String)
Dim stSysDir
    stSysDir = Environ$("COMSPEC")
'MsgBox (stSysDir)
    stSysDir = Left$(stSysDir, Len(stSysDir) - Len(Dir(stSysDir)))
'MsgBox (stSysDir & "ftp.exe -s:" & stSCRFile)
    Call Shell(stSysDir & "ftp.exe -s:" & stSCRFile, vbNormalFocus)
    Application.Wait 5
End Sub
Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Check if a file or folder exists

    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    
EarlyExit:
    On Error GoTo 0

End Function

The following code is what is written into a text file to be read by ftp.exe to execute commands. The Put and Get files are updated with cells to increment the number following PS by whatever number is in the SerialNumber.txt file. This temporary file that is used by the ftp.exe is deleted onces its purpose has been fulfilled. Created again every time the code is ran.

open "Server Name"
"User Name"
"Password"
ascii
put C:\Parker_to_SDNL_ASN\Archive\Parker_to_SDNL_PS915 .txt
get Parker_to_SDNL_PS915.txt C:\Parker_to_SDNL_ASN\FTPValidate\Parker_to_SDNL_P S915_DL.txt
bye

The Application.Wait in the first SUB about mid way down can be changed from the "2" to whatever you would like if you would like the program to pause for a day you could the format is HR:MIN:SEC I only needed this because the process was going to fast for our servers and needed slowed down so that the validation of the file being transferred would show us the right results rather thank kicking us out a failed when it truly was successful.

Again if anybody has any question I will help out as much as possible.