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

[zaankanter]

Yes, the file (preferably the .mdb file in wich the routine is called) should go to a prefixed place.
This is the situation:
My Acces-application is used by several users, each having their own stand-alone-version at dislocated places. To help/service them, they each have their own subdomain on my website, to wich they can upload their version of the application. In this way I can evaluate what their problem is and do updates. This is of course also a way to back-up.
This procedure is at present performed by calling ws-ftp, wich has for each of the users their user/pass pre-installed. But even though this is a fairly straightforward procedure, it still proves to be too complicated for some users.
That is why I want to fully automate this process: click=upload.
So yes, most of the data could be hard coded in each users version of the application (there aren't that many of them) or maybe taken from a table in wich they are stored.
Downloading is much simpler, I put the file in an windows-installer-wizard, wich the users can just open by clicking at the apropriate link in an email I send them.
Hope you have a better view of the probem now.
hope to hear soon,
Chris :)

[NeoPa]

You've lost me a little here Chris.

I asked if it is going to run the same file every time and you seem to reply "Yes", but you go on to say different users would be set up differently. This seems to indicate the answer should actually be "No".

What are the contents of the two files you anticipate using (CMD & FTP)? Feel free to * out any passwords or sensitive addresses. Just be sure that there are no items in there which vary under any circumstances.

PS. Sorry for the delay - I've been caught up with some problems yesterday & today.

[zaankanter]

You've lost me a little here Chris.
I asked if it is going to run the same file every time and you seem to reply "Yes", but you go on to say different users would be set up differently. This seems to indicate the answer should actually be "No".

Actualy I would be very pleased if we could come up with a simple hardcoded answer, because for each user the same values would apply over and over again, and would be willing to repeat hardcoding those values(with changes) in every copy of the application.
But of course a more subtle approach, with would save me that trouble would be even better.
I've done some digging and it seems to me that the Call Shell command does not give the possibility to transfer instructions tot the called program (e.g. ftp.exe)

What are the contents of the two files you anticipate using (CMD & FTP)? Feel free to * out any passwords or sensitive addresses. Just be sure that there are no items in there which vary under any circumstances

I describe the needs for 1 user, lets say the Amsterdam branch:
I would want to log in to ftp.cisteem.nl
For that a username and password (both always the same for the same version of the application, i.e. the Amsterdam branch).
Because the ftp-access to the site for this user (the Amsterdam branch) is restraint to the subdomain assigned to his version of the application (lets say amsterdam.cisteem.nl) this would right away log on to the right directory on the server.
Then a file, named cisteemuser.mdb should be transfered.
When hardcoded in the Amsterdam-branche version there would not be any variables
When coded in such a way that it would apply to al versions, the variables would then of course be:
Username
password
cisteemuser.mdb

PS. Sorry for the delay - I've been caught up with some problems yesterday & today.

Just very glad that you keep taking te trouble to answer a knitwit like me ! :)

[NeoPa]

Chris,

You keep adding bits of information without giving me all. The more bits you add the more I need to know to understand you.

Is the FTP job going to upload a single file to multiple locations (possibly with a rename)?

Is the name of the database going to change or do you already have multiple databases that will be uploaded?

I was thinking of uploading a single file to a single destination.

