Friday, 7 September 2012

VB.NET - How to open, edit and save xlsx and xls files using VB.NET



Same step by step example on YouTube:

' Open, edit and save XLSX file
        Dim fileTest As String = "C:\Temp\ExcelTest\test.xlsx"
        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")
        oExcel.Workbooks.Open(fileTest)
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        oBook = oExcel.ActiveWorkbook
        oSheet = oExcel.Worksheets(1)

        ' lets modify a1 and a2
        oSheet.Range("A1").Value = "testing a1"
        oSheet.Range("A2").Value = "testing a2"

        ' save
        oExcel.DisplayAlerts = False
        oBook.SaveAs(fileTest, 51) ' 51 == xlsx
        oBook.Close()
        oBook = Nothing

'Open, edit and save XLS file
  Dim fileTest As String = "C:\Temp\ExcelTest\test.xls"
        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")
        oExcel.Workbooks.Open(fileTest)
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        oBook = oExcel.ActiveWorkbook
        oSheet = oExcel.Worksheets(1)

        ' modify a1 and a2
        oSheet.Range("A1").Value = "Testing a1"
        oSheet.Range("A2").Value = "Testing a2"

        'save
        oExcel.DisplayAlerts = False
        oBook.SaveAs(fileTest, 1) ' 1== xls
        oBook.Close()
        oBook = Nothing

Same step by step example on YouTube:
Posted by at

9 comments:

  1. misha22 September 2013 at 23:14

    You can also achieve this task by using this .NET Excel Component/Library, it generate good results and you don't have to do the coding, this tool will do everything for you.

    ReplyDelete
  2. Anonymous16 November 2013 at 08:46

    What References you have to enable in Visual Studio for this to work? I added in the COM Reference to Excel Objects, as well as Microsoft.Office.Interop.Excel, Microsoft.Office.Tools.Excel and Microsoft.Office.Tools.Excel.v4.0.Utlilties. I get an error that "Excel.Worksheet" and "Excel.Workbook" not defined with the code you supplied copied exactly.

    ReplyDelete
    Replies
    1. Anonymous2 February 2015 at 02:26

      Working fine
      Add Reference : Microsoft.Office.Interop.Excel

      Use: Dim oBook As Microsoft.Office.Interop.Excel.Workbook
      Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

      Delete
  3. Anonymous25 February 2014 at 18:42

    Use:

    Dim oBook As Object
    Dim oSheet As Object

    ReplyDelete
  4. Anonymous4 December 2014 at 01:58

    thank you so much ,very useful

    ReplyDelete
  5. Anonymous2 February 2015 at 02:25

    Working fine
    Add Reference : Microsoft.Office.Interop.Excel

    Use: Dim oBook As Microsoft.Office.Interop.Excel.Workbook
    Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

    ReplyDelete
  6. Unknown29 May 2015 at 12:28

    Warning !!
    close your instance of application with
    oExcel.Quit()

    ReplyDelete
  7. Anonymous20 August 2015 at 09:56

    Imports Microsoft.Office.Interop.Excel

    Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim oApp As New Microsoft.Office.Interop.Excel.Application()
    oApp.Visible = False
    oApp.UserControl = True

    Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

    Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture

    System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

    'abre el libro
    oApp.Workbooks.Open("c:\biota\biota.xlsx")
    'seleciona la pestaña
    oSheet = oApp.Worksheets(1)
    'edita las celdas
    oSheet.Range("e2").Value = 1233

    'guarda lo editado
    Dim oBook As Microsoft.Office.Interop.Excel.Workbook
    oBook = oApp.ActiveWorkbook

    oApp.DisplayAlerts = False
    oBook.SaveAs("c:\biota\biota", 51) ' 51 == xlsx
    oBook.Close()
    oBook = Nothing
    oApp.Quit()

    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI





    End Sub

    ReplyDelete

Subscribe to: Post Comments (Atom)