June 5, 2009 at 6:19 am
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
June 5, 2009 at 7:36 am
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'.
June 5, 2009 at 7:59 am
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.
June 5, 2009 at 8:10 am
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?
June 5, 2009 at 9:17 am
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'
June 8, 2009 at 8:17 am
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)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply