|
|
Good morning, 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 using wininet.dll.
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. InternetOpenA works fine and returns a valid handle, but I just can't get a connection.
So, since this code worked on the test server using the defaults, I figure I'm not modifying the Port properly for use on my specific server.
All that being said, my question, I think, is; how do I change the port address in the InternetOpenA function?
Oh, an afterthought - would that this is an SFTP, not FTP, be an issue?
Thanx in advance for any assistance!
|
|
|
|
|
|
That's what I did. I tried populating nServerPort with my port address, but no good. Then, further searching informed me that nServerPort is not an actual address, but a flag with values 0-5. I tried using 5 and then set up the address in dwService with port 22 specified, but InternetConnectA still returned 0.
|
|
|
|
|
|
Unfortunately, my programming fu is not as up to the task as I would like. I didn't do any error checking as, well, it wasn't returning an error, as best I could tell. It just wasn't making a connection. As far as the code was concerned, it was doing what it was supposed to do.
Of course, this is all assumption on my part. I'll look at the link you provided, see if I can wrangle out an error code and see what it may be.
But, as I said, the code worked fine with an FTP test site - test.rebex.net - using the default port, 21. The server I'm trying to get to works fine with FileZilla, so the site works, but I think the problem is just the port address; especially as I can't find any way to change it.
I'm wondering if, because it's an SFTP server, if that may be causing an issue, too.
Well, I'll see if I can get an error code and go from there.
Thanx!
|
|
|
|
|
CKinTX wrote: it wasn't returning an error
That is why you should use GetLastError: to find out why it failed.
CKinTX wrote: I think the problem is just the port address
That should be easy to test by using the ftp prompt from a command window.
|
|
|
|
|
Based on results when I searched on how to utilize GetLastError, I found that it was not recommended because basic makes a lot of other API calls that will displace the error. So, the suggestion was to use Err.LastDllError.
I did that and got an error of 12002, Time out. So, that seems to verify my presumption that it's the port address setting.
Port 21 is failing, because the server isn't listening to that port - for security reasons - and I'm not successfully changing the port address to 22, the one it is listening to.
So, I guess my bottom line question is, how do I change the port address using InternetConnectionA in wininet.dll? The methods that I tried all failed, so I'm doing something wrong.
|
|
|
|
|
Looking at the documentation it appears that you can't, which is pretty damn stupid on Microsoft's part. So I am afraid it looks like you are going to have to find some other way around this, probably by switching to a language like C,C++ or C# which will allow you greater control. However, take a look at SFTP Client Libraries - SFTP.net[^].
|
|
|
|
|
The observation on Microsoft is spot on.
I've come across several snippets of code while doing my searches that specify a port address in nServerPort and, they claim, it works for them. Of course, that presumes that everything you see on the internets is accurate and without error.
I mean, it is, right?
But, there must be a way. For example, FileZilla does it, so there is some way to change the ports. Given that the wininet library is for C and VBasic, I figure, if C can do it, VBasic should, too. 'Course, that idea is rapidly sinking into the murk.
I'll look at your link, but C is not an option for me, as far as I know. This code is for an Excel macro as part of a data processing routine and I don't know that Excel macros can use C.
Thanx for your help, though. It's far better than I've gotten from the SQL Server community or MrExcel.
|
|
|
|
|
I expect that FileZilla, and similar apps, do not use the wininet library, but use basic Windows sockets. But I don't think you can do that from VBA. Sorry, I cannot help more.
|
|
|
|
|
*sigh*
The more computers make things convenient, the more they make them inconvenient...
Thanx, though, for your help!
|
|
|
|
|
SFTP upload with VBA - Stack Overflow[^] presents a solution using putty's stfp client. There does not seem to be an excel-integrated solution as of today.
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
|
|
|
|
|
Thanx, but that's not a solution.
The problem is that we receive files from a client covering a weeklong window of daily files. Unfortunately, whatever automation routine they use saves each daily file with the same name, so each successive file has '_X' where 'X' is an incremental values for each new file past the first appended to the name. That means, I have to manually rename the files, substituting the file's creation date in place of the '_X', download them and then move the originals to an archive folder on the SFTP site.
At the moment, I'm doing it manually with FileZilla, but I'm wanting to automate my end to cut down on manual manipulation. I want to expand on a current Excel macro that is already part of the process to take on this task.
For me, the putty solution might work - looking at the link, I don't see where I define the port address, unless it's just by appending ':22' to pHost. But, I have to make this code accessible to everyone, in case I am out and my backup has to do my task. I don't always know who that would be and they may not have the rights to add putty to their system.
But, thanx again for the response.
|
|
|
|
|
I make A project for help Desk and i need to retrieve user data from active directory by username from text box
thanks
|
|
|
|
|
Check for System.DirectoryServices.ActiveDirectory Namespace[^] which contains all objects / structs / enums you need to handle Active Directory operations in .NET.
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
|
|
|
|
|
i want to know whether it is possible to program calculation of marks in a a quiz show where 6 teams
participate. once the score button is clicked the core of each team should be shown in descending order of marks. If so how the coding should be. I am a student cum teacher. Now studying VB. Got a project to do so. But there is no way I could find a solution after going through the different sites.
|
|
|
|
|
Yes, it's possible. It's not even particularly difficult.
But ... Please don't repost if your question does not appear immediately: all of these went to moderation and required a human being to review them for publication. In order to prevent you being kicked off as a spammer, both had to be accepted, and then I have to clean up the spares. Have a little patience, please!
I've deleted the spare ...
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
As long as it use logical rules and mathematics, it is possible and with about any programming language.
But for this, you need to learn programming, but we can't teach you in the scope of this forum.
Advice: solve the problem by hand, the procedure you followed is basically your algorithm.
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
how to search record in gridview in vb 6.0
|
|
|
|
|
|
You normally would never search the gridview itself. You search the data source the grid is bound to.
|
|
|
|
|
When exporting data from SQL Server to Excel, when a field size is large e.g. varchar() might cause the error below:
String or binary data would be truncated.
The statement has been terminated.
|
|
|
|
|
Is that supposed to be a question or a statement?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
