none
Add custom sheet in existing excel files. RRS feed

  • Question

  • I have created dynamic excel files with 2 worksheets in each file. I want to add another worksheet into each excel file which consists of my custom page which is needed to be added in each existing excel file.

    All the existing excel files are in a folder and I am looking to loop through all the existing excel files in that folder and add the custom page to each file being as a 3rd worksheet of that excel file.

    Tuesday, September 10, 2019 2:37 PM

All replies

  • Hi minhalraffat,

    You could use Execute SQL Task with excel connection manager and use CREATE TABLE statement as shown in the following link. In your case you don't need dynamic code as your sheet is fixed.

    Please refer to Exporting SQLServer data to multiple sheets of Excel file.

    Check if it helps.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, September 11, 2019 2:07 AM
  • Hi minhalraffat,

    You could use Execute SQL Task with excel connection manager and use CREATE TABLE statement as shown in the following link. In your case you don't need dynamic code as your sheet is fixed.

    Please refer to Exporting SQLServer data to multiple sheets of Excel file.

    Check if it helps.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hey Zoe,

    I am not able to get the file system task to copy the template from the source file and just loops it through all the other files. Can you please explain how to do that?

    Thanks,

    Minhal

    Wednesday, September 11, 2019 2:58 PM
  • Hi minhalraffat,

    There is a tutorial you could refer to.

    Check Copy Files Using File System Task in SSIS.

    If you have further questions, please post your screenshots.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, September 12, 2019 2:26 AM
  • Hi minhalraffat,

    There is a tutorial you could refer to.

    Check Copy Files Using File System Task in SSIS.

    If you have further questions, please post your screenshots.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hi Zoe,

    I am not looking to copy a file by using file system task. I want to add another worksheet in my excel files which are created dynamically and each file has 2 worksheets in it. I just need the 3rd worksheet to be created with my cover sheet init. I am able to create a blank cover sheet if I add another sql task.

    Below are the screen shots of my current package.



    Thanks,

    Minhal


    • Edited by minhalraffat Thursday, September 12, 2019 3:20 PM
    Thursday, September 12, 2019 3:19 PM
  • Hi minhalraffat,

    Zoe already provided you a link on how to implement it

    "...You could use Execute SQL Task with excel connection manager and use CREATE TABLE statement as shown in the following link. In your case you don't need dynamic code as your sheet is fixed.

    Please refer to Exporting SQLServer data to multiple sheets of Excel file.

    …"

    Thursday, September 12, 2019 3:25 PM
  • Hi minhalraffat,

    Zoe already provided you a link on how to implement it

    "...You could use Execute SQL Task with excel connection manager and use CREATE TABLE statement as shown in the following link. In your case you don't need dynamic code as your sheet is fixed.

    Please refer to Exporting SQLServer data to multiple sheets of Excel file.

    …"

    Hi Yitzhak,

    I did follow the link provided and got the following results, I am posting the screenshots for it.

    But I am still not able to get the 3rd sheet created for my template sheet. I am creating 2 other sheets in my file and the file and the 2 other fields are dynamically created. I have added another sql task as you said to create the 3rd worksheet but it is not working. Can you please help.

    Thanks.

    Minhal

    Friday, September 13, 2019 4:49 PM
  • Hi minhalraffat,

    Why you use two Execute SQL Tasks in the Foreach Loop Container?

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 16, 2019 6:44 AM
  • Hi minhalraffat,

    Why you use two Execute SQL Tasks in the Foreach Loop Container?

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hey Zoe,

    Actually I am using 3 sql task in my foreach loop. Each of the sql task creates a new worksheet in the excel file and the other 2 worksheets are filled with data which is working fine. The 3rd worksheet is created but not taking the template for the worksheet.

    Thanks

    Monday, September 16, 2019 12:36 PM
  • Hi minhalraffat,

    You just need one Execute SQL Task as the tutorial said.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, September 17, 2019 2:39 AM
  • Hi minhalraffat,

    You just need one Execute SQL Task as the tutorial said.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hey Zoe,

    I am creating 2 other worksheets dynamically within my excel file so for that I need the other 2 sql task as well. I hope you get my point.

    Thanks,

    Minhal

    Wednesday, September 18, 2019 2:49 PM
  • Hi minhalraffat,

    We are working on it.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, September 19, 2019 5:47 AM
  • Hi minhalraffat,

    We are working on it.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hey Zoe,

    I am waiting on it. Still unable to figure out a way to do it.

    Thanks,

    Minhal

    Monday, September 23, 2019 3:00 PM
  • Hi minhalraffat,

    Sorry, still cannot figure it out.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, September 24, 2019 5:37 AM
  • Hi minhalraffat,

    Sorry, still cannot figure it out.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hi Zoe,

    If you come up with a solution please let me know about it.

    Thanks,

    Minhal

    Tuesday, September 24, 2019 12:46 PM
  • okay

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, September 25, 2019 2:20 AM