Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 13th, 2011, 08:50 AM
Registered User
 
Join Date: May 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Lightbulb FTP Transfer Validate

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!!!

Last edited by Elevate08; May 13th, 2011 at 09:08 AM.. Reason: Adding Sub sFTP
 
Old May 15th, 2011, 03:52 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old May 16th, 2011, 11:02 AM
Registered User
 
Join Date: May 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Thumbs up Solved!

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Secured FTP File Transfer syed_sooraj Classic ASP Basics 1 December 7th, 2007 06:29 PM
problem picking up files after ftp transfer holdmykidney Pro Java 0 September 11th, 2006 06:17 AM
Transfer Files via FTP vb_developer C# 2 September 8th, 2004 12:36 AM
Transfer files via FTP by using ASP.NET vb_developer Classic ASP Basics 1 September 6th, 2004 03:28 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.