VB Forms Application and MS Excel Record Saving

Question

Hi,

I am using following code to add /save record to Excel sheet.

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
        Dim xlsWorkBook As Microsoft.Office.Interop.Excel.Workbook  
        Dim xlsWorkSheet As Microsoft.Office.Interop.Excel.Worksheet  
        Dim xls As New Microsoft.Office.Interop.Excel.Application  
        Dim resourcesFolder = IO.Path.GetFullPath("D:\")  
        Dim fileName = "Report.xlsx"  
  
        xlsWorkBook = xls.Workbooks.Open(resourcesFolder & fileName)  
        xlsWorkSheet = xlsWorkBook.Sheets("Sheet1")  
  
        xlsWorkSheet.Cells(2, 2) = RichTextBox1.Text  
        xlsWorkSheet.Cells(3, 2) = RichTextBox2.Text  
        xlsWorkSheet.Cells(4, 2) = RichTextBox3.Text  
        xlsWorkBook.Close()  
        xls.Quit()  
            End Sub  

Is it possible to save data without prompting:

Ref: export-vb-form-data-to-excel

Answer 1

Add :

xlsWorkBook.Save()

before

xlsWorkBook.Close()

Answer 2

To save without prompting, see the highlighted line.

Full source see WriteFromDataGridView (not important where the data comes from)

Option Strict On  
Imports Excel = Microsoft.Office.Interop.Excel  
Imports Microsoft.Office  
Imports System.Runtime.InteropServices  
Module OpenWorkSheets3  
    Public Sub WriteFromDataGridView(ByVal OpenFileName As String, ByVal SheetName As String, ByVal dt As DataTable)  
  
        If IO.File.Exists(OpenFileName) Then  
  
            Dim Proceed As Boolean = False  
  
            Dim xlApp As Excel.Application = Nothing  
            Dim xlWorkBooks As Excel.Workbooks = Nothing  
            Dim xlWorkBook As Excel.Workbook = Nothing  
            Dim xlWorkSheet As Excel.Worksheet = Nothing  
            Dim xlWorkSheets As Excel.Sheets = Nothing  
            Dim xlCells As Excel.Range = Nothing  
  
            xlApp = New Excel.Application  
            xlApp.DisplayAlerts = False  
            xlWorkBooks = xlApp.Workbooks  
            xlWorkBook = xlWorkBooks.Open(OpenFileName)  
  
            xlApp.Visible = False  
  
            xlWorkSheets = xlWorkBook.Sheets  
  
            For x As Integer = 1 To xlWorkSheets.Count  
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)  
  
                If xlWorkSheet.Name = SheetName Then  
                    Proceed = True  
                    Exit For  
                End If  
  
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)  
                xlWorkSheet = Nothing  
  
            Next  
            If Proceed Then  
  
                For rowIndex As Integer = 0 To dt.Rows.Count - 1  
                    Dim workingValues() As Object  
  
                    Try  
                        workingValues = {dt.Rows(rowIndex).Item(0), dt.Rows(rowIndex).Item(1), dt.Rows(rowIndex).Item(2)}  
  
                        xlCells = xlWorkSheet.Range(String.Format("{0}{1}:{2}{3}", (rowIndex + 1).ExcelColumnName, 1, "D", rowIndex + 1))  
                        Console.WriteLine(xlCells.Address)  
  
                        xlCells.Value = workingValues  
                        Release(xlCells)  
  
                    Catch ex As Exception  
                        Console.WriteLine(ex.Message)  
                    End Try  
  
  
  
                Next  
  
                Console.WriteLine("Done")  
            Else  
                MessageBox.Show(SheetName & " not found.")  
            End If  
  
  
            xlWorkSheet.SaveAs(OpenFileName)  
  
            xlWorkBook.Close()  
            xlApp.UserControl = True  
            xlApp.Quit()  
  
            Release(xlCells)  
            Release(xlWorkSheets)  
            Release(xlWorkSheet)  
            Release(xlWorkBook)  
            Release(xlWorkBooks)  
            Release(xlApp)  
        Else  
            MessageBox.Show("'" & OpenFileName & "' not located. Try one of the write examples first.")  
        End If  
    End Sub  
    Private Sub Release(ByVal sender As Object)  
        Try  
            If sender IsNot Nothing Then  
                Marshal.ReleaseComObject(sender)  
                sender = Nothing  
            End If  
        Catch ex As Exception  
            sender = Nothing  
        End Try  
    End Sub  
End Module  
  

Answer 3

Thanks for reply.
In my sample, I did included as :
xls.DisplayAlerts = False
There was no prompts but output wasn't saved either.

