FTP in vba macro

E

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
 
R

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
 
O

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

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

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

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

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
 
O

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
 
O

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

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
 
Joined
Nov 23, 2009
Messages
3
Reaction score
0
Hi Orlando

I've tried your code, and really liked it, it sort of worked for me until i tried to get flash with it and now I'm left in a bit of a confusion. Might i trouble you with my error please?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top