SFTP Upload from MS-Access using VBA and WINSCP [duplicate]

Question

please see code below, this was pulled from another site and i amended with my details. i am trying to run an automatic upload to my SFTP site based on a trigger event. for some reason, it is not working. please let me know if there are any issues with what i have done. code debugger from immediate window is found below.

Private Sub Form_Close()

  Const CMD_WINSCP As String = _
    "WinSCP.com /Command ""open sftp://user:password@server/ -hostkey=""""fingerprint"""""" ""put local remote"" ""exit"""""

  Dim User As String
  Dim Password As String
  Dim Fingerprint As String
  Dim LocalFile As String
  Dim RemotePath As String
  Dim Command As String
  Dim Server As String

  'TODO: Initialize parameters.
  User = "Inarion"
  Password = "pwd123"
  Fingerprint = "CopiedFromGenerateSessionURLCodeSSHHostKey"
  LocalFile = "c:\users\myname\path\filename.csv"
  RemotePath = "stackoverflow.com/users/path/"
  Server = "999.999.999.999.sftp.stackoverflow.com" ' IPv4 should work as well


  Command = CMD_WINSCP
  Command = Replace(Command, "user", User)
  Command = Replace(Command, "password", Password)
  Command = Replace(Command, "fingerprint", Fingerprint)
  Command = Replace(Command, "local", LocalFile)
  Command = Replace(Command, "remote", RemotePath)
  Command = Replace(Command, "server", Server)
  Debug.Print Command
  Shell Command

from Immediate window:
WinSCP.com /Command "open sftp://Inarion:[email protected]/ -hostkey=""CopiedFromGenerateSessionURLCodeSSHHostKey""" "put c:\users\myname\path\filename.csv sftp.stackoverflow.com/users/path/" "exit""

ive updated my post to better reflect that i have no idea why its not working & was hoping someone here does. thx. — bobby
– bobby, Commented Sep 6, 2018 at 15:55
How did you secure that VBA code can find winscp.com? Do you have it in PATH? If not, use a full path to winscp.com instead, as show for example in Using VBA to run WinSCP script. — Martin Prikryl
– Martin Prikryl, Commented Sep 7, 2018 at 20:27
To connect to your server I think you either use an sftp address or an IP address. I can't imagine the combination to work. Do you have a means of otherwise verifying that you're using the correct address? (Thinking of entering it in the browser, or Ping-ing it via the command window.) As long as that address remains unreachable, your script won't work... — Inarion
– Inarion, Commented Sep 10, 2018 at 11:52

Inarion · Accepted Answer · 2018-09-07 06:57:16Z

0

So it seems you're asking about the connection string specifically. I'm 99% sure the one you have isn't correct. You need to put the server (IP) address in there (and not only in the remote folder). Especially since the password assignment states ActualSFTPPassword,butNo@orDomain.

I can't really tell where your connection string is coming from, but assuming you got it from the official documentation, I can't identify a syntax error.

Please add the following three lines to your code (in the appropriate place):

Dim Server as String
Server = "yourTargetDomain.com" ' IPv4 should work as well
Command = Replace(Command, "server", Server)

and try again.

An example: Starting from sftp://user:password@server/ with the username Inarion and the fictional password pwd123 and the target domain Stackoverflow.com you'll get sftp://inarion:[email protected]/ as the first part of your command string.

answered Sep 7, 2018 at 6:57

Inarion

6164 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bobby Over a year ago

updated the code above-please let me know if i've misunderstood - still not working - is it weird that in my immediate window message from FROM spot & TO spot are only separated by a space? i wouldve thought there would be some kind of other indicator (ie " or , or /)

Inarion Over a year ago

@bobby I wouldn't consider space-separated parameters unusual. In such environments, parameters that contain a space would then (have to) be enclosed by quotation marks.

Collectives™ on Stack Overflow

SFTP Upload from MS-Access using VBA and WINSCP [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related