Any help / guidance would be appreciated - I know there is lots of ways to do this, so hoping for a nudge in the right direction. (I'm using Excel 2007)
I have a workbook with 6 worksheets each with a connection to a Sharepoint table where users can update information. Each Sharepoint table is identical in terms of available fields / order. Not a huge amount of data - perhaps up to 500 to 600 records per table.
The idea is to have a single XL table with a consolidated view of all the data. I do need columns of formulae which give me various views from which I can then set a number of pivots for controls, reporting and analysis. Having an Excel Table is very useful as the formulae will auto-fill against the table and the data range of the pivots remain constant.
Any ideas the best way to do this?
So far I only have the following:
ActiveWorkbook.Connections("owssvr[2]").Refresh
ActiveWorkbook.Connections("owssvr[3]").Refresh
etc - til all connections are refreshed
Some code to consolidate to a new summary table, overwriting existing data from last refresh
ActiveWorkbook.RefreshAll
Any help would be most gratefully received
Thanks
ps - I know the best solution here would be a single Sharepoint table with different views / permissions but security won't let me touch anything other than the basic out-the-box features of Sharepoint (2010).
I have a workbook with 6 worksheets each with a connection to a Sharepoint table where users can update information. Each Sharepoint table is identical in terms of available fields / order. Not a huge amount of data - perhaps up to 500 to 600 records per table.
The idea is to have a single XL table with a consolidated view of all the data. I do need columns of formulae which give me various views from which I can then set a number of pivots for controls, reporting and analysis. Having an Excel Table is very useful as the formulae will auto-fill against the table and the data range of the pivots remain constant.
Any ideas the best way to do this?
So far I only have the following:
ActiveWorkbook.Connections("owssvr[2]").Refresh
ActiveWorkbook.Connections("owssvr[3]").Refresh
etc - til all connections are refreshed
Some code to consolidate to a new summary table, overwriting existing data from last refresh
ActiveWorkbook.RefreshAll
Any help would be most gratefully received
Thanks
ps - I know the best solution here would be a single Sharepoint table with different views / permissions but security won't let me touch anything other than the basic out-the-box features of Sharepoint (2010).
