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 :-)