Password automation for .RefreshAll when linking to FTP site

G'day,

I am using the .RefreshAll function to update numerous tables of data, which is retrieved from .csv files which reside on an external corporate server.
The .RefreshAll function is initiated by a worksheet button, but performs exactly the same action as Data ribbons' Refresh All function.

Please Login or Register  to view this content.

It works fine as long as the user inputs the correct username and password for the FTP site, when prompted by the ftp log on dialogue.
The problem arises when they enter an incorrect password, or just click to close. It then produces a runtime error, and opens up the VB editor to debug.
After the first runtime error, attempting it again will produce the same result unless the application is quit and restarted.

My preference is to hard code the ftp user/password in VBA to completely automate the process.

Alternatively create a text box popup instead, prompting them to check their password?


Any help would be greatly appreciated,

Muzza68

Try something like this...

Please Login or Register  to view this content.

Thanks @Alpha,

Failure notification works correctly with your code, but you still cannot retry the refresh (and thus the ftp log on) without closing down Excel and re-starting.....

Does anybody else have any ideas about pushing the password to the ftp process?