SCP (secure copy) via VBA

[Dohko]

Hi,

Is is possible to use this command to copy a file to a server?

Thanks.

 

[xenou]

SCP is a unix/linux command, usually run from the command line. Is your system really a *nix system (?). Generally, in windows you would use (maybe) sftp instead of scp, or open a vpn connection. I have not seen much here about automating this kind of work with vba - I've not done so myself either so I'm not really going to be much of a help but if you can describe more about what's going on then maybe someone will have more info.

 

[Dohko]

Well I'm running an Excel Macro that needs to download a file, process some data, and upload the output.
I know how to do it via FTP but I was wondering if there is a more secure way of doing that.
Perhaps calling a Putty or something like that.

 

[xenou]

That's probably a good way to go but I don't have any practical examples. It might be best to try to make sure it works using a gui client or running it from the dos prompt, before working out any vba (which might really end up being a command line script called from vba).

 

[Dohko]

Hi

I'm able to call putty with this command.
wsh2 = Shell("C:\putty.exe 137.192.139.81 -l user -pw pass", 1)
However it won't login?
How can I pass commands to the program?

 

[xenou]

I think you need to download not puTTy but pscp which is part of the "putty suite" (if you will):
http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

The pscp.exe program will allow for secure file transfers. Actually using the psftp.exe program is another option - I didn't get that one working and its late so I'll cut my losses tonight.

Here's a script at stackoverflow which worked beautifully for me. I downloaded the windows installer from the above site which included all of the programs except puTTYtel. I think it's probably necessary to have an SSH server running on the remote machine - it wouldn't hurt to test if you can connect using putty from the putty program in the start menu. Just login to a command prompt if you can. Then if you succeed in logging in, try the file transfer next.

Script from stack overflow:
http://stackoverflow.com/questions/3754928/sftp-upload-with-vba

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] SftpPut()
    [COLOR="Navy"]Const[/COLOR] cstrSftp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = """C:\Program Files\PuTTY\pscp.exe"""
    [COLOR="Navy"]Dim[/COLOR] strCommand [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] pUser [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] pPass [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] pHost [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] pFile [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] pRemotePath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    pUser = "username" [COLOR="SeaGreen"]'//user on remote system[/COLOR]
    pPass = "password" [COLOR="SeaGreen"]'//user's password on remote system[/COLOR]
    pHost = "192.168.1.100" [COLOR="SeaGreen"]'//ip address of remote system[/COLOR]
    pFile = "C:\puttytest.txt" [COLOR="SeaGreen"]'//file to transfer[/COLOR]
    pRemotePath = "/home/username/" [COLOR="SeaGreen"]'//directory where file will be transferred to[/COLOR]

    strCommand = cstrSftp & " -sftp -l " & pUser & " -pw " & pPass & _
        " " & pFile & " " & pHost & ":" & pRemotePath
    [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] strCommand
    Shell strCommand, 1 [COLOR="SeaGreen"]' vbNormalFocus '[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Dohko]

But how do I pass the commands to the Unix shell?
I need to use it to download something (wget) and upload some files.

 

[Dohko]

Can I use this to download a file from a HTTPS server?

 

[xenou]

Hi,
Regarding an https connection, I don't think that would work. It might be irrelevant, though. SSH works differently than SSL (which is what HTTPS typically uses). If you connect to a machine via SSH then the connection is encrypted, but it won't be running in an SSL layer. If you have an HTTPS connection then you don't need SSH, and vice versa. Clear as mud?

To pass commands to a unix shell you can and probably should use your puTTY tools. puTTY can open up a secure shell that you can type in commands right at the keyboard. The putty "suite" offers as well psftp.exe which you can use for secure file transfers (probably both at the command line and scripted - if scripted, it is no more than the same commands you would type at the command line, just automated). Not be be outdone, puTTY also gives you pscp.exe which allows you to use scp for secure file transfers. This last is what I tested and provided some code for above.

Bottom line - if you connect to a *nix server you'll have all the SSH tools at your service, and puTTY will give you a windows client to make use of the same protocols. The *nix server must be listening for SSH traffic. By default that's on port 22 but some admins use a different port for ssh traffic.

HTH - it's a bit of trial and error at first. I suggest you attempt to simply open a puTTY shell to see if you can access the server at all (assuming you have a user account to access. Just open puTTY.exe from the start menu and try to open a connection --> username@192.168.1.100)