Import an FTP file into Excel

[howard]

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

 

[John_w]

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:

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

 

[howard]

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

 

[John_w]

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.

 

[howard]

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

 

[John_w]

"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.

 

[howard]

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

 

[xenou]

East Vat*.xls

Spaces in command line scripts are often problematic. Maybe:

"East Vat*.xls"

 

[howard]

Hi Xenou

Thanks for the advise.

 

[sumeetp]

Hi,

Can we use this same for sFTP server too?