export data to a specificc cell in excel sheet

Question

export data to a specificc cell in excel sheet

CrystalVis

SSC Eights!

Points: 979
More actions
July 15, 2004 at 1:34 pm

#60200

i'm trying to export data from a table within sql server to a specific cell in an excel sheet. for example
select sum(amount) from tbl_Totals export to cell c24 of an abc spreadsheet. right now i have a data pump from sql server to the excel destination but have no idea how to pump the data into cell c24 of the spreadsheet. any help/suggestion/example of how i can accomplish this is greatly appreciated. thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bizzeau SSC Enthusiast Points: 106 More actions · Answer 1

It is not possible, to my knowledge, to place a specific piece of data into a specific cell in Excel via Transformation Task.

However, you can accomplish this task using an ActiveX Script Task and declare your own Excel Objects and ADO objects to pull/push your data around in Excel. I've done it before...it's not hard...

CrystalVis SSC Eights! Points: 979 More actions · Answer 2

thanks for your response. i'm awared that i have to use activex script task in order to accomplish this. but how? any example? thanks

bizzeau SSC Enthusiast Points: 106 More actions · Answer 3

Try putting some of this code together to make it work for you...these are some snippets of stuff I have previously worked on / played with:

Dim adoConn, adoCmd, adoRst

Set adoConn = CreateObject("ADODB.Connection")

Set adoCmd = CreateObject("ADODB.Command")

Set adoRst = CreateObject("ADODB.Recordset")

adoConn.ConnectionString = "driver={SQL Server};server=<SERVERNAME>;uid=;pwd=;database=<DATABASENAME>"

adoConn.ConnectionTimeout = 60

adoConn.Open

adoCmd.CommandText = "<SELECT STATEMENT>"

adoCmd.ActiveConnection = adoConn

Set adoRst = adoCmd.Execute

Dim xlApp, xlWkb, xlWks

Set xlApp = CreateObject("Excel.Application")

Set xlWkb = xlApp.Workbooks.Open("<FILEPATH>")

Set xlWks = xlWkb.Worksheets(1)

' Turn option off so your application doesn't halt for Excel Alerts

xlApp.DisplayAlerts = False

xlWks.Range("A2").Value = ""

xlWkb.Save

xlApp.Quit

xlApp.DisplayAlerts = True

Set xlApp = Nothing

Set xlWkb = Nothing

Set xlWks = Nothing

Let me know if you need any additional help, I just tried to throw something quick and dirty together...I'm sure you can figure out the rest...

Mike Howell Mr or Mrs. 500 Points: 521 More actions · Answer 4

Not a DTS solution, but why don't you use the Get Data feature from Excel. It can then refresh the data each time it is opened. If you don't want "Live" data, then you could create a table that DTS updates on a schedule and pull from that table.

Another option that may or may not work for your problem.

Mike

Mike Prascak SSC Eights! Points: 930 More actions · Answer 5

The Microsoft Query functions within Excel will allow you to pull the data from an SQL data source. You can save the query, refresh it when you enter the spreadsheet, and put it into a specific cell. Be sure that the reply from the sql command delivers just a single cell or you will get a range of cells filled. It also useful for more than just a single cell. Do give it a try; it may work for you. Certainly the activex seems to have more "exactness", but you should have it in your bag of tricks.

Mike P.

CrystalVis SSC Eights! Points: 979 More actions · Answer 6

Thank you all your help/input. My project is much more complicate than what I have posted. There are many computations involved within the dts package to arrive to various total figures beg_total, end_total, new_chargs...each of this total must go into a specific cell of the spreadsheet.

Bizzeau, i'll try your method and post the result back here. Again, thank you for taking the time to offer the helps.

Bob Johnson Mr or Mrs. 500 Points: 530 More actions · Answer 7

Instead of pushing the data into excel via DTS you could try pulliinng the data into Excel with VBA. A simple example follows . . .

With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DSN=Northwind;UID=sa;PWD=;APP=Microsoft Office XP;WSID=NS-1;DATABASE=Northwind2" _

, Destination:=Range("A1"))

.CommandText = Array( _

"SELECT Categories.CategoryName" & Chr(13) & "" & Chr(10) & "FROM Northwind2.dbo.Categories Categories")

.Name = "Query from Northwind"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = True

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.Refresh BackgroundQuery:=False

End With

Of course, you'll have to work out the query logic for each value you need to post to each cell.