UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Create A Vbscript To Run Sftp.exe And Upload A File, Access 2016    
 
   
Louverril
post Sep 13 2018, 01:29 PM
Post#1



Posts: 467
Joined: 29-April 08



Hello,

I can transfer a file to an sftp site using the COMMAND LINE - this sort of thing: https://www.digitalocean.com/community/tuto...tions-with-sftp

e.g. sftp username@host etc...


I want to write a basic VBA function / vbscript put it behind a command button to upload a file name I send. It will be a very simple controlled event.

I do NOT want to use an outside program like WinSCP or Putty.

The thing is I cannot shell to C:\Windows\System32\OpenSSH\sftp.exe from VBA

I can to FTP
CODE
Call Shell("C:\Windows\System32\ftp.exe", vbNormalFocus)


But this below just returns Error 53 File Not Found:
CODE
Call Shell("C:\Windows\System32\OpenSSH\sftp.exe", vbNormalFocus)


I got somewhere using Powershell

CODE
Dim retval As Variant
Dim strmySFTPCall As String

strmySFTPCall = "PowerShell -Command ""sftpusername@host"""
retval = Shell(strmySFTPCall, vbNormalFocus)


This makes the powershell window flash up for a few seconds!

Can anyone get me started writing a vbscript to powershell? Or VBA if it is possible?

Many thanks in advance.


--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
cheekybuddha
post Sep 13 2018, 02:34 PM
Post#2


UtterAccess VIP
Posts: 10,337
Joined: 6-December 03
From: Telegraph Hill


QUOTE
The thing is I cannot shell to C:\Windows\System32\OpenSSH\sftp.exe from VBA


Are you sure that sftp.exe is in System32 folder.

I installed OpenSSH the other day and it placed the folder under C:\

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Sep 13 2018, 02:38 PM
Post#3


UtterAccess VIP
Posts: 10,337
Joined: 6-December 03
From: Telegraph Hill


Oops! I was thinking of OpenSSL for Windows blush.gif

Have you done something like this to enable OpenSSH?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Sep 13 2018, 02:41 PM
Post#4


UtterAccess VIP
Posts: 10,337
Joined: 6-December 03
From: Telegraph Hill


For sftp you probably need to install a client like WinSCP or PuTTY

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Sep 13 2018, 02:46 PM
Post#5


UtterAccess VIP
Posts: 10,337
Joined: 6-December 03
From: Telegraph Hill


Here's a StackOverflow answer if you wish to pursue the PowerShell route

--------------------


Regards,

David Marten
Go to the top of the page
 
Louverril
post Sep 14 2018, 10:26 AM
Post#6



Posts: 467
Joined: 29-April 08



Thanks vey much cheekybuddha for the suggestions! :-)

I really don't want to rely on third party software if I can.

I might struggle on for a while with this as a start to see if I can get something that will run the powershell commands:

CODE
Dim retval As Variant
Dim strmySFTPCall As String

strmySFTPCall = "PowerShell -Command ""sftpusername@host"""
retval = Shell(strmySFTPCall, vbNormalFocus)


Your suggestion about this https://www.bleepingcomputer.com/news/micro...openssh-client/ might be useful for that sort of approach as well.

Thank you - I will report back!

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
Louverril
post Today, 09:37 AM
Post#7



Posts: 467
Joined: 29-April 08



