Securely get data to excel (automatically - not download)

[IanT]

Currently we sit securely behind a firewall. Many people use public looks in order to suck the data into an XL workbook using the webquery functionality. A lot of people have a lot of processes using this functionality (eg workbooks with loads of sheets in that pull lots of different looks data and then generate a powerpoint).
We are looking at moving to the cloud and so the public looks have to go.

It is important people own their own workbooks and not have one generated for them since they do a lot of bits and pieces in them and they are a living document.

We have thought about creating a macro or plugin which uses the API however this means saving users credentials in the workbook which brings its own complications.

Would be great to get some thoughts from people who have come up against this or have some ideas.

Thanks!