Forum » Scripting / Automation »

File transfer by VBA fails

Advertisement

oliver
Joined:
Posts:
3
Location:
Germany

File transfer by VBA fails

Hello,
I use WinSCP 4.5 and have registered the WinSCPnet.dll.

With the code below I receive an error
Session.protocol is Protocol.Sftp od Protol.Scp, but SessionOptions.SshHostKeyFingerprint not set.
In the logfile I can see, that FTPS is used as protocol, but with FTPSecure = implicit (although I have .FtpSecure = 1 in my code): 
. 2025-04-29 13:15:30.111 FTPS: Implicit TLS/SSL [Client certificate: No]
I tried anything but cannot find the mistake. Could Maybe anoyone can give me a hint?

Thanks in advance
Oliver

Code: 
Dim session As Object
Dim sessionOptions As Object
 
' Create the WinSCP Session Options
Set session = CreateObject("WinSCP.Session")
Set sessionOptions = CreateObject("WinSCP.SessionOptions")
       
session.SessionLogPath = "C:\Temp\winscp_ftps.log"
 
With sessionOptions
    .protocol = 1 'FTPS
    .HostName = ftpserver
    .PortNumber = 21
    .UserName = UserName
    .Password = Password
    .FtpMode = 1 
    .FtpSecure = 1 ' Explicit TLS/SSL
    .GiveUpSecurityAndAcceptAnyTlsHostCertificate = True
End With
 
session.Open sessionOptions

Reply with quote

Advertisement

martin◆
Site Admin
martin avatar
Joined:
Posts:
41,983
Location:
Prague, Czechia

Re: File transfer by VBA fails

Protocol = 1 means SCP. For FTP, use 2.
Though obviously better is to use the keyword Ftp than magic numbers.

Reply with quote

oliver
Joined:
Posts:
3
Location:
Germany

Thanks for your quick answer.
  1. I tried to set option by keyword (e.g. Ftp), but it seemingly does not work.
  2. I now set protocol = 2, but still receive an error message.
  3. The log file says: FTPS: Implicit TLS/SSL (see below), while I am trying to connect by FTPS explicit at port 21 by setting FtpSecure = 1. 
. 2025-05-01 07:32:07.363 Session name: XXX@ftp.XXXXX.de (Ad-Hoc site)
. 2025-05-01 07:32:07.363 Host name: ftp.XXXXX.de (Port: 21)
. 2025-05-01 07:32:07.363 User name: XXX (Password: Yes, Key file: No, Passphrase: No)
. 2025-05-01 07:32:07.363 Transfer Protocol: FTP
. 2025-05-01 07:32:07.363 Ping type: Dummy, Ping interval: 30 sec; Timeout: 15 sec
. 2025-05-01 07:32:07.363 Disable Nagle: No
. 2025-05-01 07:32:07.363 Proxy: None
. 2025-05-01 07:32:07.363 Send buffer: 262144
. 2025-05-01 07:32:07.363 UTF: Auto
. 2025-05-01 07:32:07.363 FTPS: Implicit TLS/SSL [Client certificate: No]
. 2025-05-01 07:32:07.364 FTP: Passive: No [Force IP: Auto]; MLSD: Auto [List all: Auto]; HOST: Auto
. 2025-05-01 07:32:07.364 Session reuse: Yes
. 2025-05-01 07:32:07.364 TLS/SSL versions: TLSv1.2-TLSv1.3
. 2025-05-01 07:32:07.364 Local directory: default, Remote directory: home, Update: Yes, Cache: Yes
. 2025-05-01 07:32:07.364 Cache directory changes: Yes, Permanent: Yes
. 2025-05-01 07:32:07.364 Recycle bin: Delete to: No, Overwritten to: No, Bin path: 
. 2025-05-01 07:32:07.364 Timezone offset: 0h 0m

Reply with quote

martin◆
Site Admin
martin avatar
Joined:
Posts:
41,983
Location:
Prague, Czechia

The keyword is Protocol_Ftp:
https://winscp.net/eng/docs/library_vb#enums
Something doesn't tick here though. If you get "Session.protocol is Protocol.Sftp od Protol.Scp, but SessionOptions.SshHostKeyFingerprint not set", then the session is not even started and NO session log file should be created. Maybe you are looking at a wrong/old log.
Post you complete current code and exact error you are getting from it.

Reply with quote

oliver
Joined:
Posts:
3
Location:
Germany

  1. The logfile is created properly

  2. Complete code: 
    Public Sub FTPupload(localFile As String, ftpserver As String, UserName As String, Password As String, remoteFile As String)
 
  Dim session As Object
  Dim sessionOptions As Object
 
  ' Create the WinSCP Session Options
  Set session = CreateObject("WinSCP.Session")
  Set sessionOptions = CreateObject("WinSCP.SessionOptions")
 
  session.SessionLogPath = "C:\Temp\winscp_ftps.log"
 
  With sessionOptions
    .protocol = 2 '= FTPS
    .HostName = ftpserver
    .PortNumber = 21
    .UserName = UserName
    .Password = Password
    .FtpMode = 1 ' Passive / Active
    .FtpSecure = 1 ' Explicit TLS/SSL
    .GiveUpSecurityAndAcceptAnyTlsHostCertificate = True 
  End With
 
  session.Open sessionOptions
 
  session.Dispose
 
