This seems to be an Excel question which you should post in excel/vba forum. This can be done but to automate it, the person need to have access to login of url.
I think it is not an excel question. I want to automate my excel source in Tableau. But source is an excel which sits at some other location, web where i can access it from url.