We have a series of quick "reports" which are just auto-refreshing excel spreadsheets which link to views in SQL Server. This allows me to change the underlying view when they want additional information, and the column will automatically appear the next time they refresh. It works, and I have to be able to use excel for this so please don't try convincing me to change that. The annoying problem I face is that when I add columns, they always appear at the right most position to previous columns in the external data table regardless of their actual position in the data set.
If I remove the external data set and re-add it from the Existing Connections screen, that works. How can I, not using VBA if possible, have it always load the columns in the order in which they are returned from the SQL data set.
I have also thought about saving the excel spreadsheet after refreshing it with only one column returned from the procedure, and locking the spreadsheet so it cannot be saved, so that each time it loads it load every column as a new column.
I am using Excel 2007.