Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel document with 20+ sheets, all these sheets use the same SQL View, but they have different critirias on what to show, the only way I can get that working is to make 20 different connections each with its own SQL query.

But in case that the view needs to be updated I would have to update each and every one of these sheets, and as I have 7 of these documents it would take a long time and be very error prone.

So is there a way to share a connection across different sheets and then add additional SQL query to each to specify what to show on each sheet?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

I don't believe thats possible due to the way in which Excel cache's the data it pulls from SQL. Generally what I'll do in a similar situation is pull everything I need from SQL once into a "data" worksheet, then use it to farm out to "focus" worksheets that use the "data" worksheet as a data source. You can pull data into Pivots, or using Named Ranges, or directly into Charts-whatever your need is.

Two other options that may be worth considering if you have Excel 2010 are PowerPivot and Data Explorer. These free add-ins from MS provide nearly unlimited flexibility in pulling data from SQL (and other data sources) into Excel.

EDIT: Starting from scratch, try this:

  1. Rename a worksheet to Data.
  2. Add your data. Data>ExistingConnections.
  3. Choose the appropriate SQL connection file on your computer.
  4. In the Select Table dialog, find your SQL View (they should be displayed before Tables, and have a slightly different icon).
  5. In the Import Data dialog, choose Table (its the default) and then select the Properties button.
  6. On the Definition tab, change the command type from Table to SQL.
  7. In the Command Text box, change your SQL to something like: Select * From myview, then click OK.
  8. In the dialog box that pops up, select Yes.
  9. Back on the Import Data dialog, now select OK.
  10. Use this new Table to create any number of Pivot Tables on any number of sheets. These can then be filtered, organized and sorted independantly.
  11. If you do want to share some filters between Pivot Tables (e.g. dates), then you can set up slicers that are connected to all associated Pivot Tables that would then share the similar date filter, but otherwise be independant.
share|improve this answer
    
I tried to do the first, but if I choose Existing Connection and then Tables to choose my Data worksheet the only thing I can specify is CommandText and I dont know what to put in there –  Mech0z May 29 '13 at 13:47
    
That still dont make me able to make a sub SQL query on the sub sheets, if I make a filter, that wont work as from what I know a macro running on the document will ignore the filter. But I think what I want is just not possible –  Mech0z May 30 '13 at 8:14
    
You're right about the Macro, I didn't pick that up from the original question. If you have some more specifics about your data, it may help point to a better solution. I really think Powerpivot may hold the keys to this. As for my answer, if it didn't really answer your question, feel free to uncheck it and other folks may still actively look for a solution. –  dav May 30 '13 at 11:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.