Results 1 to 9 of 9

Import an FTP file into Excel

This is a discussion on Import an FTP file into Excel within the Excel Questions forums, part of the Question Forums category; I have an excel workbook and need to import a file for eg East Vat Account.xlsx residing on an FTP ...

  1. #1
    Board Regular
    Join Date
    Jun 2006
    Posts
    2,874

    Default Import an FTP file into Excel

    I have an excel workbook and need to import a file for eg East Vat Account.xlsx residing on an FTP site.

    I would like VBA code that will enble me to dump the FTP file East Vat Account.xls into a directory called downloads and then to import the file or alternatively to import the file directory from the FTP site

    Your assistance will be most appreciated

    Regards

    Howard

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    2,932

    Default Re: Import an FTP file into Excel

    Create and customise C:\FTP_commands.txt containing:

    open your.ftpserver.com
    user yourUsername yourPassword
    lcd C:\Downloads
    cd yourRemoteFolder
    binary
    get "East Vat Account.xlsx"
    bye

    Run it using:
    Code:
    Public Sub Ftp_Download_File()
    
        Dim FTPcommand As String
        Dim wsh As Object
        
        FTPcommand = "ftp -n -s:" & Chr(34) & "C:\FTP_commands.txt" & Chr(34)
        Set wsh = CreateObject("WScript.Shell")
        wsh.Run FTPcommand, 5, True
        
        Workbooks.Open "C:\Downloads\East Vat Account.xlsx"
        
    End Sub

  3. #3
    Board Regular
    Join Date
    Jun 2006
    Posts
    2,874

    Default Re: Import an FTP file into Excel

    Hi John

    Thanks for the help. The macro runs without any glitches, but the workbook "East Vat account" is not downloaded

    The script which is saved in notepad on my desktop is "ftp.txt" and is follows:

    open 18.2.14.196 (fictitious FTP)
    dave (fictitious)
    pass (fictitious)
    bin
    hash
    lcd c:\downloads
    cd east
    mget East Vat*.xls
    quit

    When I run the sccript in executable mode the workbook is downloaded. However, when I run the macro, it is not downloaded

    Your assistance in resolving this matter will be most appreciated

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    2,932

    Default Re: Import an FTP file into Excel

    What do you mean by executable mode?

    Delete the quit so that the command window stays open and see if there are any errors from the other commands. Also comment out the Workbooks.Open line while testing.

  5. #5
    Board Regular
    Join Date
    Jun 2006
    Posts
    2,874

    Default Re: Import an FTP file into Excel

    Hi John

    The problem seems to be occuring at the user name and passwords as the message comes up invalid login

    open 18.2.14.196 (fictitious FTP)
    dave (fictitious)
    pass (fictitious)
    bin
    hash
    lcd c:\downloads
    cd east
    mget East Vat*.xls
    quit

    should it be as follows:

    open 18.2.14.196 (fictitious FTP)
    user dave (fictitious)
    password pass (fictitious)
    bin
    hash
    lcd c:\downloads
    cd east
    mget East Vat*.xls
    quit


    Please advise how user name annd password must be set out

    Thanks

    Howard

  6. #6
    Board Regular
    Join Date
    Oct 2007
    Posts
    2,932

    Default Re: Import an FTP file into Excel

    "should it be as follows:"

    I don't know. Have you tried it?

    Did you try the syntax in my OP?

    user yourUsername yourPassword
    user dave pass

    If no joy, type ftp at the command prompt and then help, or help user. Your ftp might be different.

  7. #7
    Board Regular
    Join Date
    Jun 2006
    Posts
    2,874

    Default Re: Import an FTP file into Excel

    Hi John

    Thanks for the reply. I will be out of town for a few days, but will test code when I get back to the office on Friday & will let you know

    Thanks

    Howard

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,056

    Default Re: Import an FTP file into Excel

    East Vat*.xls
    Spaces in command line scripts are often problematic. Maybe:
    "East Vat*.xls"

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #9
    Board Regular
    Join Date
    Jun 2006
    Posts
    2,874

    Default Re: Import an FTP file into Excel

    Hi Xenou

    Thanks for the advise.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com