Dynamically exporting data to a new excel sheet

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 2000 Forums

Transact-SQL (2000)

Dynamically exporting data to a new excel sheet

Author

Topic

Paul Marks
Starting Member

7 Posts

Posted - 2006-11-22 : 07:07:41

Dear Forum

I have a problem where i am trying to write a procedure to open up an existing excel template rename the created sheet and export data to it.

Can someoneout there please assist with the syntax and SQL

With Kind REgards

Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 07:12:18

Huh?
You want us to do your work for you?

Have you tried GOOGLE?

Peter Larsson
Helsingborg, Sweden

Paul Marks
Starting Member

7 Posts

Posted - 2006-11-22 : 07:36:53

Dear Peso

No just some direction would be helpful

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 07:50:49

DTS?
SSIS?

Peter Larsson
Helsingborg, Sweden

Paul Marks
Starting Member

7 Posts

Posted - 2006-11-22 : 08:00:59

Peso

I can see you are increadibly helpful. Are you always like this with every one on the forum or have you taken some distinct dislike to me in particular. Can you help. If not just say so

The DTS routines will not dynamically rename a new sheet based upon a template.

As for the SSIS method. I am not familar with this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 08:04:23

Only with people who want other people to do their chores.

T-SQL can not rename a Excel Sheet for you.
You have to implement some kind of OA_method for this.

See if this article helps http://msdn.microsoft.com/msdnmag/issues/04/04/DataPoints/

Peter Larsson
Helsingborg, Sweden

Paul Marks
Starting Member

7 Posts

Posted - 2006-11-22 : 08:14:13

Peso

I never had ANY expectations of someone performing my chores. Just some assistance from another Human being who knows a little more than I.

I refute the insinuation!!!!!

With Kind Regards

Paul

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-22 : 09:56:38

quote:
Originally posted by Paul Marks

Dear Forum

I have a problem where i am trying to write a procedure to open up an existing excel template rename the created sheet and export data to it.

Can someoneout there please assist with the syntax and SQL

With Kind REgards

Paul

Paul,

First off what version of SQL Server?

In any case sounds like you need to work with COM Automation...Ken Henderson's book talks a lot about this, but I am curious as to why you need to do this....for the most part when I do stuff like this, I usually archive what was, and move the new data in.

Who is giving you this requirment.

It helps us if we understand what and why you are trying to do something.

Also, read the hint link in my sig.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Paul Marks
Starting Member

7 Posts

Posted - 2006-11-22 : 10:16:04

Dear Brett

Firstly thank you for your reply.

SQL 2000 v8.0

The requirement is as follows: Part of a larger application

A series of Service Audits for a well know supermarket scheduled and produced on a routine basis (via the app) ~ 30 /day. The results of these sent to an individual spreadsheet based upon one template. A spreadsheet would be created automatically after each scheduled execution of the app via the stored proc.

I believe Peso has probably answered my questions by indicating that the solution to this problem is not resolvable using this methodology.

We were trying to avoid developing an interface for this by just executing a Stored Procedure. All the parameters required from the stored data to name the newly created s/s based upon the template were available from the source data.

If this is not the solution OK. Like you have suggested would need to develop code to do this within the app.

With Kind Regards

Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 10:37:11

What are the sheet named as?
If the names are not changing, it is very possible to put the data in some single cells in Excel.

I recently did this for inTouch Solutions in Chicago.

Peter Larsson
Helsingborg, Sweden

Paul Marks
Starting Member

7 Posts

Posted - 2006-11-22 : 10:48:37

Peso

Each worksheet would have its own name based upon an eventid for the generated schedule.

IE:-
Smktsurvey12345.xls
the next one being

Smktsurvey12346.xls

ect

With Kind Regards

Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 10:51:56

Well I don't think that is the sheet name. I believe that is the FILE name.

What I did for inTouch, was that I created one template file and with xp_cmdshell I copied that file to the new position and used OPENROWSET to set some single cells to new values. Then the graphs in the sheet automatically were updated upon opening the file.

Peter Larsson
Helsingborg, Sweden

Paul Marks
Starting Member

7 Posts

Posted - 2006-11-22 : 11:17:00

Dear Peso

Apologies for confusing terminology. Not my intention.

It appears from you wealth of experience that the solution I am sourcing using this method is not possible. Back to redeveloping the app I think.

Thank you all for your assistance. Greatfully appreciated

With Kind Regards

Paul

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-11-22 : 13:38:11

DTS should be able to dynamically create file names using variables.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources