Solved

copying an excel worksheet to an existing xls template file

Posted on 2014-02-06
5
516 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
Comment Utility
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 26

Expert Comment

by:MacroShadow
Comment Utility
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
Comment Utility
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
Comment Utility
It would be

wb.Save
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Thanks for the points...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now