Cannot get FTP macro to work. From someone else.

[nicolewh]

The following macro is long but I'm trying to see it work. The red colored font is the part of hte macro I am supposed to change. I know my username, password, where my local file is, but I'm not sure what a server name is (I think is my website name - not sure). Also, I'm not sure what I put for the host file. I've tried many different types of scenarios. Any suggestions?

The problem is the promt "FTP File Error!" always comes up and the success promt doesn't.

Thanks

Nicole

'Written: June 11, 2008
'Author:  Leith Ross

'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()

 'When uploading a file, make sure you have permisson to create a file on the server.
 'The size limit for a uploading a file is 4GB.
 
  Dim hostFile As String
  Dim INet As Long
  Dim INetConn As Long
  Dim hostFile As String
  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 = "myserver.some.company"
    UserName = "anonymous"
    Password = "MyEmail@somewhere.net"
    localFile = "C:\My Documents\Test.Txt"
    hostFile = "\\My Test File.txt"

      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

 

[davidam]

Don't know if you ever worked it out, but ftp does not work with spaces in paths: ie C:\My Documents will always fail...needs to be C:\MyDocuments

 

[stapuff]

Solved - Re: Cannot get FTP macro to work. From someone else.

I messed with this for a couple hours and found several issues, but got it to work like a champ!

1) Create a folder C:\excel I created C:\excel_xml (important do not have any spaces in the name). Nothing like C:\excel xml ...if you do the code will fail. Also do not put the folder in your Documents and Settings either since it has spaces.

2) Place the file you want to ftp with data in it into the folder you created above....again I place my file themaster.xml in the folder I created C:\excel_xml

I updated the following code below, but the rest of the code above this from the initial post also needs to be added to your project. Once you do this...run the macro.

You will see the data transfer take place.

Booyah and enjoy,

stapuff

Sub UploadFTP()

'When uploading a file, make sure you have permisson to create a file on the server.
'The size limit for a uploading a file is 4GB.

Dim hostFile As String
Dim INet As Long
Dim INetConn As Long
Dim localFile As String
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 = "myserver ip address"
UserName = "my server username"
Password = "my server password"
localFile = "C:\excel_xml\themaster.xml"
' note the change to the hostFile below. This will put the new themaster.xml I will ftp at my website address in a folder I call "test"
hostFile = "/public_html/test/themaster.xml"

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