Post reply

store data on two different sheets in excel

  • December 20, 2006 at 12:56 am

    #69369

    Hi

    In my table i have one field with different values and i am runnning a DTS pacakage to export this data to excel. Is it possible to store data on different sheets in a single workbook according to my field value...

    Like if i have three values in that field A,B,C

    then excel sheet1 show A sheet2 show B and Sheet3 shows C

    Regards

  • December 20, 2006 at 2:37 am

    #679064

    Yes.  In the properties of your Transform Data task, you can specify the name of the sheet in the Destination tab.

    John

  • December 20, 2006 at 4:38 am

    #679079

    Thanks John

    I tried that what don't able to figure out how to show one code data on one sheet and other code on second sheet

    This is my query i want to show data by Promo Ref

    SELECT     Company, [Product Manager], Vendor, Delsequence, PromoRef, PartNumber,Discount, DateRequired,Comments, ReceivingAddressCode,OrderClass, PurchasingOfficer FROM       

    order by PromoRef

    i need to show sheet has only one type of PromoRef

    This query i am using in transfer data task

     

    Regards

  • December 20, 2006 at 6:29 am

    #679098

    Then I think you probably need to create one task for each sheet you want to export to.  I haven't tried it, but it shouldn't be too difficult to test.

    John

  • December 21, 2006 at 7:09 am

    #679403

    You'll need to create and Active-X task that creates and Excel object.  Once that is done you can create a for each loop based upon your PromoRef.

    You will then be able to name each sheet in Excel with the PromoRef and execute a PromoRef specific SQL statement.

    Below is a sample that shows how to create the Excel object.  It however does its looping outside of the Active-X and it's easier to do the loop inside.

    http://sqljunkies.com/Article/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk

    Regards,
    Matt

Viewing 5 posts - 1 through 4 (of 4 total)

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