End Sub
  3. Received Errormessage (translated from german): "Connection failed"
  4. Logfile 
    . 2025-05-05 12:19:26.433 --------------------------------------------------------------------------
. 2025-05-05 12:19:26.436 WinSCP Version 6.5 (Build 16288 2025-03-31) (OS 10.0.26100 – Windows 11 Pro)
. 2025-05-05 12:19:26.436 Configuration: nul
. 2025-05-05 12:19:26.436 Log level: Normal
. 2025-05-05 12:19:26.436 Local account: XXX
. 2025-05-05 12:19:26.436 Working directory: C:\Program Files (x86)\WinSCP
. 2025-05-05 12:19:26.436 Process ID: 2460
. 2025-05-05 12:19:26.453 Ancestor processes: MSACCESS, MSACCESS, explorer, ...
. 2025-05-05 12:19:26.454 Command-line: "C:\Program Files (x86)\WinSCP\winscp.exe" /xmllog="C:\Users\XX.XXX.000\AppData\Local\Temp\wscp2488.01F550A4.tmp" /xmlgroups /xmllogrequired /nointeractiveinput /stdout /stdin /dotnet=6.5.0  /ini=nul /log="C:\Temp\winscp_ftps.log"  /console /consoleinstance=_9352_27252167_827
. 2025-05-05 12:19:26.460 Time zone: Current: GMT+2, Standard: GMT+1 (Mitteleuropäische Zeit), DST: GMT+2 (Mitteleuropäische Sommerzeit), DST Start: 30.03.2025, DST End: 26.10.2025
. 2025-05-05 12:19:26.460 Login time: Montag, 5. Mai 2025 12:19:26
. 2025-05-05 12:19:26.460 --------------------------------------------------------------------------
. 2025-05-05 12:19:26.460 Script: Retrospectively logging previous script records:
> 2025-05-05 12:19:26.460 Script: option batch on
< 2025-05-05 12:19:26.460 Script: batch           on        
< 2025-05-05 12:19:26.460 Script: reconnecttime   120       
> 2025-05-05 12:19:26.460 Script: option confirm off
< 2025-05-05 12:19:26.460 Script: confirm         off       
> 2025-05-05 12:19:26.460 Script: option reconnecttime 120
< 2025-05-05 12:19:26.460 Script: reconnecttime   120       
> 2025-05-05 12:19:26.460 Script: open ftp://int:***@ftp.XXX.de:21 -implicit -certificate="*" -passive=0 -timeout=15
. 2025-05-05 12:19:26.460 --------------------------------------------------------------------------
. 2025-05-05 12:19:26.460 Session name: int@ftp.XXX.de (Ad-Hoc site)
. 2025-05-05 12:19:26.460 Host name: ftp.XXX.de (Port: 21)
. 2025-05-05 12:19:26.460 User name: int (Password: Yes, Key file: No, Passphrase: No)
. 2025-05-05 12:19:26.460 Transfer Protocol: FTP
. 2025-05-05 12:19:26.460 Ping type: Dummy, Ping interval: 30 sec; Timeout: 15 sec
. 2025-05-05 12:19:26.460 Disable Nagle: No
. 2025-05-05 12:19:26.460 Proxy: None
. 2025-05-05 12:19:26.460 Send buffer: 262144
. 2025-05-05 12:19:26.460 UTF: Auto
. 2025-05-05 12:19:26.460 FTPS: Implicit TLS/SSL [Client certificate: No]
. 2025-05-05 12:19:26.460 FTP: Passive: No [Force IP: Auto]; MLSD: Auto [List all: Auto]; HOST: Auto
. 2025-05-05 12:19:26.460 Session reuse: Yes
. 2025-05-05 12:19:26.460 TLS/SSL versions: TLSv1.2-TLSv1.3
. 2025-05-05 12:19:26.460 Local directory: default, Remote directory: home, Update: Yes, Cache: Yes
. 2025-05-05 12:19:26.460 Cache directory changes: Yes, Permanent: Yes
. 2025-05-05 12:19:26.460 Recycle bin: Delete to: No, Overwritten to: No, Bin path: 
. 2025-05-05 12:19:26.460 Timezone offset: 0h 0m
. 2025-05-05 12:19:26.460 --------------------------------------------------------------------------
< 2025-05-05 12:19:26.495 Script: Verbindung mit ftp.XXX.de wird hergestellt …
. 2025-05-05 12:19:26.495 Verbindung mit ftp.XXX.de wird hergestellt …
. 2025-05-05 12:19:26.509 Verbindung ist fehlgeschlagen.
< 2025-05-05 12:19:26.513 Script: Verbindung ist fehlgeschlagen.
< 2025-05-05 12:19:26.513 Verbindung ist fehlgeschlagen.

Reply with quote

Advertisement

martin◆
Site Admin
martin avatar

So you are a getting completely different error.
FtpSecure.Explicit is 3, not 1.
Though the error, you are getting now, implies that the server is not even responding on the port 21. So the problem is different. Can you connect in WinSCP GUI on the same machine? Can you post its log file?

Reply with quote

Advertisement

You can post new topics in this forum

Documentation

Support

Associations

Follow Us