sql and excel

[junkmail]

I am not sure this is the forum for my question but here is my problem. I have an excel spreadsheet that runs a stored procedure with a parameter and it works great using excel 2003 & 2007 but when I tried doing the same thing on a different SQL database the only one that seems to work is 2007. The 2003 version does not give any errors but I did notice that "refresh data" is grayed out. Any help would be greatly appreciated.

 

[MarkSweetland]

May have better luck in thie forum: forum68



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Qik3Coder]

Couldn't find the thread i wanted, but look in the faq section for generating dynamic sql. There are several recent posts here on dynamic sql (so you'll see the code below again)

here a blob to get you started:

CREATE TABLE myTestData(
[Year] int,
CRS_Code varchar(10),
School varchar(10),
Sub varchar(10),
prcnt money)


INSERT INTO myTestData
SELECT 2011, 'KT101', 'ML', 'English', .10 UNION ALL
SELECT 2011, 'KT101', 'ML', 'French', .60 UNION ALL
SELECT 2011, 'KT101', 'ML', 'German', .30 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'English', .60 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'French', .40 UNION ALL
SELECT 2011, 'KT102', 'ML', 'French', .50 UNION ALL
SELECT 2012, 'KT102', 'ML', 'French', .80 UNION ALL
SELECT 2013, 'KT102', 'ML', 'Japanese', .80 UNION ALL
SELECT 2013, 'KT102', 'JK', 'English', .20

DECLARE @_SQL varchar(8000), @int int
SELECT @_SQL = 'SELECT [Year], CRS_Code', @int = 0
SELECT  @_SQL = @_SQL + ', MAX(CASE WHEN t.School = ''' + tabs.School + ''' and t.SUB = ''' + 
	tabs.Sub + ''' THEN t.prcnt else 0 END) as ['+tabs.School+'-'+tabs.Sub +']' +char(13)
	FROM (Select School, SUB FROM (SELECT DISTINCT SUB FROM myTestData) as a
	CROSS JOIN (SELECT DISTINCT School FROM myTestData) as b) as Tabs
SELECT @_SQL = @_SQL + 'FROM myTestData t GROUP BY [YEAR], CRS_Code'

PRINT @_SQL

Exec(@_SQL)

Lodlaiden

You've got questions and source code. We want both!

 

[junkmail]

Thanks.

 

[Qik3Coder]

Weird...That post didn't land on the right thread.

Check you Connections in the Excel Sheet. Go to Data, Connections. Or try right clicking on the A1 cell -> Refresh data.

When you open the sheet, the location may not be trusted, so it defaults to disabling the connection.

Lod

You've got questions and source code. We want both!