Excel - including data from a remote (web) sheet?

Question

As a part of providing dynamic dashboards / reports, I've been investigating having a static Excel workbook that would contain a raw data sheet periodically updated from a web source. I'm currently using Excel 2010.

I really want to keep the workbook VBA / macro-free to reduce friction and potential errors.

My initial attempt was to add it as a workbook connection under Data -> From Other Sources -> From XML Data Import and pointed it to the url of the remote Excel workbook. This does pretty much what I want... except for sheets with more than 255 columns, unfortunately. They're simply truncated.

Is there really no way to just get Excel to include / refresh another sheet pulled from the web? If not, what would be the general approach otherwise? It seems like a pretty common usecase :-)

Answer 1

The solution I found to exceed the 255 column limit was to simply create a raw XML document (not an Excel workbook) and have Excel consume that. It's a bit more bandwidth more heavy (haven't experimented with compression), but at least it works.