Macro to do a FTP

sdk development
1

Hi all,

I am using the following macro to copy a file C:\a.txt to an ftp server .
I have added “Microsoft Internet controls” reference.
I am getting Runtime error 5:Invalid procedure call or argument.
Any idea how to add wininet.dll to references? ( i tried doing a browse and manually adding wininet.dll but it says “Cant add reference to the specified file” .
Any help is appreciated.
Or If the ftp can be done in a better way…That would help a lot.

Thanks in Advance,
Ashwin N

Private Declare Function InternetOpen _
Lib “wininet.dll” Alias “InternetOpenA” ( _
ByVal sAgent As String, _
ByVal nAccessType As Long, _
ByVal sProxyName As String, _
ByVal sProxyBypass As String, _
ByVal nFlags As Long) As Long

Private Declare Function InternetConnect _
Lib “wininet.dll” Alias “InternetConnectA” ( _
ByVal hInternetSession As Long, _
ByVal sServerName As String, _
ByVal nServerPort As Integer, _
ByVal sUserName As String, _
ByVal sPassword As String, _
ByVal nService As Long, _
ByVal dwFlags As Long, _
ByVal dwContext As Long) As Long

Private Declare Function FtpPutFile _
Lib “wininet.dll” Alias “FtpPutFileA” ( _
ByVal hFtpSession As Long, _
ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, _
ByVal dwContext As Long) As Boolean

Private Declare Function InternetCloseHandle _
Lib “wininet.dll” (ByVal hInet As Long) As Integer

Private Declare Function GetLastError Lib “coredll” () As Long

Private Const INTERNET_SERVICE_FTP = 1
Private Const INTERNET_SERVICE_GOPHER = 2
Private Const INTERNET_SERVICE_HTTP = 3

Public Sub FTPSend()

Dim hINetSession As Long, hSession As Long, xfrtype As Integer
      
hINetSession = InternetOpen("MyFTPClient", 0, vbNullString, vbNullString, 0)
hSession = InternetConnect(hINetSession, "(ftp server address)", _
   "21", "(username)", "(password)", INTERNET_SERVICE_FTP, 0, 0)
    
With Err

If .Number = 20 And .Description = “Cannot find the variable by its name.” Then
blnVariablePresent = False
Else
.Raise .Number, .Source, .Description, .HelpFile, .HelpContext
End If
End With
Call InternetCloseHandle(hSession)
Call InternetCloseHandle(hINetSession)
End Sub

ashwin_n84 :india: (BOB member since 2006-04-25)

2

I’ve used a similar technique in the past, and there was no need to add ANY references (Microsoft Internet Controls or wininet.dll) directly. The “Private Declare Function” lines take care of everything, since wininet.dll is already registered with the operating system. What line of code is throwing that error?

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

Hi,
Thanks for the reply.
The very first line itself is throwing the error.
hINetSession = InternetOpen(“MyFTPClient”, 0, vbNullString, vbNullString, 0)
So i believe its not able to get hold of InternetOpen function itself.
Regards,
Ashwin N

ashwin_n84 :india: (BOB member since 2006-04-25)

4

I don’t know enough about the “nitty gritty” of the wininet.dll implementation, sorry. I have attached the technique that I am currently using successfully. Import the attached modules into your VBA project.

It starts with two modules (plus a “helper” module) that I plagiarized from Kevin Wilson at www.thevbzone.com. They were originally written for VB5/VB6. Minor customizations were needed to get it to work with VBA5 (used in BusObj v5), and those customizations are clearly marked in the code:
[list]- basAddrOf module contains a manual replacement for the AddressOf function. AddressOf is used in the code, but doesn’t exist in VBA5.

  • modWININET lays the foundation … all of the “Declare Function” stuff
  • modFTP encapsulates those functions into more user friendly procedures … essentially a mini-API.[/list]Finally, the EDWFinance module is where I use the modFTP API to do the actual work. There is also functionality in that module that sends an email.

This is not a trivial thing to implement, but hopefully it may nudge you in the right direction.
FTP via VBA.zip (66.0 KB)

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

5

Thanks a ton Dwayne …It worked like magic…

-Ashwin N

ashwin_n84 :india: (BOB member since 2006-04-25)

6

You are quite welcome!

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)