Solved

copying an excel worksheet to an existing xls template file

Posted on 2014-02-06
5
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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 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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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