Can I use Session.EnumerateRemoteFiles() in VBA? :: Support Forum

ScruffR
Joined:: 2020-03-05
Posts:: 5

Can I use Session.EnumerateRemoteFiles() in VBA?

2020-03-13 13:54

I have browsed the forum and found some posts that stated it should be possible
(as in the final post in this thread https://winscp.net/forum/viewtopic.php?t=23824)
but in the docs I read

https://winscp.net/eng/docs/library_session_enumerateremotefiles

The method cannot be used via COM interface (i.e. from VBScript, JScript or VBA).

However, the outcome:
How would I check for the existence of a remote file of which I only know part of the name (e.g.

SomeFile_<YYYYMMDD_hhmmss>.CSV

where the timestamp cannot be predetermined).

I found that

FileExists()

cannot be used with wildcards and the common answer is to use

EnumerateRemoteFiles()

instead, but I'm always getting an Automation Error (-2146233079)

e.g.

' passing in "SomeFile_*.CSV" for fileName and a working session 
Private Function resultExists(fileName As String, ByRef activeSession As Session) As Boolean
  Dim files As New RemoteFileInfoCollection
  Dim f As RemoteFileInfo
 
  ' error occures when the following statement is executed  
  Set files = activeSession.EnumerateRemoteFiles("/somePath/", fileName, EnumerationOptions_None)
  
  resultExists = (files.count > 0)
 
  For Each f In files
    Debug.Print f.Name
  Next f
End Function

The problem doesn't seem to be the call itself but the assignment.
When I merely do

 call activeSession.EnumerateRemoteFiles("/somePath/", fileName, EnumerationOptions_None)

the call works, but I'm unable to "catch" the enumerator the call is supposed to return.

I'm on Win10 64bit Enterprise, MS Office 2016 64bit, WinSCPnet.dll (FileVersion 1.7.2.10240, ProductVersion 5.17.1.0)

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 40,587
Location:: Prague, Czechia

Re: Can I use Session.EnumerateRemoteFiles() in VBA?

2020-03-14

No you cannot. That other thread is probably some mistake. I'll edit it accordingly.

Reply with quote

ScruffR
Joined:: 2020-03-05
Posts:: 5

Re: Can I use Session.EnumerateRemoteFiles() in VBA?

2020-03-14 17:33

Thanks for the clarification, but how would one then go about checking for a specific but not fully qualified file without having to always needing to read the entire list of files stored in the directory?

And, is there any chance to get EnumerateRemoteFiles() working in VBA and/or FileExists() support wildcards ever?

Reply with quote

martin◆ Site Admin
Joined:: 2002-12-10
Posts:: 40,587
Location:: Prague, Czechia

Re: Can I use Session.EnumerateRemoteFiles() in VBA?

2020-03-17

ScruffR wrote:

Thanks for the clarification, but how would one then go about checking for a specific but not fully qualified file without having to always needing to read the entire list of files stored in the directory?

There's no other way anyway. EnumerateRemoteFiles internally calls ListDirectory.
With most servers you need to retrieve full listing in any case (even if you use any other library).

Reply with quote

ScruffR
Joined:: 2020-03-05
Posts:: 5

Re: Can I use Session.EnumerateRemoteFiles() in VBA?

2020-03-18 23:51

I found a "workaround" that perfectly fits my needs and may also help others in a similar situation.
Since I need to archive the respective file(s) I'm looking for anyway I now do this

  Dim result As WinSCPnet.TransferOperationResult
  Set result = activeSession.GetFilesToDirectory(sftpPath, archivePath, fileNameWithWildcards)  
  ' or when you want to remove the source file after archiving
  'Set result = activeSession.GetFilesToDirectory(sftpPath, archivePath, fileNameWithWildcards, True)  
  ' ... error checks here
  If (result.IsSuccess()) Then
    Dim tea As WinSCPnet.TransferEventArgs
    For Each tea in resultTransfers
      Debug.Print tea.FileName
    Next tea
  End If

(BTW, I'm more a C++/C# guy anyway, but sometimes you need to please "customers" that insist in using VBA 😁)

Reply with quote

Can I use Session.EnumerateRemoteFiles() in VBA?