none
FTP in VBA

    Question

  • Hi Everyone,

    I know that this is a little bit off-topic but I do hope that someone can help me ...

    I am processing data with Excel VBA and would like to automatically copy the result to a web server.

    I found a neat little snippet of VBA code ...

    http://www.pcreview.co.uk/forums/ftp-vba-macro-t949945.html

    ... that I thought would allow me to do this.

    And it almost does:  I can connect and a file appears on the server but it is empty of data ... and then the connection fails. Very strange!

    Here is the FTP diagnostics ...

    Connected to www.xxx.com.
    220 FTP Server ready.
    ftp> user xxx xxx
    ---> USER xxx
    331 Password required for xxx
    ---> PASS xxx
    230 User xxx logged in
    ftp> cd dddd
    ---> CWD dddd
    250 CWD command successful
    ftp> ascii
    ---> TYPE A
    200 Type set to A
    ftp> put Z:\03_proj\13_DocPub\test02.txt test02.txt
    ---> PORT 10,0,2,15,193,61
    200 PORT command successful
    ---> STOR test02.txt
    425 Unable to build data connection: Connection timed out

    I googled a lot and some sources said that the FTP session should be in PASSIVE mode for the transfer to  complete.  I then read that Windows built-in FTP (command-line) tool does not allow passive mode.

    So my question here is:

    a.) Is this correct?  Is it the lack of PASSIVE mode which is preventing the transfer from completing?

    b.) Is it true that Microsoft doesn't support this?  If so, then I still need a command-line tool that I can control from Excel-vba so can anyone recommend a good one?

    Many thanks for any tips or confirmation that you can give me.

    Regards,

    Alan Searle

    Thursday, December 06, 2012 12:59 PM

All replies

  • No, you apparently need to specify the port for many of the commands.

    http://support.microsoft.com/kb/271078

    Thursday, December 06, 2012 2:23 PM
  • Yes, you are right about the port.  Here is a quote from the site you mention ...

    "The FTP clients that ship with Windows do not support passive mode. Therefore, they always need to negotiate a data port when issuing a command that returns data."

    So now I am trying to find out which syntax to use to "negotiate a data port".

    I checked back with my service provider and apparently the port is "21" so I tried the following ...

    open www.mysite.com:21

    ... but could not connect.

    Without the port declaration I find that I can connect but whenever I try to handle data (e.g. "ls" for a directory listing) I get ...

    Unable to build data connection:  invalid argument

    I will keep googling to see if I can get the right syntax and maybe someone here in the forum can point me in the right direction?

    Many thanks,

    Alan Searle

    Friday, December 07, 2012 9:33 AM
  • Hi Alan,

    Thank you for posting in the MSDN Forum.

    According to your description, I think your issue is more related to VBA rather than Excel Object Model. I'm going to move this thread to the VBA Forum where more experts can help you.

    Thank you for your understanding.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 07, 2012 10:16 AM
    Moderator
  • Hallo Quist,

    Yes, that's a good idea.

    I managed to sort out my port syntax problem and could connect with ...

    open www.mysite.com 21

    (open www.mysite.com 20 wouldn't work)

    ... but that didn't fix my "unable to build data connection" problem.

    I looked at filezilla in the hope that I could control it from the command prompt but the options seemed very limited.

    This is starting to become a show-shopper for me so would appreciate any tips that might help.

    Many thanks,

    Alan Searle

    Friday, December 07, 2012 10:35 AM
  • The best FTP method I have found is using wininet.dll using win32 commands from VBA.  You can bing/google it for examples like:

    http://www.freevbcode.com/ShowCode.asp?ID=1991

    This is what IE uses.  I used it to collect usage stats from Excel VBA addins.  It handled the heavy usage perfectly.

    Example Command

    Public 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

    Sub OpenConnection(lngINET As Long, site As String, loginID As String, passWD As String, lngINETConn As Long)

      lngINETConn = InternetConnect(lngINET, site, 0, _
                                    loginID, passWD, 1, 0, 0)

    End Sub

    Sunday, December 09, 2012 11:55 PM
  • Many thanks for this tip and, indeed, it does seem to be the most promising for what I need.

    I tried to follow through on your recommendations but found that I did not have wininet.dll installed.  I then tried downloading/installing this from the microsoft support site ...

    http://www.microsoft.com/en-gb/download/search.aspx?q=wininet.dll

    ... but got the message that I had the incorrect version of Internet Exporer.

    This was a bit confusing because I don't want to use internet explorer but rather run things directly from VBA (in Excel and maybe also MS-Access).

    Can anyone help me with this?  Should I press on and fix the wininet.dll problem?  I can certainly do this but am worried that, if I roll out my little tool the need for wininet.dll will create installation problems* for other users.

    Any tips or further links to HOWTOs would be a great help.

    Many thanks,

    Alan Searle

    *:  Or could I manually declare the installation of the DLL?  Maybe by supplying the dll file and by making the appropriate registry changes for each installation?

    Sunday, January 27, 2013 3:42 PM
  • Wininet.dll is located at C:\Windows\System32.  It is located here in Vista and Windows 7.  You don't use IE you just use the same dll that IE uses.  You call everything using win32 commands in VBA.  Here are a few win32 commands.

    Public 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

    Public 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

    Public 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

    Monday, January 28, 2013 11:53 PM
  • I have managed to get wininet.dll working and can download from the internet using the following ...

    Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias _
        "InternetOpenUrlA" (ByVal hInternetSession As Long, _
                            ByVal lpszUrl As String, _
                            ByVal lpszHeaders As String, _
                            ByVal dwHeadersLength As Long, _
                            ByVal dwFlags As Long, _
                            ByVal dwContext As Long) As Long

    ... but when I try to write a file using FtpPutFile ...

    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

    ... the connection is established but the transfer fails with the function returning "false".

    I have tried a number of syntax permutations but cannot get it to work and so what I'd really like to know is how to debug the failure?  Yes, can I get FtpPutFile to tell me why it failed?

    Any tips on debugging would be a great help.  Or maybe someone has a snippet of code which is guaranteed to work (upload a file via ftp) in VBA (running in MS-Access or MS-Excel).

    Many thanks,

    Alan Searle


    • Edited by AlanInCologne Friday, March 29, 2013 11:10 AM Further information added
    Friday, March 29, 2013 9:43 AM
  • I tried using this snippet of code ...

    http://us.generation-nt.com/vba-wininet-dll-help-47947012.html

    ... and it compiled fine and ran but would not transfer the file to the server and gave no indication of why.

    Arrrgghh:  This is driving me crazy.  All I want to do is process a little data in MS-Office and then transfer it to our web-server and can't understand why it is so tricky.

    I hope someone out there can help me?

    Many thanks,

    Alan Searle

    Friday, March 29, 2013 3:47 PM
  • Did you try to put the file manually using command line ftp commands?  I am thinking you may not have permissions to ftp to that directory.  I had lots of permission problems.
    Friday, March 29, 2013 11:23 PM
  • HI Team,

    I want all filenames from web folder. Is this possible to make macro in excel vba and get all filenames from web folder.

    Example : Folder path is - http://myweb/mysite/myfolder/mysubfolder/

    in this webfolder i have 10 excel or any files....

    I want list of all files. is this possible?

    Wednesday, November 06, 2013 2:43 AM
  • Did you ever a good response, that allows you to FTP a file from Excel VBA to a site using PASSIVE mode?


    Dean

    Tuesday, March 25, 2014 3:08 PM