Member Avatar for Jing_1

Good Morning Everyone,

May i know how to extract all data from a excel file and copy the data all over to another excel file?

Example from Excel A, we have data

EmployeeID Name Department

 1111           John          MIS

I want to copy the data over to Excel B... may i know how to do that using VB.net?

Please assist me!

  • 3 Contributors
  • 3 Replies
  • 687 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by du_1

Dani AI

Generated

Brief practical summary and safe choices. There are several ways to move “all data” from Excel A to Excel B in VB.NET: Excel Interop (desktop automation), an OLE DB read into a DataTable, the OpenXML SDK (no Excel required), or a high‑level library such as EPPlus/ClosedXML/Spire. For desktop utilities Interop is simple (as hinted). For ASP.NET or any server-side code, avoid Interop — use OpenXML or a library like ClosedXML so you don’t depend on Excel being installed. ’s Spire sample is a good example of a library approach if you prefer a commercial/free component.

A concise, reliable pattern using ClosedXML (install the ClosedXML NuGet package) — this copies the used range from the first worksheet of the source workbook into a new workbook:

Imports ClosedXML.Excel

Sub CopySheetValues(sourcePath As String, destPath As String)
    Using src As New XLWorkbook(sourcePath)
        Dim srcWs = src.Worksheet(1)
        Using dst As New XLWorkbook()
            Dim dstWs = dst.Worksheets.Add(srcWs.Name)
            Dim used = srcWs.RangeUsed()
            If used IsNot Nothing Then
                used.CopyTo(dstWs.Cell(1, 1))    'copies values, formulas and formatting
            End If
            dst.SaveAs(destPath)
        End Using
    End Using
End Sub

Quick troubleshooting and tips. If you need values only, iterate cells and assign dst.Cell(r,c).Value = src.Cell(r,c).Value. OLE DB (ACE) is handy for simple tabular reads but watch for provider installation, HDR (header) and IMEX/type-mixing issues. Interop requires releasing COM objects and is unsupported on servers; otherwise it preserves everything (formats, charts, formulas). For an exact byte-for-byte copy, use System.IO.File.Copy instead of reading/writing cells.

Practical recommendation. For simple transfers use ClosedXML or EPPlus (fast, no Excel dependency). Use OLE DB/DataTable if you need SQL-like filters or fast bulk transforms. If the goal was simply to duplicate a workbook, File.Copy is the safest and fastest option.

Recommended Answers

All 3 Replies

Member Avatar for pop_cola

I'm not going to give you any code but a hint. From there you can start coding.

I'm assuming you know how to add reference on Microsoft Excel and Import Microsoft.Office.Excel.

-> Declare a variable as and Excel.Application
-> Declare workbookA and workbookB as Excel.Workbooks (data will be copied from workbookA to workbookB, workbook1 will have the original data and workbook2 is a blank workbook)
-> create new workbook for workbookB and Open workbookA
-> copy data from columnA to column C of workbookA, activate workbookB and paste data. Do until row of workbookA is empty.

Please google for more information. Hope this helps!

Member Avatar for Jing_1

hi... the problem is solved!

Member Avatar for du_1

hi.

Glad to see you already solved your problem.
Here I'd provide code snippet in VB.NET for copying worksheet in Excel, hope it can be helpful.(P.S. You have to add .NET Excel.dll as a reference in your project before using the below code)

Imports Spire.Xls

Namespace Copy_Worksheet

    Class Program

        Private Shared Sub Main(ByVal args() As String)
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("..\copy worksheets.xls")
            Dim worksheet As Worksheet = workbook.Worksheets(0)
            'add worksheets and name them
            workbook.Worksheets.Add("copied sheet1")
            workbook.Worksheets.Add("copied sheet2")
            'copy worksheet to the new added worksheets
            workbook.Worksheets(1).CopyFrom(workbook.Worksheets(0))
            workbook.Worksheets(2).CopyFrom(workbook.Worksheets(0))
            workbook.SaveToFile("..\copy worksheets.xls")
            System.Diagnostics.Process.Start("..\copy worksheets.xls")
        End Sub
    End Class
End Namespace
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.