Cannot get FTP macro to work. From someone else.

nicolewh

Well-known Member
Joined
Aug 7, 2009
Messages
554
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

Rich (BB code):
'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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

davidam

Active Member
Joined
May 28, 2010
Messages
452
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

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,482
Members
414,144
Latest member
UDFNewbie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top