Copying files from FTP location via VBA

Topic

New Reply

I am seeking a method of copying files from and ftp location using VBA. I have a batch file that does what I need, but prefer a VBA solution. I have tried using VBA to call a batch file that calls a script, but the window opens and then closes, without having copied the files.

Any help and ideas are greatly appreciated.

Batch file

Code:

ECHO OFF

REM ************************************************************************************************
REM SCRIPT NAME    ::: scriptOIB.txt is called by this bat file--> OIB.bat
REM MODULE         ::: This script is used to download the file from FTP Site to the  Source Folder.
REM AUTHOR         ::: Ken Van Huss
REM DESCRIPTION    ::: This script is used to download the source files from FTP Site for 
REM                   ::: RM staff
REM DATE WRITTEN   ::: 7-Sept-2011      
REM VERSION        ::: 1.0     7-Sept-2011 --> Initial Version.                                                      
REM *************************************************************************************************
ftp -s:ScrptCpy.txt

text file called from above is “ScrptCpy”

Code:

open 202.301.30.192
myfilesKen001
blahblahblah
cd inboundtest
prompt off
ascii
lcd c:_Files
mget *.*
bye

Reply | Quote

Replies

VBA can automate the ActiveX controls that form the heart of Internet Explorer, so there’s a reasonable chance you can do it from VBA. However, documentation has become more sparse over time as developers have migrated to VB.Net, so it could be tricky to find code that works with current versions of Windows/IE.

These older Lounge threads have links to some code samples and documentation for the Wininet library:

Download Online Documents via HTTP
FTP – wininet.dll, How to? (WinXP SP2, XL 2003 UK)

In addition to the free FTP functionality of Windows, there are third party ActiveX/COM libraries for FTP and other network connectivity. I haven’t used any of them myself.

Reply | Quote

Thanks for the information. I will research the third party libraries and see what works.
Thanks!

Reply | Quote

I have this example: FTP Client functions

These links should be helpful: Access Developer Tools: Apps, Add-ins, VBA Code
Check out the link for Chilkat Software

Boyd Trimmell aka HiTechCoach
Microsoft MVP – Access Expert

Reply | Quote

Boyd,
THANKS ! ! What an excellent tool! I will give it a shot. It looks really well done.

Ken

Reply | Quote

Boyd,
THANKS ! ! What an excellent tool! I will give it a shot. It looks really well done.

Ken

Ken,

You’re welcome.

Hope it helps.

Reply | Quote

Here’s a VB basic module (see attached file) that I created some years ago for FTP Puts and Gets using API calls. It worked using some older version of IE components (I don’t remember the version) under XP. You’re welcome to try it out. If you can use a bas module in your project then just rename the file extension to .bas

You’ll want to focus on the Public subs and the incoming arguments.

Reply | Quote

Is there a way to change the DOWNLOAD location for the FTP file? My database (the one Bender offers) is located in “C:juricta”. I want to save the downloaded FTP File to c:jurictaOLCC

Here’s a VB basic module (see attached file) that I created some years ago for FTP Puts and Gets using API calls. It worked using some older version of IE components (I don’t remember the version) under XP. You’re welcome to try it out. If you can use a bas module in your project then just rename the file extension to .bas

You’ll want to focus on the Public subs and the incoming arguments.

Reply | Quote

Bender,

thanks. will try to get it to run in VB.NET, which I am brand new to.

Ken

Reply | Quote