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 :)

[NeoPa]

Indeed - it's getting quite late here too, but we're an hour back from you.

No worries about the link - an innocent mistake obviously. Not everyone knows the rules back-to-front of course.

I look forward to more questions if/when you have them :)

[zaankanter]

Hi Neopa,

First of all your link to "FTP commandline" proved very insightfull, since I never worked with windows ftp.exe before, buth rather with other programs suchs as ws-ftp.
Using the folowing lines I got the FTP function in Windows to open from Acces (I see a black box containing the ftp> prompt). I tried to use the commands in the mentioned manual typing directly into the ftp box, and also have made use of a .txt file and the -s command, but it just returns the dutch equivalent of "inproper command" .
There must be something quite obvious evading my grasp.

Private Sub Knop75_Click()
On Error GoTo Err_Knop75_Click

    Dim stAppName As String

    stAppName = "C:\WINDOWS\system32\ftp.exe"
    Call Shell(stAppName, 1)

Exit_Knop75_Click:
    Exit Sub

Err_Knop75_Click:
    MsgBox Err.Description
    Resume Exit_Knop75_Click
    End Sub

Where in the lines above do I hook on to the routines you made?
Could you please be patient with me?

Chris

[zaankanter]

Appending the above:
I just managed to acces my ftp server, directly typing into the ftp box.
The list of commands at ftp-subcommands proved to be the key.
Nevertheless: This brings me to a better understanding of FTPcommand, but not yet to the target I set: automating ftp-transfer from Acces.
Hope to hear soon from you

Chris

[NeoPa]

Chris, I'm so sorry.

I've just realised this question is NOT about doing it in Excel :S

This is completely my mistake. I've just picked up a few questions in the Access forum recently for Excel and as I'd just completed one in Excel (the FTP routine) I jumped in without (enough) thinking.

With that in mind, does your procedure FTP the same file to the same place every time?

If it does we can simply Shell() the command that you can have available on your filesystem somewhere.

If not we will need to build the file(s) from the database first on the fly (and still Shell() the resultant CMD file).

[NeoPa]

...
Where in the lines above do I hook on to the routines you made?
Could you please be patient with me?

I'm afraid this means that the procedure posted earlier is not applicable to this case (It does work very nicely in Excel though :D).