?
Solved

copying an excel worksheet to an existing xls template file

Posted on 2014-02-06
5
Medium Priority
?
539 Views
Last Modified: 2014-02-08
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)
0
Comment
Question by:fmichail
  • 2
  • 2
5 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39840497
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
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39840511
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

0
 

Author Comment

by:fmichail
ID: 39842113
Dear Macroshadow

There is no public method "Save" for the worksheet. What to do?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 39842668
It would be

wb.Save
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39844293
Thanks for the points...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question