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