What I asked for earlier (the contents of the CMD & FTP files) would have answered all my questions (that's why that is what I requested).

I will post an example pair of files, but I need you to tell me what you have in mind really. I suggest we leave multiples until at least we've dealt with a single one. Use Amsterdam as the first example if you like.
CISTeam.Cmd

ECHO ON
CD /D "C:\Database"
"%SystemRoot%\System32\FTP.Exe" -n -s:CISTeam.Ftp -w:16384 ftp.cisteam.nl

CISTeam.Ftp

user ??? ???
binary
lcd C:\Database
cd ???
bell
put CISTeamAmsterdam.Mdb
bye

Now, this may not be right for you, and there are places where I really have no idea what you need and I've just used "???". This is why I need you to post the files you are considering using, which will give me clearer information of what we need to do. Does that make sense?

[NeoPa]

If you find any of what I'm saying confusing or complicated just let me know. I can help if I know.

I can't help if I'm short of the correct information.

[zaankanter]

Hi,

I suppose there is just too much wich I just don't grasp. That makes it difficult for me to understand what it is exactly that you ask me. I agree that its probably best if we try to solve the problem as a one-file-one-adress problem.

To make things more realistic, I have made an ftp-account for Amsterdam that actualy works (for as long as we need it for the purpose of solving my problem):
ftp.cisteem.nl
user amsterdam#cisteem.nl
password orange

First of all I'll give you the code, adapted as much as I can to what I think is needed:

CISteem.Cmd
Code: ( text )

ECHO ON
CD /D "C:\CISteem"
"%SystemRoot%\System32\FTP.Exe" -n -s:CISTeem.Ftp -w:16384 ftp.cisteem.nl

CISTeem.Ftp
Code: ( text )

user amsterdam#cisteem.nl orange
binary
lcd C:\Database
bell
put CISTeemAmsterdam.mdb
bye

I left out the

cd ???

part, because the ftp-rights of amsterdam are restricted to a single subdomain, and the working directory of the server will automaticly be the one assigned to amsterdam.
I hope this is what you need.

Now some questions from me (sorry for the ignorence):
I think I understand that CISteem.ftp contains commandlines much the same as would be typed into the ftp.exe window, after giving the command "open ftp.cisteem.nl"
And it seems to me that CISteem.cmd invokes those commands by calling CISteem.ftp
What I don't get is how you get CISteem.cmd to work from a click-event like

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

Hope you enjoy this neverending saga as much as I do :)

Chris

[NeoPa]

Almost perfect Chris. You have come along almost the whole distance in that one post.

NB. This is more from doing than talking ;)

From this I can tell that your database is C:\Database\CISTeemAmsterdam.Mdb and your two command files are C:\CISTeem\CISTeem.Cmd & C:\CISTeem\CISTeem.Ftp.

If that is all correct, then try simply changing line #6 of your VBA code to :

stAppName = "C:\CISTeem\CISTeem.Cmd"

Let me know how you get on.

PS Neverending sagas are most enjoyable when they produce results ;)

[zaankanter]

It works like a charm !!!
This is exactly what I had in mind when I first posted my question.
Besides solving my question, I feel I've learned a lot about cmd and ftp.
Thank you very much!!!

One remaining question:
- It seems to me that the (shell ?) routine runs seperatly from the acces-application. Is there nevertheless a way in wich I can inform the user (e.g. a message-box) when the transfer has been succesfully ended?

yours gratefull
Chris

[NeoPa]

It works like a charm !!!
This is exactly what I had in mind when I first posted my question.
Besides solving my question, I feel I've learned a lot about cmd and ftp.
Thank you very much!!!

Very pleased to hear it. That's the idea of course, to include learning in with the answers so we're not just providing ready-made solutions. Solutions is good, but with education is better :)

One remaining question:
- It seems to me that the (shell ?) routine runs seperatly from the acces-application. Is there nevertheless a way in wich I can inform the user (e.g. a message-box) when the transfer has been succesfully ended?

yours gratefully
Chris

It sounds like you need the Shell() function to work synchronously. Unfortunately it is an asynchronous function so returns control immediately the other process has been started.

See Open Access mdb from mdb delete error for a synchronous version called ShellWait().

[zaankanter]

Hi

Doing ftp-transfer in the way described above I stumbled upon another minor problem. I don't know if it should be posted here or if it should be a different posting. Anyhow this is the problem:
Al goes well when the transferred file is relatively small, but when I send a file of 5/6 Mb this happens: The file does transfer (and of course takes some time doing so) but instead of giving a message back that the transfer succeeded (wich would make the routine above do "BYE") the server stays silent. After some considerable time there is a time out and the routine (see above) finaly does BYE.
When I check, the file did arrive at the server allright.
So the routine works allright, but the ftp-window stays open far too long (up to 10 minutes or so).
Is there anything I can do to remedy this? Or is it something the server does?

Happy to hear any comment.
Chris

[NeoPa]

I'm not sure I can help you on this Chris.

I expect it is related to the FTP server being used on the site you're connecting to, but pure supposition really. All I can say is that I haven't experienced that problem.

FTP isn't a Windows protocol per se, but you may want to post in there or even in Linux / Unix / BSD to see if anyone there has experience that can help. If you get no joy you could try Miscellaneous Questions, but there are fewer experts who frequennt that forum so you may struggle to find an answer.

Anyway, good luck.