fmichail
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.E xcel.Appli cation
Dim wb As Microsoft.Office.Interop.E xcel.Workb ook = excel.Workbooks.Add
Dim ws As Microsoft.Office.Interop.E xcel.Works heet
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)
Dim excel As New Microsoft.Office.Interop.E
Dim wb As Microsoft.Office.Interop.E
Dim ws As Microsoft.Office.Interop.E
ws = CType(wb.Worksheets.Item(1
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)
Change
And
Dim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Add
ToDim wb As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(FilePathAndName)
And
ws.SaveAs(sLocation & "MLI_DEPOSIT_" & sFileName)
Tows.Save
ASKER
Dear Macroshadow
There is no public method "Save" for the worksheet. What to do?
There is no public method "Save" for the worksheet. What to do?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the points...
Dim wb As Microsoft.Office.Interop.E
use the Open method
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open.aspx