Solved winscp

[prasadgov]

Hi,

I am trying to automate my winscp script to download files froma ftp server

This is my winscp code and text file (courtesy - Pat Hartman).

Private Sub Command1_Click()
Dim strQuote As String
strQuote = Chr(34)
Dim strSFTPDir As String
strSFTPDir = "C:\Program Files (x86)\Winscp\"
Dim strCommand As String
strCommand = "/script=C:\Daily\inputs\dtccwinscpfile.txt"     'change the file name/path specific to your computer
Call Shell(strSFTPDir & "winscp.exe " & strQuote & strCommand & strQuote, vbNormalNoFocus)
End Sub

and this is my text file

open 10.xxx.x.xx
2852
pwd3478!#
cd /ftphome
lcd /DeliveryOrders
mget  *.ftprcv \\10.xxx.xx.xx\backupshare\205\Reports\DTCC\dtcc\*.txt
quit

It is not retrieving any files. Is the winscp command script correct?
The mget downloads the files which are in FTPRCV type and saves it as .txt
What am I doing wrong?

When I use winscp manually from GUI, I am able to get these files.
In our organization, they have a version of winSCP on which the Generate code and other features are disabled

How to direct the code to a log file?



TIA

 

[Gasman]

Have you tried those steps manually one by one?

You know.... simple debugging?

 

[prasadgov]

Have you tried those steps manually one by one?

You know.... simple debugging?

it doesn't give any errors. I will try to use log

 

[Gasman]

Why not go straight to the horses mouth?

Scripting and Task Automation :: WinSCP

WinSCP is a free file manager for Windows supporting FTP, SFTP, S3 and WebDAV.

winscp.net

 

[prasadgov]

it doesn't give any errors. I will try to use log

I am not sure why nothing is being written to the log file. The Call Shell(strSFTPDir & "winscp.exe " & strQuote & strCommand & strQuote, vbNormalNoFocus) executes without errors. Totally clueless!

 

[Gasman]

Well, if you had bothered to look at the link, the examples are using get not mget.?
That is why I asked if you had tested this manually?

Regardless, plenty of examples on that link.

 

[prasadgov]

Well, if you had bothered to look at the link, the examples are using get not mget.?
That is why I asked if you had tested this manually?

Regardless, plenty of examples on that link.

I did try just "get"
I modified the line

open 10.xxx.x.xx
2852
pwd3478!#
cd ftphome
lcd DeliveryOrders
get 202406280102.FTPRCV C:\Reports\DTCC\dtcc

but it did not download

 

[Gasman]

Does it work if you do it manually?
You cannot do anything automated, if it does not work manually.
Examine those examples to see if anything is different as well as the get.

 

[Gasman]

Have you paid attention to winscp being case sensitive?
You have ftprcv and FTPRCV?, so which is it?

 

[prasadgov]

Have you paid attention to winscp being case sensitive?
You have ftprcv and FTPRCV?, so which is it?

I changed it to FTPRCV but still unable to download

 

[sonic8]

open 10.xxx.x.xx
2852
pwd3478!#
cd ftphome
lcd DeliveryOrders
get 202406280102.FTPRCV C:\Reports\DTCC\dtcc

What is the intention of the lcd DeliveryOrders command?
This makes no sense. It will change the local directory, but you explicitly pass a local directory path to get, so the lcd command will not have any effect.
Did you intend to change the server directory? Might this be the cause of your problem?

PS: @Gasman suggested multiple times to run your commands manually. I second this suggestion! - It appears you are persistently ignoring this advice. Why?

 

[ComputerVersteher]

You have to quote the path to exe as it contains blanks!
Error would have shown if you tested your Shell Command with a Debug.Print
[Edit added missing & after "winscp.exe"]

Dim shellCmd As String
shellCmd = strQuote & strSFTPDir & "winscp.exe" & strQuote & " " & strQuote & strCommand & strQuote '[Edit added missing & after "winscp.exe"]
Debug.Print shellCmd 'This output can be used for testing!
Call Shell(shellCmd , vbNormalNoFocus)

btw: why not use cURL, as it is buld in Windows?

 

[prasadgov]

What is the intention of the lcd DeliveryOrders command?
This makes no sense. It will change the local directory, but you explicitly pass a local directory path to get, so the lcd command will not have any effect.
Did you intend to change the server directory? Might this be the cause of your problem?

PS: @Gasman suggested multiple times to run your commands manually. I second this suggestion! - It appears you are persistently ignoring this advice. Why?

The remote location is ftphome/DeliveryOrders.
Also, I know the command to execute ftp is
ftp -d -n -s:c\folder\dtccwinscpfile.ftp

I am aware to pass winscp within as Shell Command but not sure how to execute it manually

 

[sonic8]

The remote location is ftphome/DeliveryOrders.

Then you should change the remote directory using the cd command not the local directory.

PS: Also pay attention to @ComputerVersteher's comment regarding the blanks in the path!

I am aware to pass winscp within as Shell Command but not sure how to execute it manually

There is a WinScp.com file in the WinScp directory. This is an interactive command line client, which allows you to run commands one by one.

 

[prasadgov]

Then you should change the remote directory using the cd command not the local directory.

PS: Also pay attention to @ComputerVersteher's comment regarding the blanks in the path!


There is a WinScp.com file in the WinScp directory. This is an interactive command line client, which allows you to run commands one by one.

I'll change it to cd /ftphome/DeliveryOrders and call using
winscp.com /ini=nul /script=dtccwinscpfile..txt

 

[sonic8]

I'll change it to cd /ftphome/DeliveryOrders

Yes!

and call using
winscp.com /ini=nul /script=dtccwinscpfile..txt

No! Winscp.com was only meant as a tool to manually test your commands.

 

[prasadgov]

Yes!


No! Winscp.com was only meant as a tool to manually test your commands.

then how to make the call for winscp?

 

[gemma-the-husky]

I'm not sure but don't you need a pipe symbol or a > to direct output to a disk file?

 

[sonic8]

then how to make the call for winscp?

Just run winscp.exe, exactly as you did before.

 

[prasadgov]

I solved the winscp issue by running it manually.
But one question. It ran when I put BAT file and the text script in the same location of the winscp.exe.
Previously, I was trying to run from other directory, it did not run. That is weird.
I am going to check this further and also try the Shell command to invoke it from Access.

Thanks All!