How to Change the Port Address in wininet.dll's InternetOpenA?

[Quark]

Good afternoon, all -

I'm trying to write some code in an Excel macro to allow me to rename files on an SFTP site before downloading them.

I've managed to get a simple block written that works well against a test server, but it fails when I try to access the actual server I need.

I suspect that it's because this server uses Port 22, not the standard Port 21. But, I can't see how to change that Port address to what I need.

In InternetOpenA, I've tried setting dwAccessType to INTERNET_OPEN_TYPE_PROXY (3) and lpszProxy to ftp=ftp://Proxy1:22 (with my specifics replacing the placeholders). But, no good. InternetConnectA refuses to connect.

So, since this code worked with using the defaults, I figure I'm not modifying the Port properly.

Oh, an afterthought - would that this is an SFTP, not FTP, be an issue?

Thanx in advance for any assistance!

[techgnome]

Wel, since port 22 IS the default port for SFTP.... yes, it makes a difference. It also means you should be using SFTP and not FTP.... and no port.
ftp=sftp://Proxy1

-tg

[Quark]

Ah! I did not know that about SFTP having a different port. I'll check it out.

Thanx!

[Quark]

Wel, since port 22 IS the default port for SFTP.... yes, it makes a difference. It also means you should be using SFTP and not FTP.... and no port.
ftp=sftp://Proxy1

-tg

Well, progress - of sorts.

The error code changed from 12002 to 12029; Time out to connection could not be established.

But, if I enter the url in the address line of an explorer window, it gives me a dialog to enter a user name and password.

I'm opening the connection with (InternetOpen is defined as an alias earlier in the code);

lngInet = InternetOpen("MyFTP Control", 0, "ftp=sftp://sshftp.server.com", vbNullString, 0)

And, I'm connecting with (and, InternetConnect is also defined earlier as an alias);

lngInetConn = InternetConnect(lngInet, "sshftp.server.com", 5, sUser, sPwd, 1, 0, 0)

This code returns True, verifying the connection has been opened OK;

InternetCheckConnection(lngInet, 0, 0)


But, lngInetConn insists upon returning a 0.

I am at a loss, but will persevere.

Thanx for your assistance, tg!

[Quark]

Well, further searching today leads me to believe that wininet.dll does not support SFTP access. All the results I get say to get third party apps, but that is not an option for me; others will need to use the code who do not have the rights to add software.

This strikes me as something that should be available - and, if the third party apps are doing it, then there must be a way.

[wqweto]

I'm using putty/plink for sftp and curl for ftps -- totally different "file transfer" protocols, both unsupported by wininet or any other built-in windows component.

cheers,
</wqw>

[jpbro]

This strikes me as something that should be available - and, if the third party apps are doing it, then there must be a way.

