Post reply

export data to a specificc cell in excel sheet

  • 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

  • July 15, 2004 at 2:41 pm

    #514749

    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...

  • July 15, 2004 at 3:55 pm

    #514766

    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

  • July 15, 2004 at 5:36 pm

    #514780

    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...

  • July 15, 2004 at 6:20 pm

    #514784

    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

  • July 16, 2004 at 7:23 am

    #514834

    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.

  • July 16, 2004 at 7:49 am

    #514843

    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.

  • July 16, 2004 at 9:47 am

    #514897

    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.

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

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