By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

ftp transfer by vba ?

P: 30
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
Aug 8 '08 #1
Share this Question
Share on Google+
25 Replies


NeoPa
Expert Mod 15k+
P: 31,616
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.
Aug 9 '08 #2

NeoPa
Expert Mod 15k+
P: 31,616
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")
Expand|Select|Wrap|Line Numbers
  1. ECHO ON
  2. CD /D "^R"
  3. "%SystemRoot%\System32\FTP.Exe" -n -s:^N.Ftp -w:16384 ^S
Range("FTP")
Expand|Select|Wrap|Line Numbers
  1. user ^A ^P
  2. binary
  3. lcd ^R
  4. ^C
  5. bell
  6. put ^F
  7. bye
Aug 9 '08 #3

P: 30
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
Aug 9 '08 #4

NeoPa
Expert Mod 15k+
P: 31,616
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.
Expand|Select|Wrap|Line Numbers
  1. 'WriteFile() creates a text file (strFile) from the data in ranFile.
  2. 'The file is stored relative to the folder this spreadsheet is found in.
  3. Private Function WriteFile(ranFile As Excel.Range, _
  4.                            ByVal strFile As String) As Boolean
  5.     Dim Various replacement variables as strings
  6.     Dim strMsg As String
  7.     Dim intSaveType As Integer
  8.     Dim ranThis As Excel.Range
  9.  
  10.     WriteFile = True    'Default to failure
  11.     'Set up replacement strings
  12.     Call Workbooks.Add
  13.     'Use a matching range in the new worksheet that will
  14.     'take the data from the passed range
  15.     With Range(...See comment above ...)
  16.         .Value = ranFile.Value
  17.         For Each ranThis In .Cells
  18.             ranThis = Replace(ranThis.Value, "^A", ...)
  19.             ranThis = Replace(ranThis.Value, "^C", ...)
  20.             etc
  21.         Next ranThis
  22.     End With
  23.     With ActiveWorkbook
  24.         strFile = strRootFolder & "\" & strFile
  25.         On Error Resume Next
  26.         Kill strFile
  27.         If Err And Err <> conErrNoFile Then
  28.             strMsg = Replace("Error(%N) - ""%D""", "%N", Err.Number)
  29.             strMsg = Replace("Error(%N) - ""%D""", "%D", Err.Description)
  30.             Call MsgBox(strMsg, vbCritical Or vbOKOnly, "Kill File")
  31.         Else
  32.             'I don't know why, but it seems necessary to clear Err
  33.             Call Err.Clear
  34.             Call .SaveAs(Filename:=strFile, _
  35.                          FileFormat:=xlTextPrinter, _
  36.                          CreateBackup:=False, _
  37.                          AddToMRU:=False)
  38.             WriteFile = Err
  39.         End If
  40.         On Error GoTo 0
  41.         Call .Close(SaveChanges:=False)
  42.     End With
  43. End Function
Aug 9 '08 #5

NeoPa
Expert Mod 15k+
P: 31,616
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 ;)
Aug 9 '08 #6

P: 30
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

Expand|Select|Wrap|Line Numbers
  1. ECHO ON
  2. CD /D "^R"
  3. "%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 **
Aug 13 '08 #7

NeoPa
Expert Mod 15k+
P: 31,616
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.
Aug 13 '08 #8

NeoPa
Expert Mod 15k+
P: 31,616
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

Expand|Select|Wrap|Line Numbers
  1. ECHO ON
  2. CD /D "^R"
  3. "%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.
Aug 13 '08 #9

P: 30
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 :)
Aug 13 '08 #10

NeoPa
Expert Mod 15k+
P: 31,616
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 :)
Aug 13 '08 #11

P: 30
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Knop75_Click()
  2. On Error GoTo Err_Knop75_Click
  3.  
  4.     Dim stAppName As String
  5.  
  6.     stAppName = "C:\WINDOWS\system32\ftp.exe"
  7.     Call Shell(stAppName, 1)
  8.  
  9. Exit_Knop75_Click:
  10.     Exit Sub
  11.  
  12. Err_Knop75_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_Knop75_Click
  15.     End Sub
Where in the lines above do I hook on to the routines you made?
Could you please be patient with me?

Chris
Aug 18 '08 #12

P: 30
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
Aug 18 '08 #13

NeoPa
Expert Mod 15k+
P: 31,616
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).
Aug 18 '08 #14

NeoPa
Expert Mod 15k+
P: 31,616
...
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).
Aug 18 '08 #15

P: 30
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 :)
Aug 18 '08 #16

NeoPa
Expert Mod 15k+
P: 31,616
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.
Aug 19 '08 #17

P: 30
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 ! :)
Aug 19 '08 #18

NeoPa
Expert Mod 15k+
P: 31,616
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
Expand|Select|Wrap|Line Numbers
  1. ECHO ON
  2. CD /D "C:\Database"
  3. "%SystemRoot%\System32\FTP.Exe" -n -s:CISTeam.Ftp -w:16384 ftp.cisteam.nl
CISTeam.Ftp
Expand|Select|Wrap|Line Numbers
  1. user ??? ???
  2. binary
  3. lcd C:\Database
  4. cd ???
  5. bell
  6. put CISTeamAmsterdam.Mdb
  7. 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?
Aug 19 '08 #19

NeoPa
Expert Mod 15k+
P: 31,616
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.
Aug 19 '08 #20

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

Expand|Select|Wrap|Line Numbers
  1. ECHO ON
  2. CD /D "C:\CISteem"
  3. "%SystemRoot%\System32\FTP.Exe" -n -s:CISTeem.Ftp -w:16384 ftp.cisteem.nl
CISTeem.Ftp
Code: ( text )

Expand|Select|Wrap|Line Numbers
  1. user amsterdam#cisteem.nl orange
  2. binary
  3. lcd C:\Database
  4. bell
  5. put CISTeemAmsterdam.mdb
  6. 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
Expand|Select|Wrap|Line Numbers
  1. Private Sub Knop75_Click()
  2. On Error GoTo Err_Knop75_Click
  3.  
  4.     Dim stAppName As String
  5.  
  6.     stAppName = "C:\WINDOWS\system32\ftp.exe"
  7.     Call Shell(stAppName, 1)
  8.  
  9. Exit_Knop75_Click:
  10.     Exit Sub
  11.  
  12. Err_Knop75_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_Knop75_Click
  15.     End Sub
Hope you enjoy this neverending saga as much as I do :)

Chris
Aug 20 '08 #21

NeoPa
Expert Mod 15k+
P: 31,616
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 :
Expand|Select|Wrap|Line Numbers
  1. stAppName = "C:\CISTeem\CISTeem.Cmd"
Let me know how you get on.

PS Neverending sagas are most enjoyable when they produce results ;)
Aug 20 '08 #22

P: 30
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
Aug 20 '08 #23

NeoPa
Expert Mod 15k+
P: 31,616
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().
Aug 20 '08 #24

P: 30
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
Aug 27 '08 #25

NeoPa
Expert Mod 15k+
P: 31,616
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.
Aug 28 '08 #26

Post your reply

Sign in to post your reply or Sign up for a free account.