Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VBA FTP
I am trying to upload a spreadsheet to my webspace via VBA.
It isn't working.
 
Am I missing something obvious?
I have stepped through and it crashes at the end, going to the msgbox and showing a fail.
I think the problem is at the HOSTNAME stage, but for the life of me I cannot see it.
 
Here is the code I am using...
 

 
'Open the Internet object
 Private Declare Function InternetOpen _
   Lib "wininet.dll" _
     Alias "InternetOpenA" _
       (ByVal sAgent As String, _
        ByVal lAccessType As Long, _
        ByVal sProxyName As String, _
        ByVal sProxyBypass As String, _
        ByVal lFlags As Long) As Long
 
'Connect to the network
 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 lService As Long, _
        ByVal lFlags As Long, _
        ByVal lContext As Long) As Long
 
'Get a file using FTP
 Private Declare Function FtpGetFile _
   Lib "wininet.dll" _
     Alias "FtpGetFileA" _
       (ByVal hFtpSession As Long, _
        ByVal lpszRemoteFile As String, _
        ByVal lpszNewFile As String, _
        ByVal fFailIfExists As Boolean, _
        ByVal dwFlagsAndAttributes As Long, _
        ByVal dwFlags As Long, _
        ByVal dwContext As Long) As Boolean
 
'Send a file using FTP
 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
 
'Close the Internet object
 Private Declare Function InternetCloseHandle _
   Lib "wininet.dll" _
     (ByVal hInet As Long) As Integer
 
Sub UploadFTP()
 
  Dim hostFile As String
  Dim INet As Long
  Dim INetConn As Long
  Dim Password As String
  Dim RetVal As Long
  Dim ServerName As String
  Dim Success As Long
  Dim UserName As String
  
  Const ASCII_TRANSFER = 1
  Const BINARY_TRANSFER = 2
 
    ServerName = "www.users.freenetname.co.uk"
    UserName = "My User Name"
    Password = "My Password"
    localFile = "C:\Users\Public\Public Documents\Tables.xlsm"
    hostFile = "\\public_html\Tables.xlsm"
 
      RetVal = False
      INet = InternetOpen("MyFTP Control", 1&, vbNullString, vbNullString, 0&)
        If INet > 0 Then
          INetConn = InternetConnect(INet, ServerName, 0&, UserName, Password, 1&, 0&, 0&)
            If INetConn > 0 Then
              Success = FtpPutFile(INetConn, localFile, hostFile, BINARY_TRANSFER, 0&)
              RetVal = InternetCloseHandle(INetConn)
            End If
         RetVal = InternetCloseHandle(INet)
        End If
 
      If Success <> 0 Then
        MsgBox ("Upload process completed")
      Else
        MsgBox "FTP File Error!"
      End If
 
End Sub
Posted 28-Jan-12 11:39am
Dalek Dave432.7K
Comments
Amir Mahfoozi at 29-Jan-12 1:50am
   
The code seems well. Please specify whether it passes all "If"s or not. If not, it fails in which "If"? and what is the error details?
And generally make sure that ftp upload can be done with a third party application to ensure that no firewall will prevent this operation then focus on this application.
Slacker007 at 29-Jan-12 5:35am
   
This is a great question. When you do get it solved, please edit with your solution. :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Change the URL for FTP. Worked for me...
 
hostFile = "/public_html/Tables.xlsm"
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If "Success" is returning a "0" then I would look at your FtpPutFile() function and I would look at the transfer parameter. It doesn't look like this:
 
MSDN - FtpPutFile()[^]
 

Try using the values listed in this page link and see what happens. Just a thought.
 
Good luck, Dave!
  Permalink  
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I see 2 potential reasons of error:
1) you are trying to put current file (file can't put/send itself),
2) you don't set destination folder (public_html)
 
Here[^] you can find the description of function and complete, working example (after few simple modifications).
 
I hope it will be helpful.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 3 Jun 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100