Hi friends,

I am using below code for FTP using vbscript.

Please help me to do same task via VBA.

SystemUtil.run "cmd.exe"
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type "cd\"
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type micReturn
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type "ftp"
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type micReturn
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type "open mvsftp"
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type micReturn
wait 1
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type usrID "
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type micReturn
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type Password
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type micReturn
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type "get"
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type micReturn
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type "'"&dataset& "'"
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type micReturn
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type filePath
Window("micclass:=Window","nativeclass:=ConsoleWin dowClass").Type micReturn


Thanks & Regards

sachin ahuja wrote:

I am using below code for FTP using vbscript.

Please help me to do same task via VBA.

It depends on what you actually want to do. If you want to specifically
automate ftp, the best way is to use what amounts to a batch file:

'Untested air code...
bakdir$ = CurDir$
ChDir "\"
ftpf = FreeFile
Open "ftpcmds.tmp" For Output As ftpf
Print #ftpf, "open mvsftp"
Print #ftpf, usrID & "
'No need to explicitly wait; ftp will worry about such things for you.
Print #ftpf, Password
Print #ftpf, "get"
Print #ftpf, "'" & dataset & "'"
Print #ftpf, filePath
'At this point, you may want to add an explicit exit to the batch:
'Print #ftpf, "bye"
Close ftpf&
Shell "ftp -s:ftpcmds.tmp"
ChDir bakdir$

If you just need to grab files from an ftp server, you should be able to
connect to it via code and grab your files directly. I'm sure there are
plenty of examples for doing that on the www; Google is your friend.

--
You can't turn the volume down on beating a hooker with a baseball bat.

I wrote:

Close ftpf&

Damn, missed one. Change "ftpf&" to "ftpf" in the above line.

--
I take myself entirely too seriously.

I am getting an error:

Run-time error '75' :
Path/File access error

at line :
Open "ftpcmds.tmp" For Output As ftpf

Please help me to resolve this problem.

sachin ahuja wrote:

I am getting an error:

Run-time error '75' :
Path/File access error

at line :
Open "ftpcmds.tmp" For Output As ftpf

Please help me to resolve this problem.

You probably don't have write access to the root directory. Change the code
to this:

bakdir$ = CurDir$
ChDir "\"
tmpFile = Environ$("TEMP") & "\ftpcmds.tmp"
ftpf = FreeFile
Open tmpFile For Output As ftpf
Print #ftpf, "open mvsftp"
Print #ftpf, usrID & "
'No need to explicitly wait; ftp will worry about such things for you.
Print #ftpf, Password
Print #ftpf, "get"
Print #ftpf, "'" & dataset & "'"
Print #ftpf, filePath
'Without an exit, ftp will not automatically close.
'Print #ftpf, "bye"
Close ftpf
Shell "ftp -s:" & tmpFile
ChDir bakdir$

Note that I haven't tested your ftp script, *just* the VBA code.

--
Did the writers suffer a massive collective amnesia attack?

Thanks Auric,
Its working fine now.

Hi Auric,

Need your help again.
I wanna copy text from FTP window before typing "bye" on "cmd" in above code snippet.

Thanks

sachin ahuja wrote:

Need your help again.
I wanna copy text from FTP window before typing "bye" on "cmd" in above
code snippet.

Why? What are you trying to accomplish?

The only way I know of involves using the Windows API... but it's been so
long since I needed to do so that I don't remember how any more.

--
The burning smell kept reminding me, I shouldn't'a gotten involved.

I am using below functionality and wanna read content after Pressing Enter on 7 line.
i.e. I need "BAS7544.P.EFT.METLIFE.EOI.G0596V00" to be copied in a cell in the excel.


