dcsimg
Results 1 to 9 of 9

Thread: Problem Closing Excel Interop App Properly

  1. #1

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    173

    Problem Closing Excel Interop App Properly

    I am using Microsoft.Office.Interop.Excel and Microsoft.Office.Interop.Excel to import data from a Excel spreadsheet (Office 365). This is new to me and I have been playing around with the code to get it working.

    I have managed to import the data and then display it with a MessageBox (for testing purposes). This data will eventually be imported into a bound DGV.

    The problem I am having to closing the Excel instance. No matter what I do Excel does not close. For every time I run the app an instance of Excel remains.

    I have tried:
    Code:
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRangeEyepieces)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheetEyepieces)
    
    xlAppEyepieces.Workbooks.Close()
    
    xlAppEyepieces.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBookEyepieces)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlAppEyepieces)
    xlAppEyepieces = Nothing

    My Code:
    Code:
    Private Sub MnuImportFromExcel_Click_1(sender As Object, e As EventArgs) Handles mnuImportFromExcel.Click
    
            Dim strCellValue As String = ""
    
            Dim strColumn1 As String = ""
            Dim strColumn2 As String = ""
            Dim strColumn3 As String = ""
            Dim strColumn4 As String = ""
    
            Dim strTestString As String = ""
            Dim strSheetName As String = ""
    
    
            Try
                Dim xlAppEyepieces As New Excel.Application
                Dim xlWorkBookEyepieces As Excel.Workbook
                Dim xlWorkSheetEyepieces As Excel.Worksheet
                Dim xlRangeEyepieces As Excel.Range
    
                ' Open Excel Spreadsheet.
                xlWorkBookEyepieces = xlAppEyepieces.Workbooks.Open("D:\Temp\AstroCompanion Backups\Copy of AstroCompanionData2.xlsx")
    
                ' Loop over all sheets.
                For i As Integer = 1 To xlWorkBookEyepieces.Sheets.Count
    
                    ' Get sheet.
                    xlWorkSheetEyepieces = CType(xlWorkBookEyepieces.Sheets(i), Worksheet)
    
    
                    ' Get range.
                    xlRangeEyepieces = xlWorkSheetEyepieces.UsedRange
    
                    ' Load all cells into 2d array.
                    Dim array(,) As Object = CType(xlRangeEyepieces.Value(XlRangeValueDataType.xlRangeValueDefault), Object(,))
    
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRangeEyepieces)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheetEyepieces)
    
                    ' Scan the cells.
                    If array IsNot Nothing Then
    
                        ' Get bounds of the array.
                        Dim NumberOfRows As Integer = array.GetUpperBound(0)  'Rows
                        Dim NumberOfColumns As Integer = array.GetUpperBound(1)  'Columns
    
                        Dim intTest As Integer = 1
                        Dim strTestExellValue As String = ""
    
                        ' Loop over all elements.
                        For j As Integer = 2 To NumberOfRows
    
                            For x As Integer = 1 To NumberOfColumns
    
                                Try
                                    strCellValue = array(j, x).ToString
    
                                    Select Case x
                                        Case 1
                                            strColumn1 = strCellValue
                                        Case 2
                                            strColumn2 = strCellValue
                                        Case 3
                                            strColumn3 = strCellValue
                                        Case 4
                                            strColumn4 = strCellValue
                                    End Select
    
    
                                Catch ex As Exception
                                    MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                                Finally
                                    'Nothing
                                End Try
    
    
                            Next
    
    
                            strTestString = strColumn1 & " ---> " & strColumn2 & " ---> " & strColumn3 & " ---> " & strColumn4
    
                            MessageBox.Show(strTestString, "File - Test", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
    
                            intTest = intTest + 1
    
                            If intTest = 4 Then
                                Exit For
                            End If
    
                        Next
    
    
    
                    End If
                Next
    
                xlAppEyepieces.Workbooks.Close()
    
                xlAppEyepieces.Quit()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBookEyepieces)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlAppEyepieces)
                xlAppEyepieces = Nothing
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
    
            End Try
    
        End Sub
    What am I missing?
    I can fix what stupid does, but, I cannot fix stupid

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,848

    Re: Problem Closing Excel Interop App Properly

    Try this after "Quit"

    Code:
                Marshal.FinalReleaseComObject(xlWorkSheet)
                Marshal.FinalReleaseComObject(xlWorkBook)
                Marshal.FinalReleaseComObject(xlApp)
                xlApp = Nothing
    U

  3. #3

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    173

    Re: Problem Closing Excel Interop App Properly

    Quote Originally Posted by wes4dbt View Post
    Try this after "Quit"

    Code:
                Marshal.FinalReleaseComObject(xlWorkSheet)
                Marshal.FinalReleaseComObject(xlWorkBook)
                Marshal.FinalReleaseComObject(xlApp)
                xlApp = Nothing
    U
    No change. Thanks

    I have been testing the code. I have noticed, that sometimes the Excel instance is closed. Other times, not. All Excel instances are closed when the application is closed. Your thoughts?
    I can fix what stupid does, but, I cannot fix stupid

  4. #4
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,448

    Re: Problem Closing Excel Interop App Properly

    Hi,

    try ending like this...

    Code:
    'your code...
       oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,848

    Re: Problem Closing Excel Interop App Properly

    I've also used this,

    Code:
                xlApp.Quit()
                GC.Collect()
                GC.WaitForPendingFinalizers()
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                Marshal.FinalReleaseComObject(xlWorkBook)
                Marshal.FinalReleaseComObject(xlApp)
                xlApp = Nothing
    But you will probably get people telling you there's no reason to call the GC. At the time it was the way I could get the Excel instance to close consistently.

  6. #6

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    173

    Re: Problem Closing Excel Interop App Properly

    Quote Originally Posted by ChrisE View Post
    Hi,

    try ending like this...

    Code:
    'your code...
       oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub

    regards
    Chris
    Thanks Chris. Tried it. Does not work.

    I thought maybe I made a mistake with the name in "Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")". So, I tried "Microsoft Excel (32 bit)" and "EXCEL.EXE".
    No go.
    I can fix what stupid does, but, I cannot fix stupid

  7. #7

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    173

    Re: Problem Closing Excel Interop App Properly

    Quote Originally Posted by wes4dbt View Post
    I've also used this,

    Code:
                xlApp.Quit()
                GC.Collect()
                GC.WaitForPendingFinalizers()
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                Marshal.FinalReleaseComObject(xlWorkBook)
                Marshal.FinalReleaseComObject(xlApp)
                xlApp = Nothing
    But you will probably get people telling you there's no reason to call the GC. At the time it was the way I could get the Excel instance to close consistently.
    Thanks. Tried it. Still does not work.
    I can fix what stupid does, but, I cannot fix stupid

  8. #8
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,448

    Re: Problem Closing Excel Interop App Properly

    Hi,

    here the complete sample, try it a look if Excel is still open.

    Code:
    Option Strict On
    
    Imports Excel
    
    Public Class Form1
        Public Enum Excel_Colors
            rgbAliceBlue = 16775408          'Alice Blue
            rgbAntiqueWhite = 14150650       'Antique White
            rgbAqua = 16776960               'Aqua
            rgbAquamarine = 13959039         'Aquamarine
            rgbAzure = 16777200              'Azure
            rgbBeige = 14480885              'Beige
            rgbBisque = 12903679             'Bisque
            rgbBlack = 0                     'Black
            rgbBlanchedAlmond = 13495295     'Blanched Almond
            rgbBlue = 16711680               'Blue
            rgbBlueViolet = 14822282         'Blue Violet
            rgbBrown = 2763429               'Brown
            rgbBurlyWood = 8894686           'Burly Wood
            rgbCadetBlue = 10526303          'Cadet Blue
            rgbChartreuse = 65407            'Chartreuse
            rgbCoral = 5275647               'Coral
            rgbCornflowerBlue = 15570276     'Cornflower Blue
            rgbCornsilk = 14481663           'Cornsilk
            rgbCrimson = 3937500             'Crimson
            rgbDarkBlue = 9109504            'Dark Blue
            rgbDarkCyan = 9145088            'Dark Cyan
            rgbDarkGoldenrod = 755384        'Dark Goldenrod
            rgbDarkGray = 11119017           'Dark Gray
            rgbDarkGreen = 25600             'Dark Green
            rgbDarkGrey = 11119017           'Dark Grey
            rgbDarkKhaki = 7059389           'Dark Khaki
            rgbDarkMagenta = 9109643         'Dark Magenta
            rgbDarkOliveGreen = 3107669      'Dark Olive Green
            rgbDarkOrange = 36095            'Dark Orange
            rgbDarkOrchid = 13382297         'Dark Orchid
            rgbDarkRed = 139                 'Dark Red
            rgbDarkSalmon = 8034025          'Dark Salmon
            rgbDarkSeaGreen = 9419919        'Dark Sea Green
            rgbDarkSlateBlue = 9125192       'Dark Slate Blue
            rgbDarkSlateGray = 5197615       'Dark Slate Gray
            rgbDarkSlateGrey = 5197615       'Dark Slate Grey
            rgbDarkTurquoise = 13749760      'Dark Turquoise
            rgbDarkViolet = 13828244         'Dark Violet
            rgbDeepPink = 9639167            'Deep Pink
            rgbDeepSkyBlue = 16760576        'Deep Sky Blue
            rgbDimGray = 6908265             'Dim Gray
            rgbDimGrey = 6908265             'Dim Grey
            rgbDodgerBlue = 16748574         'Dodger Blue
            rgbFireBrick = 2237106           'Fire Brick
            rgbFloralWhite = 15792895        'Floral White
            rgbForestGreen = 2263842         'Forest Green
            rgbFuchsia = 16711935            'Fuchsia
            rgbGainsboro = 14474460          'Gainsboro
            rgbGhostWhite = 16775416         'Ghost White
            rgbGold = 55295                  'Gold
            rgbGoldenrod = 2139610           'Goldenrod
            rgbGray = 8421504                'Gray
            rgbGreen = 32768                 'Green
            rgbGreenYellow = 3145645         'Green Yellow
            rgbGrey = 8421504                'Grey
            rgbHoneydew = 15794160           'Honeydew
            rgbHotPink = 11823615            'Hot Pink
            rgbIndianRed = 6053069           'Indian Red
            rgbIndigo = 8519755              'Indigo
            rgbIvory = 15794175              'Ivory
            rgbKhaki = 9234160               'Khaki
            rgbLavender = 16443110           'Lavender
            rgbLavenderBlush = 16118015      'Lavender Blush
            rgbLawnGreen = 64636             'Lawn Green
            rgbLemonChiffon = 13499135       'Lemon Chiffon
            rgbLightBlue = 15128749          'Light Blue
            rgbLightCoral = 8421616          'Light Coral
            rgbLightCyan = 9145088           'Light Cyan
            rgbLightGoldenrodYellow = 13826810   'LightGoldenrodYellow
            rgbLightGray = 13882323          'Light Gray
            rgbLightGreen = 9498256          'Light Green
            rgbLightGrey = 13882323          'Light Grey
            rgbLightPink = 12695295          'Light Pink
            rgbLightSalmon = 8036607         'Light Salmon
            rgbLightSeaGreen = 11186720      'Light Sea Green
            rgbLightSkyBlue = 16436871       'Light Sky Blue
            rgbLightSlateGray = 10061943     'Light Slate Gray
            rgbLightSteelBlue = 14599344     'Light Steel Blue
            rgbLightYellow = 14745599        'Light Yellow
            rgbLime = 65280                  'Lime
            rgbLimeGreen = 3329330           'Lime Green
            rgbLinen = 15134970              'Linen
            rgbMaroon = 128                  'Maroon
            rgbMediumAquamarine = 11206502   'Medium Aquamarine
            rgbMediumBlue = 13434880         'Medium Blue
            rgbMediumOrchid = 13850042       'Medium Orchid
            rgbMediumPurple = 14381203       'Medium Purple
            rgbMediumSeaGreen = 7451452      'Medium Sea Green
            rgbMediumSlateBlue = 15624315    'Medium Slate Blue
            rgbMediumSpringGreen = 10156544  'Medium Spring Green
            rgbMediumTurquoise = 13422920    'Medium Turquoise
            rgbMediumVioletRed = 8721863     'Medium Violet Red
            rgbMidnightBlue = 7346457        'Midnight Blue
            rgbMintCream = 16449525          'Mint Cream
            rgbMistyRose = 14804223          'Misty Rose
            rgbMoccasin = 11920639           'Moccasin
            rgbNavajoWhite = 11394815        'Navajo White
            rgbNavy = 8388608                'Navy
            rgbNavyBlue = 8388608            'Navy Blue
            rgbOldLace = 15136253            'Old Lace
            rgbOlive = 32896                 'Olive
            rgbOliveDrab = 2330219           'Olive Drab
            rgbOrange = 42495                'Orange
            rgbOrangeRed = 17919             'Orange Red
            rgbOrchid = 14053594             'Orchid
            rgbPaleGoldenrod = 7071982       'Pale Goldenrod
            rgbPaleGreen = 10025880          'Pale Green
            rgbPaleTurquoise = 15658671      'Pale Turquoise
            rgbPaleVioletRed = 9662683       'Pale Violet Red
            rgbPapayaWhip = 14020607         'Papaya Whip
            rgbPeachPuff = 12180223          'Peach Puff
            rgbPeru = 4163021                'Peru
            rgbPink = 13353215               'Pink
            rgbPlum = 14524637               'Plum
            rgbPowderBlue = 15130800         'Powder Blue
            rgbPurple = 8388736              'Purple
            rgbRed = 255                     'Red
            rgbRosyBrown = 9408444           'Rosy Brown
            rgbRoyalBlue = 14772545          'Royal Blue
            rgbSalmon = 7504122              'Salmon
            rgbSandyBrown = 6333684          'Sandy Brown
            rgbSeaGreen = 5737262            'Sea Green
            rgbSeashell = 15660543           'Seashell
            rgbSienna = 2970272              'Sienna
            rgbSilver = 12632256             'Silver
            rgbSkyBlue = 15453831            'Sky Blue
            rgbSlateBlue = 13458026          'Slate Blue
            rgbSlateGray = 9470064           'Slate Gray
            rgbSnow = 16448255               'Snow
            rgbSpringGreen = 8388352         'Spring Green
            rgbSteelBlue = 11829830          'Steel Blue
            rgbTan = 9221330                 'Tan
            rgbTeal = 8421376                'Teal
            rgbThistle = 14204888            'Thistle
            rgbTomato = 4678655              'Tomato
            rgbTurquoise = 13688896          'Turquoise
            rgbViolet = 15631086             'Violet
            rgbWheat = 11788021              'Wheat
            rgbWhite = 16777215              'White
            rgbWhiteSmoke = 16119285         'White Smoke
            rgbYellow = 65535                'Yellow
            rgbYellowGreen = 3329434         'Yellow Green
        End Enum
    
       
       
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim a As Integer
            Dim Exceldaten(9, 2) As String
            For a = 0 To 9
                Exceldaten(a, 0) = Chr(65 + a)
                Exceldaten(a, 1) = Chr(48 + a)
                Exceldaten(a, 2) = "Column 3 - Row " & (a + 2)
            Next
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oXL = CType(CreateObject("Excel.Application"), Excel.Application)
            oWB = oXL.Workbooks.Add
            oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)
            'oXL.Visible = True
            oSheet.Range("A1").Value = "vbForums.com"
            oSheet.Range("B1").Value = "my Number"
            oSheet.Range("C1").Value = "Sssssss"
            'Excelformat
            oSheet.Range("A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("E1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("A1").ColumnWidth = 10
            oSheet.Range("B1").ColumnWidth = 10
            oSheet.Range("C1").ColumnWidth = 15.5
            With oSheet.Range("A1", "C1")
                .Font.Bold = True
                .Font.Color = Excel_Colors.rgbOrangeRed
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            End With
    
            'add something to calculate
            oSheet.Range("D2").Value = 10.25
            oSheet.Range("D3").Value = 101.25
            oSheet.Range("D4").Value = 123330.65
            oSheet.Range("D5").Value = 1255.25
          
            oSheet.Range("E6").Value = "20.10.2017 " & TimeOfDay
            oSheet.Range("E7").Value = String.Format("{0:dd.MM.yyyy HH:mm:ss}", Date.Now)
    
    
    
            'Dropdown Start ................
            'create a Dropdown , do you want to hide that column ?
            'Dim columnA As Excel.Range = CType(oSheet.Columns(2), Excel.Range)
            'columnA.Hidden = True
    
            'this will set the Dropdown Button in cell D4
            Dim range As Excel.Range = CType(oSheet.Cells(1, 4), Excel.Range)
    
    
            'what Range ? in this case B2:B70
            range.Validation.Add(Type:=Excel.XlDVType.xlValidateList, _
                AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _
                Operator:=Excel.XlFormatConditionOperator.xlBetween, _
                Formula1:="=$B$2:$B$70")
            'Dropdown End...............
    
    
            'Data to Excel 
            oSheet.Range("A2", "C9").Value = Exceldaten
            'Calulate values, 
            'in German = "=Summe(D2:D5)"
            'in Englisch probably = "=Sum(D2:D5)"
            oSheet.Range("D7").Value = "=Summe(D2:D5)"
            oSheet.Range("D7").NumberFormatLocal = "#.##0,00"
            oSheet.Range("D7").Interior.Color = Excel_Colors.rgbCadetBlue
    
            'Save Excel
            oSheet.SaveAs("C:\Exceldaten99.xls")
            oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub
    
      
    End Class
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    173

    Re: Problem Closing Excel Interop App Properly

    Thanks Chris will give it a try later.
    I can fix what stupid does, but, I cannot fix stupid

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width