FTP Using Access VBA?

  • Thread starter Thread starter CliffKing
  • Start date Start date
C

CliffKing

Hi All,

Is it possible to FTP through VBA?
Right now I manually run a batch file that calls another batch file
that just opens an FTP
server and downloads a recordeset to my local drive as a text file.
The downloaded text files are
imported into an Access Database (See example of batch files below)

Batch1.bat

open 10.243.25.034
P602176
clifking
ascii
get 'D855.LBDTR.SKF612.LBDS121E.K0001U00' C:\WKData_Gl\GenL01.txt
get 'D855.LBDTR.SKF612.LBDS121E.K0002U00' C:\WKData_Gl\GenL02.txt
get 'D855.LBDTR.SKF612.LBDS121E.K0003U00' C:\WKData_Gl\GenL03.txt
get 'D855.LBDTR.SKF612.LBDS121E.K0004U00' C:\WKData_Gl\GenL04.txt
get 'D855.LBDTR.SKF612.LBDS121E.K0005U00' C:\WKData_Gl\GenL05.txt
get 'D855.LBDTR.SKF612.LBDS121E.K0006U00' C:\WKData_Gl\GenL06.txt
quit


Batch2.bat

REM FTP CALL BATCH: Batch1.bat
ftp -s:Batch1.bat
exit


I would love to automate this process Is there a way i can achieve
this through Access VBA?


Thanks!
 
Batch1 it not a batch file. It is a FTP script file which someone has given
the name Batch1.bat.

If you don't want to use a standard FTP script file, you need to not use
a standard FTP program. For example, you could use the Internet
Transfer Library: http://www.mvps.org/access/modules/mdl0037.htm,
or you can use the Windows API: http://www.tek-tips.com/faqs.cfm?fid=5904

The second file is a single line Windows console command. You can run
that command from VBA as well as from a batch file:

taskID= vba.shell ("c:\where_ever\ftp.exe -s:Batch1.bat",
vba.vbMinimizedNoFocus )


Then you could just loop until the task is closed
I think that taskID is now another broken feature, so you will have
to use the ShellWait api: http://www.mvps.org/access/api/api0004.htm
or loop until "dir" tells you that all the files exist:

Do While Dir(sFname) = ""
DoEvents
Loop

(david)
 
CliffKing said:
Hi All,

Is it possible to FTP through VBA?
Right now I manually run a batch file that calls another batch file
that just opens an FTP
server and downloads a recordeset to my local drive as a text file.
The downloaded text files are
imported into an Access Database (See example of batch files below)
Click to expand...
<SNIP>

I have a module which will provide the functionality you need. However,
posting it here would cause a _lot_ of word-wrapping which would have to be
meticulously fixed. I can email a zip attachment if you like. Is your email
address correct?
 
Stuart McCall said:
I have a module which will provide the functionality you need. However,
posting it here would cause a _lot_ of word-wrapping which would have to be
meticulously fixed. I can email a zip attachment if you like. Is your email
address correct?
Click to expand...

Stuart

If desired you can have it added to the Access Web. With your name on
it of course. If so contact Arvin Meyer.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Tony Toews said:
Stuart

If desired you can have it added to the Access Web. With your name on
it of course. If so contact Arvin Meyer.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Click to expand...

That sounds like a plan. I'll tidy it up a bit and make it fit for human
consumption first.

So Arvin is fielding submissions these days? Has Dev retired? (I've been out
of touch for a few years due to illness)
 
Batch1 it not a batch file. It is a FTP script file which someone has
given
the name Batch1.bat.

If you don't want to use a standard FTP script file, you need to not use
a standard FTP program. For example, you could use the Internet
Transfer Library: http://www.mvps.org/access/modules/mdl0037.htm,
Click to expand...


The above transfer library is quite slick, and is a api to the wininet.dll.

If you looking for native ftp inside of ms-access, then that above library
is a good start.
(it has source code...and you can pull a few modules inside to your
applications). Note that it supports both ftp, and http transfer.

I actually wound up writing my own code and this was much due to me wanting
a nice progress bar with callbacks to update the progress bar during the
download. The above example for the FTP transfer library on the MVP site
actually does have provisions callback and a progress bar. Unfortunately
when I first looked at the code I missed this ability. Therefore spent some
time of my own to writing my own library.
 
Hi Cliff,

It's a little late for me to be jumping in but, below is some code that I use.
The first function creates a script file (give it a folder and a file name) and
the second one executes the actual FTP (pass it the script file name created in
the first function).

Hope this helps,
RD


Function fCreateScript(sFolder As String, sFileName As String) As Boolean
Dim lFileNumber As Long
On Error GoTo ErrHandler

' Get unused file number
lFileNumber = FreeFile
' Create file name
Open "C:\SomeFolder\FTPscript.scr" For Output As #lFileNumber
' Output text
Print #lFileNumber, "lcd " & """C:\SomeFolder\SomeSubFolder"""
' ftp server
Print #lFileNumber, "open 10.21.16.112"
' user
Print #lFileNumber, "anonymous"
' generic password
Print #lFileNumber, "(e-mail address removed)"
' change to the proper directory
Print #lFileNumber, "cd " & sFolder
' copy file over
Print #lFileNumber, "get " & sFileName & " " & sFileName
' quit ftp session
Print #lFileNumber, "bye"
' Close file
Close #lFileNumber

fCreateScript = True

ExitPoint:
On Error Resume Next
Exit Function

ErrHandler:
Debug.Print Err.Number & ": " & Err.Description
Stop
fCreateScript = False
Resume ExitPoint

End Function


Function fFTP(stSCRFile As String) As Boolean
Dim stSysDir As String
On Error GoTo ErrHandler

stSysDir = Environ$("COMSPEC")
stSysDir = Left$(stSysDir, Len(stSysDir) - Len(Dir(stSysDir)))
Call Shell(stSysDir & "ftp.exe -s:" & stSCRFile, vbNormalFocus)
DoEvents
fFTP = True

ExitPoint:
On Error Resume Next
Exit Function

ErrHandler:
Debug.Print Err.Number & ": " & Err.Description
fFTP = False
Stop
Resume ExitPoint

End Function
 
Back
Top