Some FTP via VBA Questions

[localfiend]

Hi all, I've got a few questions for you. I've been working on a way to transfer pdf files created in excel to my FTP server by using VBA code and have run into a few walls when it comes to error handling.

First off, here's the relevant code:

'FTP Section

'    Dim serverPATH As String
'    Dim clientPATH As String
        
'        Worksheets("Codes").Range("H25").Value = serverPATH
'        Worksheets("Codes").Range("H18").Value = clientPATH

    ' Build Script File
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\Test\script.dat", True)
        a.writeline "user1" 'username
        a.writeline "password1" 'password
        a.writeline "quote PASV" 'enable passive mode
        a.writeline "put c:\Test\Test.pdf" 'transfer test file
'        a.writeline "cd " & serverPATH 'change server directory
'        a.writeline "put " & clientPATH 'transfer file
        a.writeline "quit" 'quit
        a.Close
     
    ' Build Batch File
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\Test\transfer.bat", True)
        a.writeline "@echo off"
        a.writeline "ftp -i -s:c:\Test\script.dat myftpsite.net"
        a.writeline "echo %errorlevel%" ' 0 = Success
        a.writeline "echo %errorlevel% > check.dat" 'create check.dat
        a.writeline "del script.dat"
        a.writeline ":EOF"
        a.Close
     
    ' Run Batch File
    dRetVal = Shell("C:\Test\transfer.bat", 0) 'transfer the file

The above code works great as far as the transfer goes, even if it is a bit clunky to create a script file that contains users/passwords. The part that does not work is the error logging. The %errorlevel% command I found by googling the subject, but I can't seem to get it to return anything other than 0 even if there is a problem.

Anyone know of a good way to let excel know if the transfer has gone through?

I suppose if worst comes to worst I'll have to find an FTP program that can sync sets of folders with my server at a given time each night - but It would still be nice to create a log showing me what files didn't make the transfer. That way the transfer could be attempted again, or the files in question could be set aside for transfer later.

 

[jimrward]

there is some code around the forum for Shell and wait for completion, which i looked into in the past for FTP stuff, its only a few more lines than what you have

 

[xenou]

longshot but maybe in a .bat you need double percent signs:

echo %%errorlevel%%