Member Avatar for joshl_1995

Hello Community,
I was wondering if there is a way to make a program that will copy text from an excel spreatsheet and place it in another using visualbasic.net not vba?

Please Help

  • 4 Contributors
  • 6 Replies
  • 301 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

Brief overview of practical approaches and a minimal, copy-ready example.

Common choices

  • Microsoft Interop (requires Excel installed): easiest for small desktop utilities, preserves formatting and formulas when needed. pointed this out.
  • Open XML / third-party libraries (EPPlus, ClosedXML, Open XML SDK): read/write .xlsx without Excel installed — safer for server-side use.
  • OLE DB: works for simple table-like reads/writes but is awkward for ranges and modern .xlsx features.

Minimal Interop example (copies values from a source range to a destination workbook). Add a reference to "Microsoft Excel XX.X Object Library" and import the Interop namespace.

Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

Public Sub CopyValuesFromExcel(sourcePath As String, destPath As String, Optional sheetIndex As Integer = 1, Optional sourceRange As String = "A1:C10", Optional destAddress As String = "A1")
    Dim xl As Excel.Application = Nothing
    Dim srcWb As Excel.Workbook = Nothing
    Dim dstWb As Excel.Workbook = Nothing
    Dim srcWs As Excel.Worksheet = Nothing
    Dim dstWs As Excel.Worksheet = Nothing
    Dim rng As Excel.Range = Nothing

    Try
        xl = New Excel.Application()
        xl.Visible = False
        srcWb = xl.Workbooks.Open(sourcePath, ReadOnly:=True)
        dstWb = xl.Workbooks.Open(destPath)
        srcWs = CType(srcWb.Worksheets(sheetIndex), Excel.Worksheet)
        dstWs = CType(dstWb.Worksheets(sheetIndex), Excel.Worksheet)
        rng = srcWs.Range(sourceRange)
        Dim values = rng.Value2

        If values IsNot Nothing AndAlso TypeOf values Is System.Array Then
            Dim arr = CType(values, Object(,))
            dstWs.Range(destAddress).Resize(arr.GetLength(0), arr.GetLength(1)).Value2 = arr
        Else
            dstWs.Range(destAddress).Value2 = values
        End If

        dstWb.Save()
    Finally
        If srcWb IsNot Nothing Then srcWb.Close(False)
        If dstWb IsNot Nothing Then dstWb.Close(True)
        If xl IsNot Nothing Then xl.Quit()

        If rng IsNot Nothing Then Marshal.ReleaseComObject(rng)
        If srcWs IsNot Nothing Then Marshal.ReleaseComObject(srcWs)
        If dstWs IsNot Nothing Then Marshal.ReleaseComObject(dstWs)
        If srcWb IsNot Nothing Then Marshal.ReleaseComObject(srcWb)
        If dstWb IsNot Nothing Then Marshal.ReleaseComObject(dstWb)
        If xl IsNot Nothing Then Marshal.ReleaseComObject(xl)
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try
End Sub

Practical notes and troubleshooting

  • Always release COM objects and force GC to avoid orphaned EXCEL.EXE processes. Match the process bitness (x86/x64) with the Excel install.
  • Interop is not recommended for unattended server automation; use Open XML/EPPlus/ClosedXML instead.
  • Copying formulas vs values: the sample copies values; use Copy/PasteSpecial for formats or formulas.
  • has more complete examples for specific scenarios; 's high-level suggestion to "read then write" is the right workflow — read a range into an array, transform as needed, then write to the target workbook.

Recommended Answers

All 6 Replies

Member Avatar for Pgmer

you need to write a program to read excel file using .net and use the same application to write back into another excel.

Member Avatar for joshl_1995

I know that. I'm asking how?

Member Avatar for Pgmer

you mean you want the complete code? or just an idea?

Member Avatar for joshl_1995

Maby a bit of the code just to start me off

Member Avatar for G_Waddell

Hi,

Do you mean manipulate Excel without it being installed on the machine or do you just want a way to do it in .net as opposed to vba?

If you don't mind excel being installed on the machine, then you can use Interop. Here is a sample:

As for the other, I'm not 100% sure, I did do a project where I was trying to manipulate newer versions of excel as they are basically compressed XML files but I didn't get very far before we had to pull the plug.

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.