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

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

"Eric" wrote in message
...
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

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

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)




"Eric" escreveu na mensagem
...
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,
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


"Orlando Magalhães Filho" wrote in message
...
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

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)




"Eric" escreveu na mensagem
...
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

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


"Orlando Magalhães Filho" wrote in message
...
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

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)




"Eric" escreveu na mensagem
...
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

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


"R. Choate" wrote in message
...
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


"Orlando Magalhães Filho" wrote in message
...
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

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)




"Eric" escreveu na mensagem
...
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,
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


"Orlando Magalhães Filho" wrote in message
...
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

(So that you get best and rapid solution and all may benefit from the
discussion, please reply within the newsgroup, not in email)




"Eric" escreveu na mensagem
...
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

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

"R. Choate" escreveu na mensagem
...
Eric,
Unless you have been able to make Orlando's suggestion work and you are
happy with the outcome, I'm going to stick with my original suggestion.
Using the Windows API for FTP is quick, easy, and seamless. It may look
like
a lot of code at first, but you only need some of what I sent. The API
version does not use any DOS commands and will not make the screen flash
or
anything like that. Further, I tried to make the code work that Orlando
gave
you and I was not successful. I'm not a big fan of code which opens a DOS
session in the background to run, which this one seems to. I believe you
now have my email address since I sent you the zip file. If you have any
questions or need some help getting my code to work, just send me an email
and I'll do my best to get you going.
--
RMC,CPA


"Eric" wrote in message
...
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

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" wrote in message
...
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

"R. Choate" escreveu na mensagem
...
Eric,
Unless you have been able to make Orlando's suggestion work and you are
happy with the outcome, I'm going to stick with my original suggestion.
Using the Windows API for FTP is quick, easy, and seamless. It may look
like
a lot of code at first, but you only need some of what I sent. The API
version does not use any DOS commands and will not make the screen flash
or
anything like that. Further, I tried to make the code work that Orlando
gave
you and I was not successful. I'm not a big fan of code which opens a
DOS
session in the background to run, which this one seems to. I believe
you
now have my email address since I sent you the zip file. If you have any
questions or need some help getting my code to work, just send me an
email
and I'll do my best to get you going.
--
RMC,CPA


"Eric" wrote in message
...
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

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" escreveu na mensagem
...
I've got an Access file which includes code that was converted from VB for
API transfer. I'll send it to you. Study it some and then let me know if
you
want further explanations.
--
RMC,CPA


"Orlando Magalhães Filho" wrote in message
...
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

"R. Choate" escreveu na mensagem
...
Eric,
Unless you have been able to make Orlando's suggestion work and you are
happy with the outcome, I'm going to stick with my original suggestion.
Using the Windows API for FTP is quick, easy, and seamless. It may look
like
a lot of code at first, but you only need some of what I sent. The API
version does not use any DOS commands and will not make the screen flash
or
anything like that. Further, I tried to make the code work that Orlando
gave
you and I was not successful. I'm not a big fan of code which opens a
DOS
session in the background to run, which this one seems to. I believe
you
now have my email address since I sent you the zip file. If you have any
questions or need some help getting my code to work, just send me an
email
and I'll do my best to get you going.
--
RMC,CPA


"Eric" wrote in message
...
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

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


"Orlando Magalhães Filho" wrote in message
...
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" escreveu na mensagem
...
I've got an Access file which includes code that was converted from VB for
API transfer. I'll send it to you. Study it some and then let me know if
you
want further explanations.
--
RMC,CPA


"Orlando Magalhães Filho" wrote in message
...
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

"R. Choate" escreveu na mensagem
...
Eric,
Unless you have been able to make Orlando's suggestion work and you are
happy with the outcome, I'm going to stick with my original suggestion.
Using the Windows API for FTP is quick, easy, and seamless. It may look
like
a lot of code at first, but you only need some of what I sent. The API
version does not use any DOS commands and will not make the screen flash
or
anything like that. Further, I tried to make the code work that Orlando
gave
you and I was not successful. I'm not a big fan of code which opens a
DOS
session in the background to run, which this one seems to. I believe
you
now have my email address since I sent you the zip file. If you have any
questions or need some help getting my code to work, just send me an
email
and I'll do my best to get you going.
--
RMC,CPA


"Eric" wrote in message
...
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