Using Microsoft Access to connect to an sftp site, list files in directory, download file from that.

Advertisement

Russ
Joined:
Posts:
4
Location:
North Carolina

Using Microsoft Access to connect to an sftp site, list files in directory, download file from that.

I am using Microsoft Access 2016 to connect to an sftp site. I need to list files in the sftp directory. Check the names in that list and then download a specific file from that list.
Any examples on how to use this in MS Access or through scripting?

I have spent two day trying to code this in MS Access. With no success at all.
I have written a command file to download a file from an sftp site.
It is mainly being able to LIST what files are in the SFTP folder I am unable to do.
Thank you
Russ

Reply with quote

Advertisement

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

Re: Using Microsoft Access to connect to an sftp site, list files in directory, download file from that.

Are you using simple WinSCP scripting or WinSCP .NET assembly?
Show us what you have.

Reply with quote

Russ
Joined:
Posts:
4
Location:
North Carolina

I am using Microsoft Access 2016. I believe using scripting will be the best solution. I am using a shell command to run WinSCP.exe.
That does work but I cannot save a list of files from the ls command. If I could save a list of files to a local text file I could read that file and search the file names and dates.
How to use the "ls" command to return a list directory list of files. From that list I will determine just which ONE to download.

OPEN SFTP://xx:xx@Company.site.com/ -hostkey="xxx"
ls *.zip
exit

Reply with quote

Russ
Joined:
Posts:
4
Location:
North Carolina

NET is not an option available.
Using the LOG file appears to be the best solution.
ONE question. Can I always expect the log file to have the same layout, especially when it comes to line that has the file names, as in the example below?
Thank you

> 2020-03-26 13:38:58.255 Script: ls *.zip
. 2020-03-26 13:38:58.255 Listing directory "/".
< 2020-03-26 13:38:58.591 Script: -rwxrwxr-- 0 1981354 Mar 1 7:08:00 2020 FILE.ANT245.MONULIC.DT030120.TM050812.ZIP
< 2020-03-26 13:38:58.591 Script: -rwxrwxr-- 0 222 Mar 1 10:23:00 2020 FILE.ANTB245.DT030120.TM082345.ZIP
> 2020-03-26 13:38:58.591 Script: exit
. 2020-03-26 13:38:58.591 Script: Exit code: 0

Reply with quote

Advertisement

martin
Site Admin
martin avatar

I was suggesting XML logging. Not a plain session logging. We give no guarantees to the format of session log. It can change in any version. It is not to be parsed.

Reply with quote

Russ

Example VBA code to read the LOG.XML file.

Do you have sample vba code that will read the xml log file?
using this ojbect : CreateObject("MSXML2.DOMDocument")
thank you

Reply with quote

Advertisement

You can post new topics in this forum