can I pull data from FTP server?

[demodren]

Hi all, would anyone be able to perhaps point me in the right direction.
I run current macros which pull specific data from a shared drive. I want to see if I can now pull the same data from ftp server(I have address,user id,pw) Is it possible to incorporate this in the code I currently have? Any other options?

Any help would be greatly appreciated

Thanks so much

Dim strConn As String
    Dim objConn As Object
    Dim rst As Object
    Set objConn = CreateObject("ADODB.Connection")
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=C:\data.xls;" & _   
              "Extended Properties=""Excel 8.0;HDR=YES"""
    objConn.Open strConn
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "SELECT * FROM [data$] WHERE [level_id] = '2';", objConn
 
    Sheets("Main").Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

 

[demodren]

any thoughts? any help would be greatly appreciated

 

[xenou]

FTP is a file transfer protocol. You could use it to transfer a file, then query the file after it has been transferred. But you can't query the file in a remote location with FTP.
HTH,
ξ

 

[demodren]

gotcha..so I tried to the below to transfer the files but got stuck. I want to connect to the ftp site and download the 3 files to a specific G drive.

1) I created (test.bat) file

Open [URL="ftp://ftp.myfiles.net/"]ftp.myfiles.net[/URL]
Userid pw
cd files
get file 1
get file 2
get file 3
bye

2) I set up a macro in excel with: RetVal = Shell("H:\test.BAT", 1)

However when I run the macro, it just runs everything in a sec and nothing happens..

Am I missing something? My G drive is a shared drive..I would prefer the files to be downloaded there but otherwise I assume its default to my C drive?

Any help would be appreciated

Thanks so much

 

[Ruddles]

Windows comes with a command-line version of an FTP client and with careful programming this can be driven by VBA. I'm told you can use ftp:// in IE but I've never tried it. If it's true, this could potentially be automated using VBA.

There are GUI-based FTP clients which look like Windows Explorer: they have a view of your machine on one side of the screen and the remote host(s) on the other side, and you can drag-and-drop files very easily. Google AceFTP, CuteFTP or SmartFTP - there must be loads more, they're just the ones that spring to mind. I don't know whether these can be automated.

If you use Access, there's a third party (i.e. non-Microsoft) library of FTP functions available which can be driven by VBA. It can be downloaded from http://www.smccall.demon.co.uk/Downloads.htm#FTPClient and the author responds to emails - or at least he did when I contacted him about a year ago.

I suppose you could write a routine in Access which uses ftp to transfer a file from a remote server, which auto-runs when the database loads and drops the file in a folder somewhere, then open it from your Excel workbook. Your workbook could sit and wait for a 'semaphore' file to appear or time out if it doesn't appear.

 

[John_w]

gotcha..so I tried to the below to transfer the files but got stuck. I want to connect to the ftp site and download the 3 files to a specific G drive.

Try http://www.mrexcel.com/forum/showthread.php?p=2422426#post2422426, using get or mget instead of mput in the FTP_commands.txt file to download files. Surround file paths/names with double quotes if they contain spaces, though it does no harm if they don't.

 

[xenou]

My go at this is here:
http://www.mrexcel.com/forum/showthread.php?p=2411780

I suggest you proceed at the command prompt first, without vba, to be sure it works - just typing in the ftp commands. Then script it. Use filenames without any spaces in them. You want to be sure ftp services are allowed on your network - another reason to test it without code first.

Note: IE does provide basic ftp capabilities. So does Windows Explorer. There's quite a few clients out there. (I've been using Filezilla for secure ftp for the last two years - to add to Ruddle's list). FYI FTP sends passwords in plain text so I don't think it can be considered a safe option these days.

Edit again: I just tested IE and Windows Explorer -- I haven't done that for a while - and probably won't for a while again . A URL like this usually works:

ftp://ftp.MyDomain.com

You should then see a prompt for the user id and password. But I'd get a better client that has SFTP (Secure File Transfer Protocol) capabilities - then use it whenever possible.

 

[Ruddles]

I suppose you could write a routine in Access which uses ftp to transfer a file from a remote server... and drops the file in a folder somewhere, then open it from your Excel workbook.

Done - not too difficult. The hardest thing was the error-handling.

 

[demodren]

Is it possible to download a file where the name always changes. The name of the files is always the previous business day.. So would be:

File_name_(MMDDYY-1)

 

[xenou]

Yes, like so:

Dim sFileName As String

sFileName = "File_Name_" & UCase(Format(Date - 1, "mmddyy")) & ".csv"

ξ