Getting data from Excel, when to close quit Excel?

[codedemon]

I am working on a program that generates serial#s for our machines, it will read and write data to an excel spreadsheet.

When the form loads:

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        GetSheetNames()
    End Sub

        Public Sub GetSheetNames()        
        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim totalWorkSheets As Excel.Worksheet
        Dim objWorkSheets As Excel.Worksheet
        Dim ExcelSheetName As String = ""


        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open("C:\VBNET\master-serial-list.xlsx")
        ComboBox1.Items.Clear()
        ' this code gets the names off all the worksheets 
        For Each totalWorkSheets In objWorkBook.Worksheets
            'ExcelSheetName += totalWorkSheets.Name
            ComboBox1.Items.Add(totalWorkSheets.Name)
        Next totalWorkSheets
        objExcel.Quit()


    End Sub

I notice that the Excel sheet doesnt quit until I close the application. If I want to use the same Excel sheet in another Sub, it opens another instance of Excel.

Example: (I inserted a Msgbox just for testing)

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


        Public Sub GetHistory()        Dim objExcel As Excel.Application
        Dim objWorkBook As Excel.Workbook
        Dim totalWorkSheets As Excel.Worksheet
        Dim objWorkSheets As Excel.Worksheet
        Dim ExcelSheetName As String = ""


        objExcel = CreateObject("Excel.Application")
        objWorkBook = objExcel.Workbooks.Open("C:\VBNET\master-serial-list.xlsx")
        MsgBox(ComboBox1.ValueMember)
        objExcel.Quit()
    End Sub

How can I quit the instance of the Excel sheet and reopen it later when I need to write to it? Should I leave it open until the application is closed? If so, how do I prevent multiple instances from opening?

 

[JohnH]

If you want to reuse the Excel application instance across methods you have to declare the variable outside those methods.

Office application does not quit after automation from Visual Studio .NET client