Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    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
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have some sample code to download a file. See this page:

    http://www.jkp-ads.com/articles/updateanaddin.asp

    Code:
    Private Declare Function URLDownloadToFile Lib "urlmon" _
        Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
        ByVal szURL As String, ByVal szFileName As String, _
        ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
    
    Private Sub DownloadFile(strWebFilename As String, strSaveFileName As String)
        ' Download the file.
        URLDownloadToFile 0, strWebFilename, strSaveFileName, 0, 0
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Jan

    Thanks for the help. I have copied your code into sheet1, but not sure how to run this.

    I also have the following code saved in notepad on my desktop as ftp.txt and have a macro that is linked to the script file.

    when I run the file ftp.exe that workbook East Vat clearing.xls is downloaded to a directory called downloads, but when I run the macro, I get no errors, but the file is not downloaded

    ftp.txt (in notepad saved on desktop)
    open 18.2.14.196 (fictitious FTP)
    dave (fictitious)
    pass (fictitious)
    bin
    hash
    lcd c:\downloads
    cd east
    mget East Vat*.xls
    quit

    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



    End Sub

    Your assistance in resolving the matter will be most appreciated

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using the routine to download a file is very easy:

    Code:
    Sub Foo()
        'Some code
        DownloadFile "CompleteURL2FIleGoesHere", "CompletePathToStoreFileGoesHere"
        'Other Code
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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