Not getting very far with this... :-(

I am trying to do the button to sftp (upload) a file from an Access form without having to rely on too much being installed on the client PC (which I don't control). The only solution I am getting near to is the client having to have Powershell installed; be able to run Powershell and also have and be able to run Posh SSH. Which in the environment the system is being used in could be a nightmare.

Is there simpler way - I would be grateful for any ideas!!!!

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
JonSmith
post Today, 09:47 AM
Post#8



Posts: 3,806
Joined: 19-October 10



I meant to post on this the other day and forgot.

I recently changed company and now need to do lots with FTP.
I found the options in VBA lacking.
There was one big set of code I found that did what I needed but it wasn't particularly slick nor efficient.

So I wrote a set of classes to handle it. There is the FTP class which handles the connection, it has classes for the folders and files within the FTP too so you can can basically connect to a folder and see a collection of files and folders present and do actions of them (Download, delete, upload, rename etc).
It all uses Windows API's.

I am not sure of the difference between FTP and sFTP but perhaps my code will work for you?

JS
Go to the top of the page
 
Louverril
post Today, 10:17 AM
Post#9



Posts: 467
Joined: 29-April 08



Hello John,

Thanks for the reply. I am OK with FTP its the SFTP that is the problem - it is different.

There is no password in the window sftp.exe command to allow you to specify one - as far as I can see - (list below).

Also Shell will not work to sftp :-( - it has to be Pawershell.

CODE
'sftp> help
'Available commands:
'bye                                Quit sftp
'cd path                            Change remote directory to 'path'
'chgrp grp path                     Change group of file 'path' to 'grp'
'chmod mode path                    Change permissions of file 'path' to 'mode'
'chown own path                     Change owner of file 'path' to 'own'
'df [-hi] [path]                    Display statistics for current directory or
'                                   FileSystem containing 'path'
'exit                               Quit sftp
'get [-afPpRr] remote [local]       Download file
'reget [-fPpRr] remote [local]      Resume download file
'reput [-fPpRr] [local] remote      Resume upload file
'help                               Display this help text
'lcd path                           Change local directory to 'path'
'lls [ls-options [path]]            Display local directory listing
'lmkdir path                        Create local directory
'ln [-s] oldpath newpath            Link remote file (-s for symlink)
'lpwd                               Print local working directory
'ls [-1afhlnrSt] [path]             Display remote directory listing
'lumask umask                       Set local umask to 'umask'
'mkdir path                         Create remote directory
'progress                           Toggle display of progress meter
'put [-afPpRr] local [remote]       Upload file
'pwd                                Display remote working directory
'quit                               Quit sftp
'rename oldpath newpath             Rename remote file
'rm path                            Delete remote file
'rmdir path                         Remove remote directory
'symlink oldpath newpath            Symlink remote file
'version                            Show SFTP version
'!command                           Execute 'command' in local shell
'!                                  Escape to local shell
'?                                  Synonym for help

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
cheekybuddha
post Today, 10:19 AM
Post#10


UtterAccess VIP
Posts: 10,337
Joined: 6-December 03
From: Telegraph Hill


Hi Allyson,

Are you able to get Posh SSH installed in the PowerShell?

If you can, then the trick would be to write the PowerShell script to a local file, then call Shell() to run PowerShell with the script as an argument (theoretically!! wary.gif )

It's a shame you can't use putty (or rather pscp.exe). Then it's very easy.

As far as I remember, you don't need to even install pscp.exe - I'm pretty sure it's a standalone executable. You could store it in your database and then write it to the same folder that the database resides, and then use it from there.

@Jon, I think the point is not to use insecure FTP! (SFTP is FTP over a secure shell (SSH), but you need additional software to create the secure shell).

--------------------


Regards,

David Marten
Go to the top of the page
 
JonSmith
post Today, 10:23 AM
Post#11



Posts: 3,806
Joined: 19-October 10



Yeah, I get that David. I just thought maybe my code could work.
It uses the wininet.dll rather than any batch files or the like so gives alot more control hence all the classes I built around it to handle all the folders files and actions.

I just checked though and the first Google hit says that wininet.dll doesn't work with sftp.

Maybe there is another API that can be used?
Go to the top of the page
 
Louverril
post Today, 10:48 AM
Post#12



Posts: 467
Joined: 29-April 08



Thanks David,

I have downloaded Puttey and found the pscp.exe file.

Do I have to use Powershell with Putty?

I did get Posh SSH installed in Powershell. But could not get any code to connect wit a password - yet anyway.


--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
cheekybuddha
post Today, 11:26 AM
Post#13


UtterAccess VIP
Posts: 10,337
Joined: 6-December 03
From: Telegraph Hill


>> Do I have to use Powershell with Putty? <<
No

See the last response in the StackOverflow post.

Try something like this aircode:
CODE
Function SFTPUpload() As Boolean

  Const PSCP As String = "pscp.exe"
  Dim strPSCPpath As String, _
      strRemoteServer As String, _
      strRemoteUser As String, _
      strRemoteUserPW As String, _
      strRemotePath As String, _
      strFileToUpload As String, _
      strCmd AS String, _
      blRet As Boolean

  strPSCPpath = Chr(34) & "C:\the\folder\where\you\have\saved\" & PSCP & Chr(34)    ' If it's in your db folder you can use: strPSCPpath = CurrentProject.Path & "\" & PSCP
  strRemoteServer = "111.222.123.234"   ' or domain address
  strRemoteUser = "Louverril"    ' remote user account name
  strRemoteUserPW  = "XXXXXXX"
  strRemotePath  = "ftp_uploads/"    ' remote folder path
  strFileToUpload = Chr(34) & "C:\local\path\to\file\to\upload.txt" & Chr(34)

  strCmd = strPSCPpath & " -sftp -pw " & strRemoteUserPW  & " " & strFileToUpload & " " & strRemoteUser & "@" & strRemoteServer & ":" & strRemotePath  
  blRet = Shell(strCmd)

  SFTPUpload = blRet

End Function


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Today, 11:47 AM
Post#14


UtterAccess VIP
Posts: 10,337
Joined: 6-December 03
From: Telegraph Hill


I just gave the code a test, and it returned true, but did not transfer the file.

So I did a Debug.Print of strCmd and tried it in a command prompt.

Since it was the first time connecting to the server it interactively asked whether you want to make the transfer because the server's key is not cached in the registry. I chose 'Yes' and the file transferred.

I deleted the file from the server and ran the code again - the file transferred fine.

So, you may have an issue with first run!! I'm not sure how to get around that. Does your server use a Public Key?

--------------------


Regards,

David Marten
Go to the top of the page
 
Louverril
post Today, 11:56 AM
Post#15



Posts: 467
Joined: 29-April 08



Thanks LOADS!!! David,

I have managed to upload a test file. Need to uninstall Putty now and just keep the pscp.exe file to make sure it will work on its own.

Then I will try your idea of: - "You could store it in your database and then write it to the same folder that the database resides, and then use it from there."

That should be manageable.

MANY MANY THANKS!!!!! notworthy.gif

--------------------
Cheers!

Allyson
UK North Yorkshire / North East
Go to the top of the page
 
cheekybuddha
post Today, 11:57 AM
Post#16


UtterAccess VIP
Posts: 10,337
Joined: 6-December 03
From: Telegraph Hill


yw.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th September 2018 - 04:15 PM