Dynamic named excel worksheet

Question

Dynamic named excel worksheet

Washout

SSC Enthusiast

Points: 192
More actions
June 5, 2009 at 6:19 am

#138374

I am trying to write a foreach loop container
whereby for each iteration a new worksheet is created in my excelsheet based on the value in the record set.
So if my my recordset object has (apples,oranges,pears)
then create three worksheets apples oranges and pears.
is it possible? praying for yes

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

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

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

I'm not sure on this but you could try having the excel workbook defined as a linked server then running a bit of dynamic sql like 'select * into linkedservername.oranges from yourquery'.

villersk SSCrazy Points: 2007 More actions · Answer 2

you could also add a script task to your loop to create excel files.

This is a pretty random chunk of code, but it could help you get started.

Imports Microsoft.Office.Interop

Module Module1

Sub Main()

Dim xlsApp = New Excel.Application

Dim xlsBook = xlsApp.Workbooks.Open(Filename:="C:\Documents and Settings\villersk\Desktop\20090430tj.csv", UpdateLinks:=False, ReadOnly:=False)

xlsBook.SaveAs(Filename:="C:\Documents and Settings\villersk\Desktop\20090430tj.xls", FileFormat:=Excel.XlFileFormat.xlExcel2)

End Sub

End Module

you could have a blank file you open each time and save as a new file for each iteration of the loop.

Washout SSC Enthusiast Points: 192 More actions · Answer 3

been trying something similar but i cannot seem to configure the Microsoft.Office.Interop lib to work

Im getting Excel.Application is not defined.

ive installed the Office 2003 Primary Interop Assemblies redistributable package.

is there anything i need todo in visual studio?

villersk SSCrazy Points: 2007 More actions · Answer 4

haven't added references for scripts within ssis, but I guess that is what you would need to do.

In a normal VS project you would go to the 'references' folder in the solution explorer, right click and hit add reference.

For excel you need 'Microsoft.office.tools.excel'

Washout SSC Enthusiast Points: 192 More actions · Answer 5

Got it working for the best part.

the one thing i cant seem to figure out is that excel process stays running in memory.

so if i am looping through worksheet creation 20 times there will be 20 instances of excel process running.

The below is the snippet of code causing the issue.

if anyone can see why then it would be great.

Dim varStringFileName As String

Dim varCustNo As String

Dim varRep As String

Dim excel As New Excel.Application

Dim wb As Excel.Workbook

Dim copyfromWS As Microsoft.Office.Interop.Excel.Worksheet

varStringFileName = Dts.Variables("varfilename").Value.ToString

varCustNo = Dts.Variables("VarCustomerNo").Value.ToString

wb = excel.Workbooks.Open(varStringFileName)

Dim copyNewCustNo As Excel.Worksheet = DirectCast(wb.Worksheets.Add(), Excel.Worksheet)

copyfromWS.Name = varCustNo

wb.Save()

wb.Close()

excel.Quit()

Runtime.InteropServices.Marshal.ReleaseComObject(wb)

Runtime.InteropServices.Marshal.ReleaseComObject(excel)