-
Feb 3rd, 2018, 02:09 PM
#1
Thread Starter
Addicted Member
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
-
Feb 3rd, 2018, 03:24 PM
#2
Re: Problem Closing Excel Interop App Properly
Try this after "Quit"
Code:
Marshal.FinalReleaseComObject(xlWorkSheet)
Marshal.FinalReleaseComObject(xlWorkBook)
Marshal.FinalReleaseComObject(xlApp)
xlApp = Nothing
U
-
Feb 3rd, 2018, 04:35 PM
#3
Thread Starter
Addicted Member
Re: Problem Closing Excel Interop App Properly
Originally Posted by wes4dbt
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
-
Feb 3rd, 2018, 04:47 PM
#4
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.
-
Feb 4th, 2018, 10:14 AM
#5
Thread Starter
Addicted Member
Re: Problem Closing Excel Interop App Properly
Originally Posted by ChrisE
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
-
Feb 3rd, 2018, 04:49 PM
#6
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.
-
Feb 4th, 2018, 10:17 AM
#7
Thread Starter
Addicted Member
Re: Problem Closing Excel Interop App Properly
Originally Posted by wes4dbt
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
-
Feb 4th, 2018, 12:06 PM
#8
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.
-
Feb 4th, 2018, 12:18 PM
#9
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|