-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
SCP (secure copy) via VBA
- Thread starter Dohko
- Start date
xenou
MrExcel MVP
- Joined
- Mar 2, 2007
- Messages
- 16,836
- Office Version
- 2019
- Platform
- Windows
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.
Upvote
0
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.
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.
Upvote
0
xenou
MrExcel MVP
- Joined
- Mar 2, 2007
- Messages
- 16,836
- Office Version
- 2019
- Platform
- Windows
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).
Upvote
0
xenou
MrExcel MVP
- Joined
- Mar 2, 2007
- Messages
- 16,836
- Office Version
- 2019
- Platform
- Windows
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
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
Code:
[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]
Upvote
0
xenou
MrExcel MVP
- Joined
- Mar 2, 2007
- Messages
- 16,836
- Office Version
- 2019
- Platform
- Windows
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)
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)
Upvote
0
