Link to home
Start Free TrialLog in
Avatar of fmichail
fmichailFlag for Canada

asked on

copying an excel worksheet to an existing xls template file

I have a code which populates an excel worksheet, and saves it as (using saveas method of the worksheet) to a a new file. what I want now is how to save the constructed worksheet to an existing excel template (xls). I am using vb.net, and here is my code. I ppreciat your help. Thanks
           
            Dim excel As New Microsoft.Office.Interop.Excel.Application


            Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Add
            Dim ws As Microsoft.Office.Interop.Excel.Worksheet
            ws = CType(wb.Worksheets.Item(1), Worksheet)
            sFileName = txtFeedFile.Text & ".xls"

            'DS is a dataset containing the table that I want to export to excel
            dtMLI = DS.Tables(0)

            'Populate Excel spreadsheet
            Dim dc As System.Data.DataColumn
            Dim dr As System.Data.DataRow
            Dim colIndex As Integer = 0
            Dim rowIndex As Integer = 0

            For Each dc In dtMLI.Columns
                colIndex = colIndex + 1
                ws.Cells(1, colIndex) = dc.ColumnName.ToString
            Next
            For Each dr In dtMLI.Rows
                rowIndex = rowIndex + 1
                colIndex = 0

                For Each dc In dtMLI.Columns
                    colIndex = colIndex + 1
                    ws.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName).ToString
                Next
            Next

            'Save excel MLI file
            ws.SaveAs(sLocation & "MLI_DEPOSIT_" & sFileName)
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Instead of

Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Add

use the Open method

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open.aspx
Change
Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Add

Open in new window

To
Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(FilePathAndName)

Open in new window


And
ws.SaveAs(sLocation & "MLI_DEPOSIT_" & sFileName) 

Open in new window

To
ws.Save

Open in new window

Avatar of fmichail

ASKER

Dear Macroshadow

There is no public method "Save" for the worksheet. What to do?
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the points...