0

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:pwd123@999.999.999.999.sftp.stackoverflow.com/ -hostkey=""CopiedFromGenerateSessionURLCodeSSHHostKey""" "put c:\users\myname\path\filename.csv sftp.stackoverflow.com/users/path/" "exit""
bobby
  • 15
  • 1
  • 7
  • What is your question? – Inarion Sep 06 '18 at 14:54
  • ive updated my post to better reflect that i have no idea why its not working & was hoping someone here does. thx. – bobby Sep 06 '18 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](https://stackoverflow.com/q/37280733/850848#37284826). – Martin Prikryl Sep 07 '18 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 Sep 10 '18 at 11:52

1 Answers1

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:pwd123@Stackoverflow.com/ as the first part of your command string.

Inarion
  • 578
  • 3
  • 14
  • 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 /) – bobby Sep 07 '18 at 17:26
  • @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. – Inarion Sep 10 '18 at 11:48