• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2010 Remove Data Connections, keep the data, combine monthly workbooks.

wilkies0106aw

New Member
All,


Dept 1 are downloading data into excel on the 1st of every month from an SQL db. This is their archive solution since the SQL database is always current and doesn't or can't hold historic data - just not been set up that way.


I've created an excel dashboard (excel 2010 workbook) using the data as above. Each SQL store procedure has its own connection within the excel workbook and downloads its data onto a separate worksheet. From these worksheets I've created pivot tables which form my dashboard.


With me so far?


1. How can I create my dashboard workbook at a fixed point in time i.e. on the 1st of every month (it can be manually opened to run a macro etc.), keep the data, remove the data connections?


2. Combine the monthly data into a 'master' report since they will want to compare various figures month on month?


Thanks


W
 
1. To delete all connections in the workbook.

[pre]
Code:
Public Sub RemoveConnections()
Dim cn
For Each cn In ThisWorkbook.Connections
cn.Delete
Next cn
End Sub
[/pre]
 
Back
Top