Using VB to upload to an FTP

**CCK** · 06-10-2008, 08:16 PM

I'm looking for a way to automatically upload a file to an FTP site. I'm currently writing out a text file, but then have to use an FTP app to move the file over... any thoughts on how to best do this?

**Leith Ross** · 06-10-2008, 10:18 PM

Hello CCK,

Welcome to the Forum!

As a new member of the forum, please take a few minutes and read the forum rules for posting and general etiquette. To see them, click on the link below...

Forum Rules

There are two ways to use the FTP with VBA. One is to create and run a batch file or to user API calls. Personally, I feel the API is easier and safer to use. Change the information marked in red below to match your information.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

**CCK** · 06-11-2008, 03:33 AM

Leith, thanks that was fast.

I'll work with this tomorrow, to see if I can get it running properly.
I did a quick test, and had an error "duplicate definition" on the "Dim hostFile As String"

any thoughts, I don't see it in another place?

also, a couple quick questions, is the "hostFile" a new name that will be used to rename the localFile when it's place on the FTP?

localFile = "C:\My Documents\Test.Txt"
hostFile = "\\My Test File.txt"

thanks

CCK

**CCK** · 06-11-2008, 03:47 AM

Ok, I had to try tonight, and it works great...!

I just commented out the one error, and it's fine..

I do have another question though... The location I'm trying to put the file is several layer down...

The FTP site is 123.123.xxxx.xxx

But when it try to use the following it fails...

123.123.xxxx.xxx/folder1/folder2/

how should I input this to make it work properly...

Thanks for the help... !!

CCK

**CCK** · 03-20-2009, 09:28 PM

Can you please mark this as solved... !

thank you

**Saul** · 02-13-2011, 06:36 PM

Hey Leith, your code is very useful. Is there some statement on copyright, reuse, licence you can give as I want to include it in a project and credit without problems surfacing later.

**nabin** · 11-20-2011, 06:01 AM

Fantastic solution to add file to an FTP site thru excel VBA.

But one thing more. Can we add a folder it its not exist in the ftp site.

I mean if there is not a specific folder in the ftp we can create a folder into the ftp site and within that folder we have to put a local file.

Please sir I shall be glad if you can give the code for it.

**nabin** · 11-20-2011, 06:04 AM

How can we add a folder to an ftp site

**evd46** · 12-03-2011, 06:11 AM

Hi Leith,
The macro works fine, indeed. Thx a lot!
How could I possibly upload a file to a subdirectory to the root (like "\XML")?
Thanks in advance for your answer.
Erik

**arthurbr** · 12-03-2011, 06:50 AM

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.