0

I'd like to automate the below steps using Excel VBA/VB Script. Here is the list of actions I do manually

Step1: login to winscp.

  • Hostname: example.ash.pwj.com
  • Port: 22
  • UsrName: xxx Pwd: yyyy
  • File Protocol: SFTP

transfer a file named 'testFile' from local machine to a Directory in Unix server using Winscp (the directory has a python script 'connector.py')

Step2: Open putty.

  • Destination Host Name: example.ash.pwj.com
  • Port: 22
  • Connection Type: SSH
  • Login using credentials.

Step3: call python script in the directory. python connector.py argument1 argument2 argument3

Example: python connector.py testFile example2.ash.pwj.com 123

I have to handle WINSCP file transfer and calling python script in putty using Excel VBA/VBScript.

Here is my code:

Set sh = CreateObject("WScript.Shell")

sh.Run "pscp -pw yyyy C:\Users\abc\testFile xxx@example.ash.pwj.com:/home/test/dir", 0, True
sh.Run "plink.exe -pw yyyy xxx@example.ash.pwj.com python /home/test/dir/connector.py testFile example2.ash.pwj.com 514", 0, True
Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Prasanna
  • 13
  • 6
  • possible duplicate of [How to call python script on excel vba?](http://stackoverflow.com/questions/18135551/how-to-call-python-script-on-excel-vba) – will Oct 17 '14 at 10:27
  • @will Not really a duplicate, since he wants to run a python script on a different host with a different operating system. – Ansgar Wiechers Oct 18 '14 at 16:08
  • That can all be done from inside the python script being called though, can it not? – will Oct 18 '14 at 16:50
  • @will The Python script is called on the remote (Unix) host. We don't know if a Python interpreter is installed on the Windows machine, or if it's possible for the OP to install one. – Ansgar Wiechers Oct 18 '14 at 21:18

1 Answers1

0

You could shell out to pscp and plink from the PuTTY suite:

Set sh = CreateObject("WScript.Shell")

sh.Run "pscp -pw yyyy c:\testFile xxx@example.com:/some/dir/", 0, True
sh.Run "plink -pw yyyy xxx@example.com python /some/dir/connector.py arg1 arg2 arg3", 0, True

Note that you need to add proper quoting if paths contain spaces.

If your destination path is just the destination directory without the filename: specify the path with a trailing filename. That way you'll get an error when the destination directory doesn't exist. Otherwise pscp file user@example.com:/some/dir might create a file /some/dir instead of /some/dir/file when there is no subdirectory dir in /some.

Also, you need to make sure that you pass the correct path to your Python script. When you copy the file to /home/test/dir/ you should either change into that directory before calling the Python script, or specify the filename with its full path:

sh.Run "plink.exe -pw yyyy xxx@example.ash.pwj.com python /home/test/dir/connector.py /home/test/dir/testFile example2.ash.pwj.com 514", 0, True

Running the Python script from /home/test/dir doesn't automatically make that directory the current working directory.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • I tried the above with appropriate paths. "The System cannot find file specified" error is thrown each time.! – Prasanna Oct 24 '14 at 06:14
  • @Prasanna Does any of the paths/filenames contain spaces? If so: did you quote the paths properly? – Ansgar Wiechers Oct 24 '14 at 06:52
  • Hi Ansgar, paths/filenames does not have spaces. Note: arg2 is a filename present in /some/dir – Prasanna Oct 24 '14 at 07:48
  • sh.Run "pscp -pw yyyy C:\Users\abc\testFile xxx@example.ash.pwj.com:/home/test/dir", 0, True || sh.Run "plink.exe -pw yyyy xxx@example.ash.pwj.com python /home/test/dir/connector.py testFile example2.ash.pwj.com 514", 0, True – Prasanna Oct 24 '14 at 07:54
  • @Prasanna Please don't post stuff like that in comments. Add it to your *question* instead. Is that code a single line in your script? If so: that won't work. `||` is a CMD operator. It's not valid in VBScript. – Ansgar Wiechers Oct 24 '14 at 08:55
  • @Angsar I have updated my question with the code. I used || as a separator for the two lines of code. Sorry for the inconvenience caused. – Prasanna Oct 24 '14 at 10:27