I am working with very basic scenario and form looks like:

Excel sheet looks like following:

would like to save from vb form to excel and working with following code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
        Dim xlsWorkBook As Microsoft.Office.Interop.Excel.Workbook  
        Dim xlsWorkSheet As Microsoft.Office.Interop.Excel.Worksheet  
        Dim xls As New Microsoft.Office.Interop.Excel.Application  
        'xls.DisplayAlerts = False 'This will supress the file save dialoge  
        Dim resourcesFolder = IO.Path.GetFullPath("D:\")  
        Dim fileName = "Report.xlsx"  
        xlsWorkBook = xls.Workbooks.Open(resourcesFolder & fileName)  
        xlsWorkSheet = xlsWorkBook.Sheets("Sheet1")  
  
        xlsWorkSheet.Cells(2, 2) = RichTextBox1.Text  
        xlsWorkSheet.Cells(3, 2) = RichTextBox2.Text  
        xlsWorkSheet.Cells(4, 2) = RichTextBox3.Text  
        xlsWorkBook.Close()  
        xls.Quit()  
        MsgBox("file saved to " & resourcesFolder)  
    End Sub  

Answer 4

If open to using a NuGet free Excel package. Only downside the library only works for .xlsx, not .xls

Imports System.IO
Imports SpreadsheetLight

''' <summary>
''' 
''' Requires NuGet package SpreadsheetLight, free to use
''' 
''' For .NET Framework          Install-Package SpreadsheetLight -Version 3.5.0
''' For .NET Core Framework     Install-Package SpreadsheetLight.Core -Version 3.4.0
''' </summary>
''' <remarks>
''' SpreadSheetLight home page which has a help file
''' https://spreadsheetlight.com/
''' </remarks>
Public Class ExcelOperations2
    ''' <summary>
    ''' Writes three values to the default worksheet
    ''' </summary>
    ''' <param name="fileName">Existing file</param>
    ''' <param name="value1">first value to write</param>
    ''' <param name="value2">second value to write</param>
    ''' <param name="value3">three value to write</param>
    Public Shared Sub SimpleWrite(fileName As String, value1 As String, value2 As String, value3 As String)

        If Not File.Exists(fileName) Then
            Throw New FileNotFoundException($"Dude failed to find {fileName}")
        End If

        If Not String.IsNullOrWhiteSpace(value1) AndAlso Not String.IsNullOrWhiteSpace(value2) AndAlso Not String.IsNullOrWhiteSpace(value3) Then

            Try

                Using doc As New SLDocument(fileName)
                    '
                    ' SLConvert.ToColumnName(1) converts 1 to A, if you want B use 2 etc.
                    ' So value1 is written to A2, value2 to A3 etc.
                    '
                    doc.SetCellValue($"{SLConvert.ToColumnName(1)}2", value1)
                    doc.SetCellValue($"{SLConvert.ToColumnName(1)}3", value2)
                    doc.SetCellValue($"{SLConvert.ToColumnName(1)}4", value3)

                    doc.Save()

                End Using
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        Else
            Console.WriteLine("Missing one or more values")
        End If

    End Sub
    Public Shared Sub SimpleWrite(fileName As String, workSheetName As String, value1 As String, value2 As String, value3 As String)

        If Not File.Exists(fileName) Then
            Throw New FileNotFoundException($"Dude failed to find {fileName}")
        End If

        If Not String.IsNullOrWhiteSpace(value1) AndAlso Not String.IsNullOrWhiteSpace(value2) AndAlso Not String.IsNullOrWhiteSpace(value3) Then

            Try

                Using doc As New SLDocument(fileName)

                    If SheetExists(doc, workSheetName) Then
                        doc.SelectWorksheet(workSheetName)
                    End If


                    doc.SetCellValue($"{SLConvert.ToColumnName(1)}2", value1)
                    doc.SetCellValue($"{SLConvert.ToColumnName(1)}3", value2)
                    doc.SetCellValue($"{SLConvert.ToColumnName(1)}4", value3)

                    doc.Save()

                End Using
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        Else
            Console.WriteLine("Missing one or more values")
        End If

    End Sub
    Public Shared Function SheetExists(doc As SLDocument, pSheetName As String) As Boolean
        Return doc.GetSheetNames(False).Any(Function(sheetName) sheetName.ToLower() = pSheetName.ToLower())
    End Function
End Class

Usage

ExcelOperations2.SimpleWrite("SimpleWriteExample.xlsx", "Karen", "Payne", "Wrote this")