Copy information from a Excel file to another Excel file using SSIS VB2010, What wrong with these code

Question

I am making an SSIS that will:

1. Copy data from SQL Server SQL to Excel file
2. Then from the Excel File into a another preexisting Excel file with some formulas

I did the first part --Copy from SQL to Excel1-- which works fine. Then I tried to copy from the Excel1 file to the other using script task in Visual Basic 2010.

I want to somebody to check this program and tell me what is wrong with it in trying to copy information from a Excel1 to a Excel2. When I executed I get the error DTS scrip Task Runtime Error and it does nothing.

Public Sub Main()
    Dim oExcel As Microsoft.Office.Interop.Excel.Application,
        oExel2 As Microsoft.Office.Interop.Excel.Application

    Dim Obook As Microsoft.Office.Interop.Excel.Workbook,
        Obook2 As Microsoft.Office.Interop.Excel.Workbook,
        Osheet As Microsoft.Office.Interop.Excel.Worksheet,
        Osheet2 As Microsoft.Office.Interop.Excel.Worksheet

    'Programing

    'From  Excel Spreadsheet

    oExcel = New Microsoft.Office.Interop.Excel.Application()
    oExcel.Visible = False
    Obook = New Microsoft.Office.Interop.Excel.Workbook("C:\Documents\AT1.xls")
    Osheet = DirectCast(Obook.Sheets("Sheet1"), Microsoft.Office.Interop.Excel.Worksheet)


    'To Excel Spreadsheet

    oExel2 = New Microsoft.Office.Interop.Excel.Application()
    oExel2.Visible = False
    Obook2 = New Microsoft.Office.Interop.Excel.Workbook("C:\Documents\A2.xls")
    Osheet2 = DirectCast(Obook2.Sheets("January"), Microsoft.Office.Interop.Excel.Worksheet)

    Osheet2.Range("B11", "R16").Value = Osheet.Range("A3", "Q8").Value

    'Close 

    Osheet = Nothing
    Obook.Close(False)
    oExcel.Quit()

    Osheet2 = Nothing
    Obook.Close(False)
    oExel2.Quit()

    Dts.TaskResult = ScriptResults.Success

 End Sub

Answer 1

You should not really use Excel Interop in SSIS as it implies that you must install Excel on the server and that is not generally recommended. However this will do the trick...

Public Sub Main()

    Dim oExcel As Microsoft.Office.Interop.Excel.Application

    Dim Obook As Microsoft.Office.Interop.Excel.Workbook, _
        Obook2 As Microsoft.Office.Interop.Excel.Workbook, _
        Osheet As Microsoft.Office.Interop.Excel.Worksheet, _
        Osheet2 As Microsoft.Office.Interop.Excel.Worksheet

    oExcel = New Microsoft.Office.Interop.Excel.Application()
    oExcel.SheetsInNewWorkbook = 1
    oExcel.DisplayAlerts = False
    oExcel.Visible = False

    Obook = oExcel.Workbooks.Open("C:\Documents\AT1.xls")
    Osheet = Obook.Sheets("Sheet1")

    Obook2 = oExcel.Workbooks.Add()
    Osheet2 = Obook2.Worksheets.Item(1)
    Osheet2.Name = "January"

    Osheet2.Range("B11", "R16").Value = Osheet.Range("A3", "Q8").Value

    Obook2.SaveAs("C:\Documents\A2.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal)

    Obook.Close()
    Obook2.Close()
    oExcel.Quit()

End Sub

It would not be my choice, I'd probably use OLEDB instead.

To simply overwrite an existing workbook then...

Public Sub Main(ByVal Arguments() As String)

    Dim oExcel As Microsoft.Office.Interop.Excel.Application

    Dim Obook As Microsoft.Office.Interop.Excel.Workbook, _
        Obook2 As Microsoft.Office.Interop.Excel.Workbook, _
        Osheet As Microsoft.Office.Interop.Excel.Worksheet, _
        Osheet2 As Microsoft.Office.Interop.Excel.Worksheet

    oExcel = New Microsoft.Office.Interop.Excel.Application()
    oExcel.SheetsInNewWorkbook = 1
    oExcel.DisplayAlerts = False
    oExcel.Visible = False

    Obook = oExcel.Workbooks.Open("C:\Documents\AT1.xls")
    Osheet = Obook.Sheets("Sheet1")

    Obook2 = oExcel.Workbooks.Open("C:\Documents\A2.xls")
    Osheet2 = Obook2.Sheets("January")

    Osheet2.Range("B11", "R16").Value = Osheet.Range("A3", "Q8").Value

    Obook2.Save()

    Obook.Close()
    Obook2.Close()

    oExcel.Quit()

End Sub