You can code your own client against the various SFTP specs, but that would be a nightmare. If you can't install other software, are you able to use third-party DLLs packaged with your software? If so, there are some commercial libraries out there that you might be interested in. Try a Google searches for ChilkatSFTP and wodSFTP. I've used ChilkatSFTP and I can recommend the entire Chilkat bundle (not exactly cheap but excellent value for the money - there's lots of good stuff in it).

[Quark]

You can code your own client against the various SFTP specs, but that would be a nightmare. If you can't install other software, are you able to use third-party DLLs packaged with your software? If so, there are some commercial libraries out there that you might be interested in. Try a Google searches for ChilkatSFTP and wodSFTP. I've used ChilkatSFTP and I can recommend the entire Chilkat bundle (not exactly cheap but excellent value for the money - there's lots of good stuff in it).

I've seen the Chilkat stuff pop up, but my employer is, by no means, going to pop for the expense. I mean, what I'm trying to do is just to automate an existing manual process - that, if a certain client saved their files properly, a process that wouldn't be necessary - to remove potential for human error on my part or my backup's. It's like pulling teeth just to get software that we need, let alone something for convenience.

I looked at coding something of my own, but, from what I've seen, that's a bit above my paygrade.

Thanx for your reply, jpbro!

[jpbro]

Do you have Windows 10 with the April 2018 update (or newer) installed? It comes with an SFTP command line tool (or if you are just copying files to a remote server, I think you could use SCP instead). Have a look in this folder to see what's available:

C:\Windows\System32\OpenSSH

You can dynamically build your commands then shell them.

[wqweto]

Indeed, scp.exe for sftp and curl.exe for ftps are already built-in commands for Windows 10 so these can be used to "file transfer" at will.

cheers,
</wqw>

[Quark]

Do you have Windows 10 with the April 2018 update (or newer) installed? It comes with an SFTP command line tool (or if you are just copying files to a remote server, I think you could use SCP instead). Have a look in this folder to see what's available:

C:\Windows\System32\OpenSSH

You can dynamically build your commands then shell them.

I'll check that out. I think we all have Win10, so something native to that OS will work well.

What we've got is a set of daily files that we download on a weekly basis. But, the client does not use unique names for the files as they're placed on the server, so each succeeding file has a copy number appended to it. But, the files are moved to an archive folder, so there are other files in there that would have the same names.

What we do now is, rename the files by deleting the copy number and appending the creation date. We then download the files and place the originals into an archive folder on the server. Then, we go on our merry way processing the downloaded data.

I figured, since we're using an Excel macro to process the downloaded data, I could just add to that macro to do the rename, download and move parts of the task.

I'll give that OpenSSH a look.

Thanx, jpbro!

[Quark]

Do you have Windows 10 with the April 2018 update (or newer) installed? It comes with an SFTP command line tool (or if you are just copying files to a remote server, I think you could use SCP instead). Have a look in this folder to see what's available:

C:\Windows\System32\OpenSSH

You can dynamically build your commands then shell them.

Well, I've looked over what I can find on OpenSSH, including the manuals available on OpenSSH.com.

The CLI commands seem straightforward enough, except that, though I can find a command to connect to my target SFTP site, I saw no command for entering user info - username and password. Well, none that I could recognize, anyway.

I then spent most of yesterday afternoon searching in vain for some sort of tutorial on how to use sftp.exe, but didn't find anything that seemed useable. Lot of third party solutions, but that's not an option for me.

Seems this is something I'm going to have to table and just keep plugging on with the manual method.

[jpbro]

Is the SFTP server running Linux,and if so do you have access to it via SSH? You could create a key pair on your computer, then add the public key to the servers /home/<username>/.ssh/authorized_keys file. You can then use the -i parameter of scp with your private key and connect without requiring a password.

TBH though, I would bring this all up with your boss and present the business case for buying a component for a one-time cost of $200 USD (wodSFTP) to $300 USD (Chilkat full bundle with SFTP and more components that may come in handy down the road).

For example, you've already spent X hours at your hourly wage working at this, and the manual method takes and extra Y hours more per month than necessary. You can then calculate break even on the commercial SFTP component + some additional development time. After that everything is saving money. If it's a short-ish period of time to break-even, then maybe your boss with go for it.

[Quark]

Indeed, scp.exe for sftp and curl.exe for ftps are already built-in commands for Windows 10 so these can be used to "file transfer" at will.

cheers,
</wqw>

Thanx for the reply, wqweto!

[jdc2000]

Did you see these links:

https://answers.microsoft.com/en-us/...4-2f56e527aff5

https://smallbusiness.chron.com/test...mpt-43993.html

https://www.itworld.com/article/2928...t-windows.html

[Quark]

Is the SFTP server running Linux,and if so do you have access to it via SSH? You could create a key pair on your computer, then add the public key to the servers /home/<username>/.ssh/authorized_keys file. You can then use the -i parameter of scp with your private key and connect without requiring a password.

I've discovered that our new laptops may have WinSCP on them by default, so that opens a new path for me to explore.

I don't know if the server is on Linux or not, though I'm sure I can find out.

Right now, we're using FileZilla to access the data

TBH though, I would bring this all up with your boss and present the business case for buying a component for a one-time cost of $200 USD (wodSFTP) to $300 USD (Chilkat full bundle with SFTP and more components that may come in handy down the road).

For example, you've already spent X hours at your hourly wage working at this, and the manual method takes and extra Y hours more per month than necessary. You can then calculate break even on the commercial SFTP component + some additional development time. After that everything is saving money. If it's a short-ish period of time to break-even, then maybe your boss with go for it.

From some of the search results I've seen, having WinSCP might be satisfactory. Now, I need to educate myself on how to use it.

There's not a big business call for the automation I'm trying. It's just something I'm trying to improve upon to make it easier for someone who may have to do my tasks if I'm out on vacation or something. And, to make it faster and easier on myself, too.

Granted, all this effort to find a solution is not exactly "faster and easier," but I'm learning in the process and it fills my day.

And, actually, the self-learning may be all I get out of it, anyway. There's talk that our ETL function will be taken over by another department and all we'll be doing is running queries and reports.

Yay. /sarc

You've been a great help, jpbro, more than I've gotten on three other forums for this question.

[Quark]

Did you see these links:

https://answers.microsoft.com/en-us/...4-2f56e527aff5

https://smallbusiness.chron.com/test...mpt-43993.html

https://www.itworld.com/article/2928...t-windows.html

I had not.

Just opened them up to check them out. Thanx!