Linking excel to SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Linking excel to SQL

Author

Topic

Steve Edwards
Starting Member

3 Posts

Posted - 2007-10-15 : 11:26:32

Hi
recently a consultant came and impressed by linking an Excel spreadsheet to SQL database.

The spreadsheet had a "live" connection to SQL.

He could refresh the SS and the data would refresh.

This would be great as it would allow my users to just open the SS when they needed the data.

Help
Steve

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-15 : 11:29:13

So what's the question here?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-15 : 11:30:17

Great work.
Where is the working query?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-15 : 12:36:51

quote:
Originally posted by Steve Edwards

Hi
recently a consultant came and impressed by linking an Excel spreadsheet to SQL database.

The spreadsheet had a "live" connection to SQL.

He could refresh the SS and the data would refresh.

This would be great as it would allow my users to just open the SS when they needed the data.

Help
Steve

Thanks for your information

Madhivanan

Failing to plan is Planning to fail

ruchirj07
Starting Member

37 Posts

Posted - 2007-10-16 : 03:12:08

Is there any way to export the data back to SQL SERVER after making changes in excel file which we had imported?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 03:28:50

One of the ways is http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Steve Edwards
Starting Member

3 Posts

Posted - 2007-10-17 : 04:50:10

Apologies if I was not specific enough in my query.
I watched a consultant enter code into Excel (not the code I have see following the link above). This created a live link to the SQL database. Directly into a table in the database.
He saved the excel file. Every time he accessed the excel file its contents changed to reflect the contents of the sql database.
I have not given exact database, server and table info as I am sure "servername", "database" and "table" are all that would be needed in the explanation.
Thanks
Steve

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-17 : 05:00:29

Attaching SQL table/query results to Excel is really not very difficult. You can either do it by using Import External Data option or by writing a VBA macro.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

demifelix
Starting Member

22 Posts

Posted - 2007-10-17 : 09:04:53

quote:
Originally posted by Steve Edwards

Apologies if I was not specific enough in my query.
I watched a consultant enter code into Excel (not the code I have see following the link above). This created a live link to the SQL database. Directly into a table in the database.
He saved the excel file. Every time he accessed the excel file its contents changed to reflect the contents of the sql database.
I have not given exact database, server and table info as I am sure "servername", "database" and "table" are all that would be needed in the explanation.
Thanks
Steve

At my company we use pivot table, which has ODBC connection to the SQL table, and then we write macro scripts to have the pivot table refresh automatically everytime the SS is open. Wonder if this is what you saw with that consultant.

Steve Edwards
Starting Member

3 Posts

Posted - 2007-10-18 : 15:03:39

Hi Demifelix

That sounds good to me. I have managed the Import data from the external source which is great but it is dead data. It needs to refresh.
So next question. How?
thanks
Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources