ftp transfer by vba ?

[zaankanter]

Does anyone know if its possible to make a ftp-file transfer by vba?
What I have in mind is a routine in vba or a macro wich will e.g. transfer my database or parts of it to a back-up site

[NeoPa]

I just finished implementing one this last week in fact :)

It's not too simple but is certainly possible. Let me dig up some details.

[NeoPa]

I start off with two named ranges in a hidden sheet of my workbook. Carats (^) indicate that the following character should indicate a replacement string.
^A = Account name on FTP site
^C = A placeholder to add an optional cd line in FTP file if required - If not set to empty line
^F = Name of file required to upload
^N = Name of FTP file (excluding the .Ftp bit) - Use this for CMD file too for simplicity
^P = Password for ^A
^R = Root Folder - the folder that the spreadsheet is found in
^S = FTP site address

The data for the files themselves are included below. I needed a fair amount of flexibility. If you don't then you can replace the variable bits with hard coded items.
Range("CMD")

ECHO ON
CD /D "^R"
"%SystemRoot%\System32\FTP.Exe" -n -s:^N.Ftp -w:16384 ^S

Range("FTP")

user ^A ^P
binary
lcd ^R
^C
bell
put ^F
bye

[zaankanter]

Thanx NeoPa for your answer. I'm a novice, so I don't fully grasp it yet, but before asking more dumb questions I'm going to dig in your answer some more.
I'm sure I'll be back with further questions.
Thanks for now.

Chris

[NeoPa]

You can use this procedure as a basis for what you need to do to save your CMD & FTP files.
I had to strip out some of my code as it's specific to what I needed and wouldn't suit you. You will need to Copy/Paste the procedure, but you'll also need to make relevant amendments for your situation too.

When the files are prepared and are ready, you just need to execute the CMD file using the Shell() function.

'WriteFile() creates a text file (strFile) from the data in ranFile.
'The file is stored relative to the folder this spreadsheet is found in.
Private Function WriteFile(ranFile As Excel.Range, _
                           ByVal strFile As String) As Boolean
    Dim Various replacement variables as strings
    Dim strMsg As String
    Dim intSaveType As Integer
    Dim ranThis As Excel.Range
 
    WriteFile = True    'Default to failure
    'Set up replacement strings
    Call Workbooks.Add
    'Use a matching range in the new worksheet that will
    'take the data from the passed range
    With Range(...See comment above ...)
        .Value = ranFile.Value
        For Each ranThis In .Cells
            ranThis = Replace(ranThis.Value, "^A", ...)
            ranThis = Replace(ranThis.Value, "^C", ...)
            etc
        Next ranThis
    End With
    With ActiveWorkbook
        strFile = strRootFolder & "\" & strFile
        On Error Resume Next
        Kill strFile
        If Err And Err <> conErrNoFile Then
            strMsg = Replace("Error(%N) - ""%D""", "%N", Err.Number)
            strMsg = Replace("Error(%N) - ""%D""", "%D", Err.Description)
            Call MsgBox(strMsg, vbCritical Or vbOKOnly, "Kill File")
        Else
            'I don't know why, but it seems necessary to clear Err
            Call Err.Clear
            Call .SaveAs(Filename:=strFile, _
                         FileFormat:=xlTextPrinter, _
                         CreateBackup:=False, _
                         AddToMRU:=False)
            WriteFile = Err
        End If
        On Error GoTo 0
        Call .Close(SaveChanges:=False)
    End With
End Function

[NeoPa]

Thanx NeoPa for your answer. I'm a novice, so I don't fully grasp it yet, but before asking more dumb questions I'm going to dig in your answer some more.
I'm sure I'll be back with further questions.
Thanks for now.

Chris

That's fine Chris. This isn't too basic stuff - I expect some questions.

Hopefully what I've included so far at least has the building blocks to support what you need to accomplish.

PS. If you manage to get this to work for you then you can probably swagger a bit for your boss and colleagues ;)

[zaankanter]

Hi

This is realy difficult for me. But one step at a time.

Please tell me what the -n , -s and -w in this bit stand for

ECHO ON
CD /D "^R"
"%SystemRoot%\System32\FTP.Exe" -n -s:^N.Ftp -w:16384 ^S

By the way, there are no collegues or bosses to boast at. I'm just a one-guy operation, doing a hobby beside my regular non-IT job.
Check me out: ** Link removed **

[NeoPa]

I was happy to check that out, but I'm afraid we don't allow links to personal web sites in the technical areas so I've removed it from your post.

Of course, pleasant as it was, I couldn't understand too much of it as I can only do ein - tien in Dutch (pretty well) anyway.

[NeoPa]

Hi

This is realy difficult for me. But one step at a time.

Please tell me what the -n , -s and -w in this bit stand for

ECHO ON
CD /D "^R"
"%SystemRoot%\System32\FTP.Exe" -n -s:^N.Ftp -w:16384 ^S

By the way, there are no collegues or bosses to boast at. I'm just a one-guy operation, doing a hobby beside my regular non-IT job.
Check me out: ** Link removed **

No worries. Any questions can be dealt with. As many as you have :)

FTP Command Line will give you all you need but :
-n : Suppresses the ability to log on automatically when the initial connection is made. This stops anonymous logon.
-s : Specifies a text file that contains FTP commands. These commands run automatically after FTP starts. This parameter allows no spaces. Use this parameter instead of redirection (<).
-w : Specifies the size of the transfer buffer. The default window size is 4096 bytes. This (default) runs quite slowly in my experience, but probably depends on networking parameters and how many you choose to run in parallel.

[zaankanter]

Its 23.30 local time, so I will struggle on tomorrow.
Thanks for your answer so far, sorry for the link I shouldn't have posted.
Guess it's probably for the best. People googling my name/trademark could very well stumble upon this thread, wich of course doesn't reflect my proffesional qualities in a very good light :)