1. C:\ftp
2. ftp open mvsftp
Connected to l4dupfw113.Server.com.
220- --- NOTICE! ---
220-This is a private computer facility protected by a security system.
Access
220-to and use of this facility requires explicit written, current authorization
220-and is strictly limited to the purposes of this organization's business.
220-Unauthorized or any attempt at unauthorized access, use, copying,
220-alteration, destruction or damage to its data, programs, or equipment may
220-violate the Federal Computer Fraud and Abuse Act of 1986 as well as applicable
220-state law and may result in criminal or civil liability, or both.
220-
220-Associates File Transfer Protocol Proxy
220-Please enter the userid and the remote host you wish to connect to
220-(e.g. )
220-at the user prompt and enter your password at the password
220 prompt. Please contact the Support Center if you encounter difficulties.
3. User (l4dupfw113.server.com:(none)):
331-(----GATEWAY CONNECTED TO 10.100.20.23----)
331-(220-TCPFTP1 IBM FTP CS V2R1 at L4DMPSYSC, 02:28:15 on 2014-08-05.)
331-(220 Connection will close if idle for more than 15 minutes.)
331 Send password please.
4. Password:
230 @F5658 is logged on. Working directory is "@F5658.".
5. ftp get
6. Remote file 'BAS7544.P.EFT.METLIFE.EOI(0)'
7. Local file c:\pat\abc.txt
200 Port request OK.
125 Sending data set BAS7544.P.EFT.METLIFE.EOI.G0596V00 FIXrecfm 80
250 Transfer completed successfully.
ftp: 92 bytes received in 0.00Seconds 92.00Kbytes/sec.
ftp


Please help me.

Thanks,

I am using below functionality and wanna read content after Pressing Enter on 7 line.
i.e. I need "BAS7544.P.EFT.METLIFE.EOI.G0596V00" to be copied in a cell in the excel.


1. C:\ftp
2. ftp open mvsftp
Connected to l4dupfw113.Server.com.
220- --- NOTICE! ---
220-This is a private computer facility protected by a security system.
Access
220-to and use of this facility requires explicit written, current authorization
220-and is strictly limited to the purposes of this organization's business.
220-Unauthorized or any attempt at unauthorized access, use, copying,
220-alteration, destruction or damage to its data, programs, or equipment may
220-violate the Federal Computer Fraud and Abuse Act of 1986 as well as applicable
220-state law and may result in criminal or civil liability, or both.
220-
220-Associates File Transfer Protocol Proxy
220-Please enter the userid and the remote host you wish to connect to
220-(e.g. )
220-at the user prompt and enter your password at the password
220 prompt. Please contact the Support Center if you encounter difficulties.
3. User (l4dupfw113.server.com:(none)):
331-(----GATEWAY CONNECTED TO 10.100.20.23----)
331-(220-TCPFTP1 IBM FTP CS V2R1 at L4DMPSYSC, 02:28:15 on 2014-08-05.)
331-(220 Connection will close if idle for more than 15 minutes.)
331 Send password please.
4. Password:
230 @F5658 is logged on. Working directory is "@F5658.".
5. ftp get
6. Remote file 'BAS7544.P.EFT.METLIFE.EOI(0)'
7. Local file c:\pat\abc.txt
200 Port request OK.
125 Sending data set BAS7544.P.EFT.METLIFE.EOI.G0596V00 FIXrecfm 80
250 Transfer completed successfully.
ftp: 92 bytes received in 0.00Seconds 92.00Kbytes/sec.
ftp


Please help me.

Thanks,

On Tuesday, August 5, 2014 10:32:40 AM UTC+5:30, Auric__ wrote:
sachin ahuja wrote:



Need your help again.

I wanna copy text from FTP window before typing "bye" on "cmd" in above

code snippet.



Why? What are you trying to accomplish?



The only way I know of involves using the Windows API... but it's been so

long since I needed to do so that I don't remember how any more.



--

The burning smell kept reminding me, I shouldn't'a gotten involved.



On Tuesday, August 5, 2014 10:32:40 AM UTC+5:30, Auric__ wrote:
sachin ahuja wrote:



Need your help again.

I wanna copy text from FTP window before typing "bye" on "cmd" in above

code snippet.



Why? What are you trying to accomplish?



The only way I know of involves using the Windows API... but it's been so

long since I needed to do so that I don't remember how any more.



--

The burning smell kept reminding me, I shouldn't'a gotten involved.