(Excel) AWS Transfer for SFTP (Amazon S3)

Once you've setup your AWS Transfer for SFTP in the AWS Console, interacting with it is no different than any other SSH/SFTP server. AWS will provide a private key in PEM format. It is used for authentication (instead of a password).

For more information, see https://docs.aws.amazon.com/transfer/latest/userguide/getting-started-use-the-service.html

Download Excel Class Modules Chilkat Excel Class Modules

' This example assumes the Chilkat API to have been previously unlocked.
' See Global Unlock Sample for sample code.

Dim sftp As Chilkat.SFtp
Set sftp = Chilkat.NewSFtp

' Connect to the AWS SFTP server.
' Change the domain to your value.

domain = "s-123456df999999fab.server.transfer.eu-west-2.amazonaws.com"

port = 22

success = sftp.Connect(domain,port)
If (success = False) Then
    Debug.Print sftp.LastErrorText
    Exit Sub
End If

' Load your AWS SFTP private key PEM file..
Dim sshKey As Chilkat.SshKey
Set sshKey = Chilkat.NewSshKey

keyText = sshKey.LoadText("qa_data/pem/s3_sftp_privateKey.pem")
If (sshKey.LastMethodSuccess <> True) Then
    Debug.Print sshKey.LastErrorText
    Exit Sub
End If

success = sshKey.FromOpenSshPrivateKey(keyText)
If (success = False) Then
    Debug.Print sshKey.LastErrorText
    Exit Sub
End If

' Authenticate with the SSH server using the private key.
success = sftp.AuthenticatePk("myUsername",sshKey)
If (success = False) Then
    Debug.Print sftp.LastErrorText
    Exit Sub
End If

' After authenticating, the SFTP subsystem must be initialized:
success = sftp.InitializeSftp()
If (success = False) Then
    Debug.Print sftp.LastErrorText
    Exit Sub
End If

' Upload from the local file to the SSH server.
' Important -- the remote filepath is the 1st argument,
' the local filepath is the 2nd argument;

remoteFilePath = "hamlet.xml"

localFilePath = "c:/temp/hamlet.xml"

success = sftp.UploadFileByName(remoteFilePath,localFilePath)
If (success = False) Then
    Debug.Print sftp.LastErrorText
    Exit Sub
End If

Debug.Print "Success."