Exporting a SQL query into specific cells in MS Excel 1

[CoolFactor]

I'm trying to export a view I created in SQL Server 2000 into specific cells within two worksheets within a workbook.

 

[RRinTetons]

All the rows from the view into a specific range in the spreadsheet's workbooks?

Use Excel's Data menu to connect to an External Data Source. Define the data source with server name, connection string, etc., specify the view from which you want the data. That can be as simple as SELECT * FROM MyView, or as complex as you want it to be. You can also use a call to a parameterized stored procedure to make it more flexible. Part of defining the data source is specifying where in the workbook you want the data to land. Once that's set up the data an be updated from Excel's menu's, toolbars or with a custom button on the sheet if it's to be deploye to end users. The data will update in the range you assigned to it. Excel formulae can reference the range to carry the data on into more interactive Excel models.


-
Richard Ray
Jackson Hole Mountain Resort

 

[CoolFactor]

Thank you first of all for responding back to me. Below you will find the simple table I am trying to export to Excel.

strFY AccountID CostElementWBS CostElementTitle
2008 1 7 Integrated Logistics Support
2008 1 7 Integrated Logistics Support

I want to export the 1st record of this table to excel workbook "Test 1," in the following way:

In Workheet "Sheet1," I want the data pertaining to field strFY to go to Cell "A1," and then I want the data pertaining to field AccountID to go to Cell "A2."

Then in Worksheet "Sheet2," I want the data pertaining to field CostElementWBS to go to Cell "B1," and then I want the data pertaining to field CostElementTitle to go to Cell "B2."

FOR THE SECOND RECORD IN THIS TABLE:

I want to export the 2nd record of this table to excel workbook "Test 2," in the following way:

In Workheet "Sheet1," I want the data pertaining to field strFY to go to Cell "A1," and then I want the data pertaining to field AccountID to go to Cell "A2."

Then in Worksheet "Sheet2," I want the data pertaining to field CostElementWBS to go to Cell "B1," and then I want the data pertaining to field CostElementTitle to go to Cell "B2."

If it wouldn't be too much trouble if you could give a step by step process of how to accomplish this. I appreciate anything you could suggest.

 

[RRinTetons]

The two rows in your example are identical, but I assume there's a column that uniquely identifies one as Row 1 and the other as Row 2? If not, you'll need one. I'm going to call the column RowID and assume it contains a value of '1' for the first row and '2' for the second. If something like the combination of the strFY and the AccountID give a unique key you can use those in query. Best to creae a Primary Key for your table and use that to get the rows you want.

You'll still use the Import External Data functionality from Excel, but the query will have a column list in SELECT clause of the query and some criteria in the WHERE clause. Try Googling for 'Excel external data' for tutorials on how the basic functionality works, or work through the Excel Help on the topic.

Once you've done that it will be easier: here's the framework:

Create a connection to your database, specify the data as coming from your view and select the columns strFY and AccountID. Specify that you want only the data from the row with RowID = 1. You can either do that in the visual query editor or by simply writing the SQL statement and pasting it into the SQL window.

(SELECT strFY, AccountID FROM MyView WHERE rowID = 1)

Make A1 the upper left of your return range. strFY will appear in A1 and AccountID will appear in A2 for row 1. Repeat on Sheet2 for CostElementWBS and CostElementTitle.

(SELECT CostElementWBS, CostElementTitle FROM MyView WHERE rowID = 1)

Copy the workbook and edit the queries on sheet 1 and sheet 2 to get the data for row 2.
(SELECT strFY, AccountID FROM MyView WHERE rowID = 2)
(SELECT CostElementWBS, CostElementTitle FROM MyView WHERE rowID = 2)



-
Richard Ray
Jackson Hole Mountain Resort

 

[CoolFactor]

Thank you that was a lot of help.