can I pull data from FTP server?

D

demodren

Board Regular
Joined
Aug 20, 2010
Messages
106
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


Code: 
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
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,
ξ
 
Upvote 0
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
Code: 
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
 
Upvote 0
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.
 
Upvote 0
Upvote 0
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:
Code: 
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.
 
Last edited:
Upvote 0
Ruddles said:
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.
Click to expand...
Done - not too difficult. The hardest thing was the error-handling.
 
Upvote 0
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)
 
Upvote 0
Yes, like so:

Code: 
Dim sFileName As String

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

ξ
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Error Query HDR=No Text File
Replies
5
Views
239
adygelber
A
pcc
  • Solved
Conversion of special characters
Replies
6
Views
847
JEC
JEC
A
  • Question Question
Import Table Data from Web using VBA to excel Worksheet and able to input Values which can modify the web data to be extract
Replies
0
Views
658
Ambrosia
A
C
  • Question Question
VBA Consolidating Multiple Sheets in a Pivot Table
Replies
1
Views
319
c_clark
C
L
  • Solved
Querying access db from excel - 32 bit to 64 bit conversion
Replies
2
Views
770
JEC
JEC

Forum statistics

Threads
1,224,578
Messages
6,179,654
Members
452,934
Latest member
mm1t1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top