Solved

copying an excel worksheet to an existing xls template file

Posted on 2014-02-06
5
522 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 27

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 500 total points
ID: 39842668
It would be

wb.Save
0
 
LVL 27

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

777 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