using vba to download file via ftp

  • Thread starter Thread starter daryl
  • Start date Start date
D

daryl

Need help on how to use excel vba to download file via ftp(or othe
means) from another system(specifically, openVMS alpha system).

Would this also be possible to input command to this system? usin
excel as the medium.

Need to have an interface that could be user friendly and would no
worry about the syntax being used on the openVMA system.

thank you very muc
 
Hello daryl,

It can be done with API and other methods...
API: Please have a look at the link below.
http://xcelfiles.homestead.com/Excel02.html#anchor_35

And it can be done with using command prompt as a bat file.
(make a bat file for ftp then kick it from Excel.- it works on
Windows2000 and the later version)
Would this also be possible to input command to this system? using
Click to expand...
excel as the medium.
I don't have any knowledge about openVMS alpha system so I'm not sure
if it can be done.
 
Daryl,

this works and is very simple if your source machine can run a web server
and the file you want resides somewhere within the web root. By suggesting
use of FTP I am guessing you can also use http. You'll need to know the ip
address of the source machine and change the strFullURL line.

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal strURL As String, _
ByVal strFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Public Sub GetInternetFile(strFileName As String)

Dim lReturn As Long
Dim strFullURL As String
Dim strLocation as string

strFullURL = "http:\\nnn.nnn.nnn.nnn\subfolder\" & strFileName
strLocation = environ("Temp") & "\" & strFileName

lReturn = URLDownloadToFile(0, strFullURL, strLocation, 0, 0)

If lReturn <> 0 Then call msgbox("failed")

End Sub

Robin Hammond
www.enhanceddatasystems.com
 
Actually you don't need an api call.
You can eith shell a command to run a batch file or, amd
more easily, drom an inet control onto a userform.

The following code can be used to send a file...it first
calls a procedure to open a channel, then sends the file

Private Sub SendFile()

'...prepare
OpenChannelToFTP ' see below

With INetControl ' name of the inet control on form

'...execute
mbSent = False
.Execute , "PUT " & msSourceFile & " " _
& TargetFile
mbSent = True

'...wait
Do
DoEvents
Loop While .StillExecuting

'...close
.Execute , "Close"
End With

End Sub


The following procedure is called to open an FTP channel
and sets a target folder....

Private Sub OpenChannelToFTP()

With INetControl
.Protocol = ProtocolConstants.icFTP
.URL = URL
.UserName = Login
.Password = Password

.Execute , "CD " & TargetPath

'...wait
Do
DoEvents
Loop While .StillExecuting

End With

End Sub


I use named variables that are pretty much self-
describing.
Note: the variable 'mSent' is boolean and set in the
forms General area - is its scope is the code module.
This is set by by the sending procedure - I use it later
to test if a send worked. I also have the "statechanged"
event of the inet control coded to test the ftp responses.

So, you can see that this is an extremely easy-to-use
interface.

Patrick Molloy
Microsoft Excel MVP
 
Hi Patrick,

I am quite new at this and was wondering whether you could assist.

I am trying to get a file via an ftp conneciton rather than send it.

can oyu please help with the code for that.

thank you kindly
ron
 
Back
Top