I have a macro in my excel file that uploads a file to my server using the pscp (putty client).
This is the code
Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
Dim cstrSftp As String
Dim strCommand As String
Dim pUser As String
Dim pPass As String
Dim pHost As String
Dim pFile As String
Dim pRemotePath As String
'Specifying the values
cstrSftp = """" & Application.ActiveWorkbook.Path & "\pscp.exe" & """"
pUser = "username"
pPass = "password "
pHost = "ftp.xyz.com"
pFile = """" & Application.ActiveWorkbook.Path & "\test.mkv" & """"
pRemotePath = "/home/storage/public_html/"
'Setting the command
strCommand = cstrSftp & " -sftp -l " & pUser & " -pw " & pPass & _
" " & pFile & " " & pHost & ":" & pRemotePath
'Execution
wsh.Run strCommand, windowStyle, waitOnReturn
This works fine, but i have two problems:
1) Using Windows 10 the system popup a message asking to the user if he wants to run the pscp.exe. I would like to bypass that.
Instead of WScript.Shell i was using this:
Call Shell(strCommand, vbNormalFocus)
Using Call Shell the operation system don't ask about running the pscp.exe, but the problem is i don't have a way to know when the process is over
2) When the user runs the command for the first time, putty prompt this message:
The server's host key is not cached in the registry. You have no guarantee that the server is the computer you think it is. The server's rsa2 key > fingerprint is: [ssh-rsa 1024 somekey] If you trust this host, enter "y" to add the key to PuTTY's cache and carry on connecting. If you want to carry on connecting just once, without adding the key to the cache, enter "n". If you do not trust this host, press Return to abandon the connection. Store key in cache? (y/n)
I need it to be completely prompt free, automatic.
I tried to use "echo n" like this:
strCommand = "echo n | " & cstrSftp & " -sftp -l " & pUser & " -pw " & pPass & _ " " & pFile & " " & pHost & ":" & pRemotePath
But i get the message that the "IWshShell3" failed.
