Excel VBA Script to Access Files on an SFTP Site

[CKinTX]

Good morning, all -

I download files from an SFTP site, but the files don't always have a unique name. Files on the site have a naming convention of File_Name.xls for the first file in the group and File_Name_X for each subsequent file in the set where X is an incrementing value from 1 to the total number of files - 1. So, if there are five files, they'll be;

1. File_Name.xls
2. File_Name_1.xls
3. File_Name_2.xls
4. File_Name_3.xls
5. File_Name_4.xls

What I have to do is rename each of the files according to their creation date, appending the date to the end of each name, replacing the "_X" as necessary. For example, using the above list and presuming that the creation dates are from 1/1/2020 to 1/5/2020, the files would be renamed as follows;

1. File_Name.xls -> File_Name 2020-01-01.xls
2. File_Name_1.xls -> File_Name 2020-01-02.xls
3. File_Name_2.xls -> File_Name 2020-01-03.xls
4. File_Name_3.xls -> File_Name 2020-01-04.xls
5. File_Name_4.xls -> File_Name 2020-01-05.xls

Once renamed, the files are downloaded and the originals are then moved to an archive folder. Then, when I next download files, they are named in the same fashion again, and I have to rename them again.

My original plan was to write an Excel Macro to download the files, rename them and then upload copies of the newly named files back to the SFTP site, archiving them as I normally would. Unfortunately, during some manual testing of the renaming loop, I learned that the files, once downloaded, assume the current date as the creation date, giving them all the same name.

Blast, thought I.

So, my next plan was to rename the files while they still resided on the SFTP site, using the site parameters as a sort of drive alias. But, I've never been able to progress to this idea, because I can't get a connection to the SFTP site to save me.

I've found some WinSCP code that alleges to connect, but it has a USING statement - and other bits of code - that are VB.Net and, so, not compatible with Excel's macro basic. Well, at least, that's what I think the problem is, anyway. I couldn't get rid of the red squigglies. From what I could determine, it seems I have to have msinet.ocx on my computer. Unfortunately, that file is nowhere to be found and a Microsoft link to it gives me a 404 error.
Is there any way to set up a connection to an SFTP site such that I can do what I'm trying to do in renaming these files, or am I simply stuck doing it manually?

Thanx in advance for any assistance!

 

[CSmith]

What about files on the same date?

 

[CKinTX]

What about files on the same date?

If that happens, then the file provider has made an error in their automation routines and will have to check their process. The files are a daily census and should only run once a day, so only some error on their part would cause any duplication.

However, if it happens, my idea on renaming should, I think, flag such files by appending the standard windows flag to the name that it's a duplicate name. Then, when I see that, I can berate the site owners prodigiously for being so inane as to duplicate the file.

At least, I figure that's what would happen.

However, once I get the routine to do the renaming properly, then this can be something tackled after that, if necessary.

 

[CKinTX]

From wininet.dll, I use InternetOpen to initialize the connection. It successfully returns a handle. Then, I use InternetCheckConnection to test the connection, just to make sure. But, when I use InternetConnect to connect to the SFTP using the handle generated by InternetOpen, it returns 0 causing FtpGetFile to fail.

Any clues?

 

[CKinTX]

Deciding to go with just a barebones script and use a test server, rather than the one with my data, I used the following code to open a path to the internet;

lngInetConn = InternetConnect(lngInet, "ftp.microsoft.com", 0, "anonymous", "wally@wallyworld.com", 1, 0, 0)

This returned a handle for the connection in the form of a six-digit number. So, OK, fine, that works.

Then, I attempted to make a connection using that handle with the following code;

blnRC = FtpGetFile(lngInetConn, "dirmap.txt", "c:\dirmap.txt", 0, 0, 1, 0)

No good.

blnRC​

returns a '0' which indicates a fail.

Does anyone have any idea what I'm doing wrong?

 

[CKinTX]

Update:

Using InternetCheckConnectionA, the code returns TRUE which purports to indicate a successful connection.

Hope this is useful info.

 

[CKinTX]

Update:

I checked to see the status of ftp.microsoft.com and discovered that it has been down for a long time. Somewhat relieved that this might be my problem, I then searched for an accessible public FTP site that worked to test my script against. That search gave me test.rebex.net.

I modified my test info accordingly, ran my script and [insert great crowds of roaring, cheering peasants here] it worked!

So, I can get a connection to an FTP site and I can download a file.

But, this test FTP site uses port 21 and the server I need to access uses port 22.

So, on to test that and it fails.

Looks like my problem is the port address. So, can anyone here give me a hand on how to change the port address? The port value in the InternetConnectA call uses a flag, not an actual port, so that's not much help - that I can see, anyway.

Thanx in advance for any assistance!

 

[mydayisgood]

yo CKinTX, I just looked up your InternetConnect(lngInet, "ftp.microsoft.com", 0, "anonymous", "wally@wallyworld.com", 1, 0, 0) instruction and the 0, after the site name appears to be able to set the port number - I'm guessing that the 0 is default and sets up port 21.