FTP in vba macro

[Eric]

HI There,

Can anyone help me on how to use 'FTP' in vba macro. My
objective is, after converting my excel file to a CSV file
(comma delimited file) I want to FTP'd it to our
production server, using macro code.

I would appreciate very much your help.

Thanks
Eric

 

[Richard Choate]

Well, I will be happy to give you the code, or better yet, a db with the
code in it, but I'm afraid it is a lot of code and it involves significant
API calls. If you don't mind using them, then it works great. Unfortunately,
I can't send you the code until in the morning because I have to leave for
now. I definitely can't just post it to the NG because it is just too much
code and wouldn't make sense in a newsreader. Can you handle converting some
Access code for use in Excel?
--
HTH
Richard Choate, CPA

HI There,

Can anyone help me on how to use 'FTP' in vba macro. My
objective is, after converting my excel file to a CSV file
(comma delimited file) I want to FTP'd it to our
production server, using macro code.

I would appreciate very much your help.

Thanks
Eric

 

[Orlando Magalhães Filho]

Hi Eric,

Try to use this code:

Public Sub FtpSend()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

vPath = ThisWorkbook.Path
vFile = "YourFile.csv"
vFTPServ = "ftp.yourserver.com"

'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "\FtpComm.txt" For Output As #fNum
Print #1, "user YourLogin YourPass" ' your login and password"
Print #1, "cd TargetDir" ' change to dir on server
Print #1, "bin" ' bin or ascii file type to send
Print #1, "put " & vPath & "\" & vFile & " " & vFile ' upload local
filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program
Close

Shell "ftp -n -i -g -s:" & vPath & "\FtpComm.txt " & vFTPServ,
vbNormalNoFocus
End Sub


HTH

 

[R. Choate]

Orlando,
Have you had success with this code? I've not seen this before and have
spent a lot of time in the past trying to find a simple solution. If this
works, it would be a great way to go.
--
RMC,CPA


Hi Eric,

Try to use this code:

Public Sub FtpSend()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

vPath = ThisWorkbook.Path
vFile = "YourFile.csv"
vFTPServ = "ftp.yourserver.com"

'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "\FtpComm.txt" For Output As #fNum
Print #1, "user YourLogin YourPass" ' your login and password"
Print #1, "cd TargetDir" ' change to dir on server
Print #1, "bin" ' bin or ascii file type to send
Print #1, "put " & vPath & "\" & vFile & " " & vFile ' upload local
filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program
Close

Shell "ftp -n -i -g -s:" & vPath & "\FtpComm.txt " & vFTPServ,
vbNormalNoFocus
End Sub


HTH

 

[R. Choate]

I got an error on the Shell line. It shows in red in the editor so something
is missing or bad syntax. Could you give some explanatory detail on the
shell. I know that runs the executable, but please explain more about it.

Also, what is the syntax for the Username and password line? Finally, what
is the FtpComm.txt?
--
RMC,CPA


Hi Eric,

Try to use this code:

Public Sub FtpSend()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

vPath = ThisWorkbook.Path
vFile = "YourFile.csv"
vFTPServ = "ftp.yourserver.com"

'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "\FtpComm.txt" For Output As #fNum
Print #1, "user YourLogin YourPass" ' your login and password"
Print #1, "cd TargetDir" ' change to dir on server
Print #1, "bin" ' bin or ascii file type to send
Print #1, "put " & vPath & "\" & vFile & " " & vFile ' upload local
filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program
Close

Shell "ftp -n -i -g -s:" & vPath & "\FtpComm.txt " & vFTPServ,
vbNormalNoFocus
End Sub


HTH

 

[R. Choate]

Sorry, the shell line was just a text wrapping thing. However, it still
didn't work. No error, just didn't get a result.
--
RMC,CPA


I got an error on the Shell line. It shows in red in the editor so something
is missing or bad syntax. Could you give some explanatory detail on the
shell. I know that runs the executable, but please explain more about it.

Also, what is the syntax for the Username and password line? Finally, what
is the FtpComm.txt?
--
RMC,CPA


Hi Eric,

Try to use this code:

Public Sub FtpSend()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

vPath = ThisWorkbook.Path
vFile = "YourFile.csv"
vFTPServ = "ftp.yourserver.com"

'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "\FtpComm.txt" For Output As #fNum
Print #1, "user YourLogin YourPass" ' your login and password"
Print #1, "cd TargetDir" ' change to dir on server
Print #1, "bin" ' bin or ascii file type to send
Print #1, "put " & vPath & "\" & vFile & " " & vFile ' upload local
filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program
Close

Shell "ftp -n -i -g -s:" & vPath & "\FtpComm.txt " & vFTPServ,
vbNormalNoFocus
End Sub


HTH

 

[R. Choate]

Orlando,
As I learn more about your code, I'm starting to think that this is running
a DOS command. That must be why the screen flashes when the code runs. I'm
assuming that FtpComm.txt is supposed to be a script file with specific ftp
commands. What are you recommending the OP to write into that file?

Have you tried the Windows API method?

--
RMC,CPA


Hi Eric,

Try to use this code:

Public Sub FtpSend()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long

vPath = ThisWorkbook.Path
vFile = "YourFile.csv"
vFTPServ = "ftp.yourserver.com"

'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "\FtpComm.txt" For Output As #fNum
Print #1, "user YourLogin YourPass" ' your login and password"
Print #1, "cd TargetDir" ' change to dir on server
Print #1, "bin" ' bin or ascii file type to send
Print #1, "put " & vPath & "\" & vFile & " " & vFile ' upload local
filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program
Close

Shell "ftp -n -i -g -s:" & vPath & "\FtpComm.txt " & vFTPServ,
vbNormalNoFocus
End Sub


HTH

 

[Orlando Magalhães Filho]

Hi R. Choate,

Yes. FTP.exe is a DOS utility and I have used one code like this for a long
time without problems.

If you have other smart solution with API and if it was possible, I'd like
to see.

Regards,

Orlando

 

[Rob Bovey]

Hi Orlando,

This vbip web site has all kinds of good info on Internet programming
with VB, most of which can be easily translated to VBA. The specific link
below has a good discussion of FTP.

http://www.vbip.com/wininet/wininet-ftp-command-01.asp

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Orlando Magalhães Filho]

Thanks, R. Choate. As I thought, your solution is something complex and
ready with user interface. Maybe the OP wants something simple and that
could be used by command line.

Regards,

Orlando

 

[R. Choate]

Actually, Orlando, I've not heard from the OP since I sent him my file. I
notice he didn't comment on your solution either. We may never know what
happened with him. I should add, though, that my ftp code isn't nearly as
complex as it looks. There is a lot of extra code there because it is tied
to a form that you could use to do all kinds of things after you get a
connection. It is a complete API FTP solution. However, if you know you are
going to send the same file or get the same file (or both) all the time, you
could cut out a huge block of the code. You would also not need the form, so
that would cut a bunch more of it.

Thanks for your command line info, too. I appreciated learning about it.
--
RMC,CPA


Thanks, R. Choate. As I thought, your solution is something complex and
ready with user interface. Maybe the OP wants something simple and that
could be used by command line.

Regards,

Orlando