SaveAs method fails when saving to FTP site.

Steve Brennan · Sep 30, 2003

If you manually save a file to an FTP site in Excel, you
get the FTP log on dialog where you are prompted for
username and password to log into the FTP site...

How do you log on to the FTP site using Visual Basic so
the SaveAs method doesn't fail, since there is no argument
for providing the username & password required to access
the FTP site?

Thanks,

Steve Brennan

Robin Hammond · Sep 30, 2003

Steve,

I've no idea if this works for FTP or if it is site specific but this is
from Stephen Bullen, John Green, Bovey, Rosenberg's book.

set obk = workbooks.open("http://UserID:[email protected]/book1.xls")

obk.saveas "http://UserID:[email protected]/book2.xls"

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in

Steve Brennan · Sep 30, 2003

Robin,

Thanks for the reply! The SaveAs method fails, and the
Open method doesn't pass the UserID and Password, but I
used it like this:

On Error Resume Next
Workbooks.Open Filename:= _
"ftp://UserID

[email protected]/bogusfile.xls"

This does force the FTP Log On dialog, and after the user
enters the username and password, it attempts to open a
non-existent file on the FTP server...it errors, but
the "On Error Resume Next" ignores the error and the
procedure continues to run. The SaveAs method then works
because the user has logged in to the FTP site.