VBA Scripts to Control Win SCP actions

Advertisement

dgreenac
Joined:
Posts:
3

VBA Scripts to Control Win SCP actions

I have started playing with VBA in order to automate some of the workload within my team. We use WinSCP to house our files and therefore I would ultimately like to build a script that downloads a daily file from the FTP to a local directory and has them ready for working at 6am every morning.

I have designed a script based on 'SendKeys' that logs the user onto the WinSCP GUI, however downloading a file from the FTP directory and relocating to a local directory is becoming quite a challenge.

I'm not sure if I'm approaching this in the incorrect way... or need to look at an alternative.

I have seen that Win WCP supports 'command lines' however I don't really have a clue what this means, I think I might need to design a 'session' and interact via the command lines. However I don't know where to start.

My code so far is as below:

[Code]

Sub Login_SCP_FTP()

'Open WinSCP and Determine it as a object
Dim WinSCP_Start_App As Long
WinSCP_Start_App = shell("C:\Program Files\WinSCP\WinSCP.exe <IP_ADDRESS>", vbNormalFocus) 'location of the WinSCP exe and also the IP/Host Name

Dim WinSCP_Username As String
WinSCP_Username = "<unsername>" 'Username

Dim WinSCP_Password As String
WinSCP_Password = "<PASSWORD>" 'Password

'Variables the indicate the Application Window Names for each stage
Dim WinSCP_Login_Step1 As String
WinSCP_Login_Step1 = "Username - <IP_ADDRESS>" 'Login app step1 for username entry

Dim WinSCP_Login_Step2 As String
WinSCP_Login_Step2 = "Password - <IP_ADDRESS>" 'Login app step1 for password entry

Dim WinSCP_Active_App As String
WinSCP_Active_App = "<IP_ADDRESS> - WinSCP" 'Application window name once login is sucessful


Application.Wait (Now + TimeValue("00:00:09"))
AppActivate WinSCP_Login_Step1, False
SendKeys WinSCP_Username 'Server Username
SendKeys "{TAB}~"
Application.Wait (Now + TimeValue("00:00:02"))
AppActivate WinSCP_Login_Step2, False
SendKeys WinSCP_Password 'Server Password
SendKeys "{TAB}{TAB}~"


Application.Wait (Now + TimeValue("00:00:10"))
AppActivate WinSCP_Active_App, False
SendKeys "%"

'Further script required to locate file and copy from SCP and move to local drive

End Sub


[Code/]

Reply with quote

Advertisement

dgreenac
Joined:
Posts:
3

Thanks.

I have read through the documentation and understand that a command line is used to trigger a request through the 'WinSCP.com' executable. I want to copy a file to a local directly, so understand that the 'GET' command is necessary.

However I assume I need to create some kind of 'session' before I can send commands to WinSCP? What type of code is used in VBA to achieve this? I have tried to search, but I think I am asking the wrong questions.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
40,476
Location:
Prague, Czechia

First, you do not need VBA at all for this! You can achieve this from command-line only. Of course you can execute that command-line from VBA, if you need to integrate this to Excel for example. Use the shell command, as per your sample code.

The session is opened using open command from WinSCP script. You can see that in the guide I've pointed you to already.

Reply with quote

dgreenac
Joined:
Posts:
3

Thank you, the guide doesn't appear to be aimed at novices and is the reason it doesn't make 100% sense to me.

I will do some research based on what you have stated and see how far I can get. I'm trying to learn a new skill, so I need to keep plugging away.

Appreciate your help / patience.

Reply with quote

Advertisement

You can post new topics in this forum