Downloading files fromftp server

[haplc]

Dear All,

I have to download all files from a ftp server and paste them in a folder in computer

my ftp server is: ftp://user:pw@10.128.158.152 (user is user id and pw is password)
my destination folder is c:\temp

Can anyone tell me how to write a macro for this copy (cut) paste function in excel?

Many thanks

 

[RobertSF]

I don't know if this will work, but nobody else was posting an answer, and this worked for me to download files. I was using http:// links, but I expect it should work with ftp:// links as well. However, the FTP return code for a successful transfer may not be 200. It may be something else.

Dim webReq As MSXML2.XMLHTTP60Set webReq = New MSXML2.XMLHTTP60


Dim oStream As ADODB.Stream
Set oStream = New ADODB.Stream
oStream.Type = 1


webReq.Open "GET", "http/ftp link", False
webReq.send
If webReq.Status = 200 Then
    oStream.Open
    oStream.Write webReq.responseBody
    oStream.SaveToFile ("path and file name")
    oStream.Close
End If

You'll need to set references to:

Microsoft XML, v6.0 for MSXML2.XMLHTTP60
Microsoft ActiveX Data Objects 6.0 Library for ADODB

These are the latest libraries. Older ones work too if you don't have the latest.

I hope this helps!

 

[haplc]

Many thanks Robert SF. However, on running the macro, I am gettign this message: unknown command "ADODB.Stream"
I have enabled all the libraries you have mentioned

THanks for further help

 

[RobertSF]

There are two lines with ADODB.Stream

[COLOR=#333333]Dim oStream As ADODB.Stream
[/COLOR][COLOR=#333333]Set oStream = New ADODB.Stream[/COLOR]

Do you know on which line the error occurs? The editor will highlight the line in yellow if execution has started. If the error occurs before execution has started (something wrong with the Dim statements), the line with the error won't be highlighted but it will be selected.

If I deselect the library, I get an error before execution starts, but the error is "User Type not defined." Since the library is not selected, Excel knows nothing about ADODB.Stream and figures it must be a type that I defined. When it doesn't find the definition, it throws up that error, so I don't understand why you get unknown command.

You know how Excel has "intellisense?" If you type ADODB., as soon as you hit the period you should get a list of the various methods and attributes contained in ADODB. Does this happen? If it does, do you see the Stream if you scroll down?

I googled around and didn't find anything, so I'm pretty stumped. I've only used ADODB.Stream this one time, and I didn't run into this problem.

 

[haplc]

Re: Downloading files from ftp server

THe message is: compile error: user-defiend type not defined

It is occuring at line: Dim oStream As ADODB.Stream

I have following libraries selected from the reference: Microsoft XML, 6.0 and MIcrossoft active x data objects(multi dimensional ) library 6

Here is the code:

Sub m1()
Dim webReq As MSXML2.XMLHTTP60
webReq = New MSXML2.XMLHTTP60


Dim oStream As ADODB.Stream
Set oStream = New ADODB.Stream
oStream.Type = 1


webReq.Open "GET", " ftp://userw@10.128.158.152", False
webReq.send
If webReq.Status = 200 Then
oStream.Open
oStream.Write webReq.responseBody
oStream.SaveToFile ("c:\temp")
oStream.Close
End If
End Sub

 

[RobertSF]

Re: Downloading files from ftp server

Oh, ok, so maybe it is a missing library.

It's the blind leading the blind here, you know.

Here are the libraries I have selected in that code that does work.

I'm sure you don't have to have the Outlook library selected. That was for something else.

 

[haplc]

Re: Downloading files from ftp server

LOL--now it is an intelligent man trying to teach a non Excel man

I have selected /checked the libraries. HTey are same as yours. I am now getting this error: Object not set at this line:

webReq = New MSXML2.XMLHTTP60

Can you please try to run my code in your computer? Here is the code

Dim webReq As MSXML2.XMLHTTP60
webReq = New MSXML2.XMLHTTP60


Dim oStream As ADODB.Stream
Set oStream = New ADODB.Stream
oStream.Type = 1



webReq.Open "GET", " ftp://userw@10.128.158.152", False
webReq.send
If webReq.Status = 200 Then
oStream.Open
oStream.Write webReq.responseBody
oStream.SaveToFile ("c:\temp")
oStream.Close
End If

 

[RobertSF]

Re: Downloading files from ftp server

Thank you, but perhaps not so intelligent, as a typo in my code led you astray a bit.

In my original code, I had included the line

Dim webReq As MSXML2.XMLHTTP60Set webReq = New MSXML2.XMLHTTP60

which should have been two lines, like this

Dim webReq As MSXML2.XMLHTTP60
Set webReq = New MSXML2.XMLHTTP60

You probably noticed that and changed it to

[COLOR=#333333]Dim webReq As MSXML2.XMLHTTP60[/COLOR]
[COLOR=#333333]webReq = New MSXML2.XMLHTTP60[/COLOR]

That's what's causing the "Object not set" error.

Here's what's going on. Ordinary variables can be declared and values assigned with an equals sign, so

Dim x As Integer
x = 3

works perfectly fine.

But objects must be assigned with the Set statement.

Dim o as Object
Set o = SomeActualObject

This is an error that bites all of us. Fortunately, once bitten, it's an error that's easy to find once you know about it.

I was concerned whether XMLHTTP would work with FTP, but I found this thread in which it appears you can. As the code in that thread shows, you may have to set a request header.

https://www.experts-exchange.com/questions/20867066/Using-MSXML2-XMLHTTP-for-file-download.html