Excel工作簿，文件夹和图片文件未完全关闭/结束 [英] Excel workbook, folder and picture files not closing/ending completely
本文介绍了Excel工作簿，文件夹和图片文件未完全关闭/结束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在下面的这个项目是一个参考卡，它从Excel文件中拉出文本，并从相同的搜索文件夹中提取图片。然后通过一个接一个地调用该子进程循环该进程，直到该应用程序退出。参考卡应该通过研究文件和重复进程每10分钟更新一次。问题是我想要的代码打开文件，拉，然后关闭文件完全等待并重复。这样可以在下次更新之前对该文件进行编辑。相反，它表示它仍在使用中，这意味着只读。即使我关闭了应用程序和视觉工作室，它仍然在使用。使用 Marshal.ObjectRelease 不工作。代码启动Excel进程，通过代码释放不起作用。之后它循环遍历第二次并创建一个新的进程（现在2 Excel进程）该版本的工作原理，但只为新的进程而不是原始的，并继续每个循环。
$
导入系统
导入System.Text
导入Excel = Microsoft.Office.Interop。 Excel
公共类Form1
Dim appXL As Excel.Application
Dim wbXl As Excel.Workbook
Dim shXL As Excel.Worksheet
Dim FldPath As String
Dim PartID As String
Dim RefCard As String
Dim timeUpDate As Double
Private Sub Form1_Load（sender As System.Object，e As System。 EventArgs）处理MyBase.Load
'Dispaly Brembo Logo
picLogo.SizeMode = PictureBoxSizeMode.StretchImage
End Sub
Private Sub Wait（）
Threading.Thread.Sleep（600000）
ReferenceCardDataPull（）
End Sub
私有Async Sub ReferenceCardDataPull（）
'读取文件来源与零件号******************
PartID =（19.N111.10）
'启动Excel并获取Application对象。
appXL = CreateObject（Excel.Application）
appXL.Visible = False
'打开参考卡*************** ************************************************** ********************
FldPath =（\\HOMESHARE01\Public\Kaizens\Kaizen 44 - 缺少零件\\'参考卡\\按部件号填写参考卡&\& PartID）
如果System.IO.Directory.Exists（FldPath）然后
wbXl = appXL.Workbooks.Open（FldPath& \&.xlsm）
shXL = wbXl.Worksheets（Sheet1）
'Copys参考卡数据按单元格应用程序标签
lblCODE.Text = shXL.Cells（6，5）.Value
lblREV.Text = shXL.Cells（3，5）.Value
lblDate.Text = shXL.Cells（9，5）.Value
lblCustomer.Text = shXL.Cells（3，1）.Value
lblPart.Text = shXL.Cells（6，1）.Value
lblSpindleType.Text = shXL.Cells（9， 1）.Value
lblPai ntType.Text = shXL.Cells（12，1）.Value
lblDunnageType.Text = shXL.Cells（15，1）.Value
lblPartsLayer.Text = shXL.Cells（3，3）.Value
lblLayers.Text = shXL.Cells（6，3）.Value
lblTotalParts.Text = shXL.Cells（9，3）.Value
lblPackagingInstructs.Text = shXL.Cells（12， 3）.Value
'从指定的零件文件夹中拉出图片
如果System.IO.File.Exists（FldPath& \& PicSpindle& PartID& .JPG）然后
picSpindle.Image = Image.FromFile（FldPath&\&PicSpindle& PartID&.JPG）
picSpindle.SizeMode = PictureBoxSizeMode。 StretchImage
Else
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
End If
如果System.IO.File.Exists（FldPath&\& PicRotorTop& PartID&.JPG）然后
picRotorTop.Image = Image.FromFile（FldPath&\&PicRotorTop& PartID&.JPG）
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
如果
如果System.IO.File.Exists（FldPath&然后
picRotorBottom.Image = Image.FromFile（FldPath&\&PicRotorBottom&PartID&PictootBottom&PicRotorBottom& .JPG）
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
如果
如果System.IO.File.Exists（FldPath& \& PicDunnageFinal& PartID& .JPG）然后
picDunnageFinal.Image = Image.FromFile（FldPath&\&PicDunnageFinal& PartID&.JPG）
picDunnageFinal.SizeMode = PictureBoxSizeMode。 StretchImage
Else
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
End If
如果System.IO.File.Exists（FldPath&\& PicDunnageLayer& PartID&.JPG）然后
picDunnageLayer.Image = Image.FromFile（FldPath&\&PicDunnageLayer& PartID&.JPG）
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
End If
'关闭对象
shXL = Nothing
wbXl.Close（）
appXL.Quit（）
appXL = Nothing
Else
lblCODE.Text =（Error）
lblCODE.ForeC olor = Color.Red
lblREV.Text =（Error）
lblREV.ForeColor = Color.Red
lblDate.Text =（Error）
lblDate.ForeColor = color.Red
lblCustomer.Text =（Error）
lblCustomer.ForeColor = Color.Red
lblPart.Text =（Error）
lblPart.ForeColor =颜色。 Red
lblSpindleType.Text =（Error）
lblSpindleType.ForeColor = Color.Red
lblPaintType.Text =（Error）
lblPaintType.ForeColor = Color.Red
lblDunnageType.Text =（Error）
lblDunnageType.ForeColor = Color.Red
Lable49.Text =（Error）
Lable49.ForeColor = Color.Red
lblLayers.Text =（Error）
lblLayers.ForeColor = Color.Red
lblTotalParts.Text =（Error）
lblTotalParts.ForeColor = Color.Red
lblPackagingInstructs .Text =（Error）
lblPackagingInstructs.ForeColor = Color.Red
lblError.Visible = True
End If
timeUpDate = 599
tmrUpdate.Start（）
Application.DoEvents（）
等待Task.Run（Sub （）
Wait（）
End Sub）
ReferenceCardDataPull（）
End Sub
私有子Timer1_Tick（发件人作为对象，e作为EventArgs）句柄tmrUpdate.Tick
Dim hms = TimeSpan.FromSeconds（timeUpDate）
Dim m = hms.Minutes.ToString
Dim s = hms.Seconds.ToString
如果timeUpDate > 0然后
timeUpDate - = 1
lblTimer.Text =（m&：& s）
Else
tmrUpdate.Stop ）
lblTimer.Text =更新
如果
End Sub
结束类
/ pre>
使用更新代码 Marshal.objectrelease
导入系统
导入System.IO
导入System.Text
导入System.Runtime.InteropServices
导入Excel = Microsoft.Office.Interop.Excel
导入System.ComponentModel
公共类Form1
Dim appXL作为Excel.Application
'Dim wbXl作为Excel.Workbook * ***存档
'Dim shXL作为Excel.Worksheet ****存档
Dim wbXls作为Excel.Workbooks
Dim wbXl作为Excel.Workbook
Dim shXL作为Excel.Worksheet
Dim FldPath As String
Dim PartID As String
Dim RefCard As String
Dim timeUpDate As Double
Dim OpenFolder As Object = CreateObject（shell.application）
私有子Form1_Load（发件人As System.Object，e As System.EventArgs）处理MyBase.Load
'Dispaly Brembo徽标
picLogo.SizeMode = PictureBoxSizeMode。 StretchImage
ReferenceCardDataPull（）
End Sub
Private Sub Wait（）
Threading.Thread.Sleep（10000）
End Sub
私人Async Sub ReferenceCardDataPull（）
'准备加载
lblTimer.Text =更新
lblError.Visible = False
'读取文件来源与部件号** ****************
PartID =（19.N111.10）
'启动Excel并获取应用程序对象。
appXL = CreateObject（Excel.Application）
appXL.Visible = False
'打开参考卡*************** ************************************************** ********************
FldPath =（\\HOMESHARE01\Public\Kaizens\Kaizen 44 - 缺少零件\\'参考卡\\按部件号填写参考卡&\& PartID）
如果System.IO.Directory.Exists（FldPath）然后
如果System.IO.File。存在（FldPath&\& PartID&.xlsm）然后
'wbXl = appXL.Workbooks.Open（FldPath&\& PartID&.xlsm） ****存档
wbXls = appXL.Workbooks
wbXl = wbXls.Open（FldPath&\& PartID&.xlsm）
shXL = wbXl.Worksheets（Sheet1）
'Copys参考卡数据按单元格应用程序标签
lblCODE.Text = shXL.Cells（6，5）.Value
lbl REV.Text = shXL.Cells（3，5）.Value
lblDate.Text = shXL.Cells（9，5）.Value
lblCustomer.Text = shXL.Cells（3，1）.Value
lblPart.Text = shXL.Cells（6，1）.Value
lblSpindleType.Text = shXL.Cells（9，1）.Value
lblPaintType.Text = shXL.Cells（12， 1）.Value
lblDunnageType.Text = shXL.Cells（15，1）.Value
lblPartsLayer.Text = shXL.Cells（3，3）.Value
lblLayers.Text = shXL。单元格（6，3）.Value
lblTotalParts.Text = shXL.Cells（9，3）.Value
lblPackagingInstructs.Text = shXL.Cells（12，3）.Value
Else
lblCODE.Text =（Error）
lblREV.Text =（Error）
lblDate.Text =（Error）
lblCustomer.Text =（Error ）
lblPart.Text =（Error）
lblSpindleType.Text =（Error）
lblPaintType.Text =（Error）
lblDunnageType.Tex t =（Error）
Lable49.Text =（Error）
lblLayers.Text =（Error）
lblTotalParts.Text =（Error）
lblPackagingInstructs.Text =（Error）
lblError.Visible = True
'关闭对象****存档
'shXL =没有任何****存档
'wbXl.Close（）****存档
'appXL.Quit（）****存档
'appXL =没有任何****存档
如果
Else
'找不到文件错误
lblCODE.Text =（错误）
lblREV.Text =（错误）
lblDate.Text =（错误）
lblCustomer.Text =（错误）
lblPart.Text =（错误）
lblSpindleType.Text =（
lblPaintType.Text =（Error）
lblDunnageType.Text =（错误）
Lable49.Text =（错误）
lblLayers.Text =（Error）
lbl TotalParts.Text =（Error）
lblPackagingInstructs.Text =（Error）
lblError.Visible = True
如果
'从指定零件文件夹
如果System.IO.File.Exists（FldPath& \& PicSpindle& PartID& .JPG）然后
picSpindle.Image = Image.FromFile（FldPath&\&PicSpindle& PartID&.JPG）
picSpindle.SizeMode = PictureBoxSizeMode。 StretchImage
Else
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
End If
如果System.IO.File.Exists（FldPath&\& PicRotorTop& PartID&.JPG）然后
picRotorTop.Image = Image.FromFile（FldPath&\&PicRotorTop& PartID&.JPG）
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
如果
如果System.IO.File.Exists（FldPath&然后
picRotorBottom.Image = Image.FromFile（FldPath&\&PicRotorBottom&PartID&PictootBottom&PicRotorBottom& .JPG）
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchI mage
Else
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
End If
如果System.IO.File.Exists（FldPath& \& PicDunnageFinal& PartID& .JPG）然后
picDunnageFinal.Image = Image.FromFile（FldPath&\&PicDunnageFinal& PartID&.JPG）
picDunnageFinal.SizeMode = PictureBoxSizeMode。 StretchImage
Else
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
End If
如果System.IO.File.Exists（FldPath&\& PicDunnageLayer& PartID&.JPG）然后
picDunnageLayer.Image = Image.FromFile（FldPath&\&PicDunnageLayer& PartID&.JPG）
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
End If
'关闭对象
wbXl.Close（）
wbXls.Close（）
appXL.Quit（）
'发布对象
releaseObject（shXL）
releaseObject（wbXl）
releaseObject（wbXl）
releaseObject（wbXls）
releaseObject（ appXL）
timeUpDate = 9
tmrUpdate.Start（）
Application.DoEvents（）
等待Task.Run（Sub（）
Wait（）
End Sub）
ReferenceCardDataPull（）
End Sub
私有子Timer1_Tick（发件人作为对象，e作为EventArgs）处理tmrUpdate.Tick
Dim hms = TimeSpan.FromSeconds（timeUpDate）
Dim m = hms.Minutes.ToString
Dim s = hms.Seconds.ToString
如果timeUpDate> 0然后
timeUpDate - = 1
lblTimer.Text =（m&：& s）
Else
tmrUpdate.Stop
lblTimer.Text =准备更新
如果
End Sub
Private Sub releaseObject（ByVal obj As Object）
尝试
Dim intRel As Integer = 0
Do
intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject（obj）
循环while intRel> 0
'MsgBox（Final Released obj＃& intRel）
Catch ex As Exception
MsgBox（Error release object& ex.ToString）
obj = Nothing
最后
GC.Collect（）
结束尝试
End Sub
结束类
解决方案
.Net中与COM的工作需要释放com对象。不仅如此，在.Net中使用excel互操作（或任何其他COM对象）时，不能使用点，因为在点之间，临时对象是在场景后面创建的，需要被释放。
例如，你写道：
appXL.Workbooks.Open
您需要将其拆分为
作为Excel.Workbook
工作簿= appXL.WorkBooks
workbooks.Open
...
发布时间，您需要调用Marhsal.ReleaseComObject（工作簿）。
您必须为代码中的所有excel对象执行此操作。
VB.Net中使用excel的示例：
公共函数PrintExcel（sPath As String，iFrom As整数）As String实现IPrint.PrintExcel
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheets作为Excel.Sheets =没有
Dim xlWorkSheet作为Excel.Worksheet =没有
尝试
xlApp =新的Excel.Application
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open（sPath）
xlWorkSheets = xlWorkBook.Sheets
xlWorkSheet = xlWorkSheets（1）
'DO SOMETHING
xlWorkBook.Close ）
xlWorkBooks.Close（）
xlApp.Quit（）
Catch ex As Exception
最后
releaseObject（xlWorkSheet）
releaseObject（xlWorkSheets）
releaseObject（xlWorkBook）
releaseObject（xlWorkBooks）
releaseObject（xlApp）
结束尝试
返回s
结束函数
Private Sub releaseObject ByVal obj As Object）
尝试
如果obj IsNot Nothing然后
Marshal.ReleaseComObject（obj）
如果
捕获ex As Exception
最后
obj =没有
结束尝试
结束Sub
'----------------------
按照您的代码修复，不检查：
选项显示
导入系统
导入System.IO
导入系统。文本
导入Excel = Microsoft.Office.Interop.Excel
公共类Form1
Dim appXL As Excel.Application
Dim wbXls As Excel.Workbooks
Dim wbXl As Excel.Workbook
Dim shXLs As Excel.Sheets'FIX 1：Sheets而不是WorkSheets
Dim shXL As Excel.Worksheet
Dim FldPath As String
Dim PartID As String
Dim RefCard As String
Dim timeUpDate As Double
Private Sub Form1_Load（sender As System.Object，e As System.EventArgs）处理MyBase.Load
'Dispaly Brembo Logo
picLogo.SizeMode = PictureBoxSizeMode.StretchImage
ReferenceCardDataPull（）
End Sub
Private Sub Wait（）
线程.Thread.Sleep（600000）
End Sub
私有异步Sub ReferenceCardDataPull（）
'读取部件号的文件来源************ ******
PartID =（19.N111.10）
'启动Excel并获取Application对象。
appXL = CreateObject（Excel.Application）
appXL.Visible = False
'打开参考卡*************** ************************************************** ********************
FldPath =（\\HOMESHARE01\Public\Kaizens\Kaizen 44 - 缺少零件\\'参考卡\\按部件号填写参考卡&\& PartID）
如果System.IO.Directory.Exists（FldPath）然后
wbXls = appXL.Workbooks
wbXl = wbXls.Open（FldPath&\& PartID&.xlsm）
shXLs = wbXl.Worksheets
shXL = shXLs（Sheet1）
'通过单元格应用程序标签的Copys参考卡数据
lblCODE.Text = shXL.Cells（6，5）.Value
lblREV.Text = shXL.Cells（3，5）.Value
lblDate.Text = shXL.Cells（9，5）.Value
lblCustomer.Text = shXL.Cells（3，1）.Value
lblPart.Text = shXL.Cells（6，1）.Value
lbl主轴Type.Text = shXL.Cells（9，1）.Value
lblPaintType.Text = shXL.Cells（12，1）.Value
lblDunnageType.Text = shXL.Cells（15，1）.Value
lblPartsLayer.Text = shXL.Cells（3，3）.Value
lblLayers.Text = shXL.Cells（6，3）.Value
lblTotalParts.Text = shXL.Cells（9， 3）.Value
lblPackagingInstructs.Text = shXL.Cells（12，3）.Value
'从指定的零件文件夹中拉出图片
如果System.IO.File.Exists（ FldPath& \& PicSpindle& PartID& .JPG）然后
picSpindle.Image = Image.FromFile（FldPath&\&PicSpindle& PartID&.JPG）
picSpindle.SizeMode = PictureBoxSizeMode。 StretchImage
Else
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
End If
如果System.IO.File.Exists（FldPath&\& PicRotorTop& PartID&.JPG）然后
picRotorTop.Image = Image.FromFile（FldPath&\&PicRotorTop& PartID&.JPG）
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
End If
如果System.IO.File.Exists（FldPath&然后
picRotorBottom.Image = Image.FromFile（FldPath&\&PicRotorBottom&PartID&PictootBottom&PicRotorBottom& .JPG）
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
如果
如果System.IO.File.Exists（FldPath& \& PicDunnageFinal& PartID& .JPG）然后
picDunnageFinal.Image = Image.FromFile（FldPath&\&PicDunnageFinal& PartID&.JPG）
picDunnageFinal.SizeMode = PictureBoxSizeMode。 StretchImage
Else
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
End If
如果System.IO.File.Exists（FldPath&\& PicDunnageLayer& PartID&.JPG）然后
picDunnageLayer.Image = Image.FromFile（FldPath&\&PicDunnageLayer& PartID&.JPG）
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
End If
'关闭对象
'FIX 2：删除shXL = Nothing
wbXl.Close（）
wbXls.Close（）
appXL.Quit（）
'FIX 3：re移动appXL = Nothing
releaseObject（shXL）
releaseObject（shXLs）
releaseObject（wbXl）
releaseObject（wbXls）
releaseObject（appXL）
Else
lblCODE.Text =（Error）
lblCODE.ForeColor = Color.Red
lblREV.Text =（Error）
lblREV。 ForeColor = Color.Red
lblDate.Text =（Error）
lblDate.ForeColor = Color.Red
lblCustomer.Text =（Error）
lblCustomer.ForeColor = color.Red
lblPart.Text =（Error）
lblPart.ForeColor = Color.Red
lblSpindleType.Text =（Error）
lblSpindleType.ForeColor =颜色。 Red
lblPaintType.Text =（Error）
lblPaintType.ForeColor = Color.Red
lblDunnageType.Text =（Error）
lblDunnageType.ForeColor = Color.Red
Lable49.Text =（Error）
Lable49.ForeColor = Color.Red
lblLayers.Text =（Error）
lblLayers.ForeColor = Color.Red
lblTotalParts.Text =（Error）
lblTotalParts.ForeColor = Color.Red
lblPackagingInstructs.Text =（Error）
lblPackagingInstructs.ForeColor = Color.Red
lblError.Visible = True
End If
timeUpDate = 599
tmrUpdate.Start （）
Application.DoEvents（）
等待Task.Run（Sub（）
Wait（）
End Sub）
ReferenceCardDataPull（）
End Sub
Private Sub Timer1_Tick（发件人作为对象，e作为EventArgs）处理tmrUpdate.Tick
Dim hms = TimeSpan.FromSeconds（timeUpDate）
Dim m = hms。 Minutes.ToString
Dim s = hms.Seconds.ToString
如果timeUpDate> 0然后
timeUpDate - = 1
lblTimer.Text =（m&：& s）
Else
tmrUpdate.Stop ）
lblTimer.Text =更新
如果
End Sub
Private Sub releaseObject（ByVal obj As Object）
尝试
如果obj IsNot Nothing然后
Marshal.ReleaseComObject（obj）
如果
捕获ex作为异常
最后
obj = Nothing
结束尝试
End Sub
结束类
This project I am working on below is a reference card that pulls text from an excel file and pictures from the same searched folder. The process is then "looped" by calling the Subs one after another until the app is exited. The reference card is supposed to update every 10 minute by researching for the files and repeating the process. The issue is that I wanted the code to open the file, pull, and then close the file completely then wait and repeat. This way the file could be edited before the next update. Instead it says it is still in use, meaning read only. Even when I close the app and visual studios it still says still in use. Using Marshal.ObjectRelease isn't working. The code starts the Excel Process, goes through out the code and release does not work. After it loops through the 2nd time and creates a new process (Now 2 Excel Processes) The release works but only for the new process not the original and this continues for each loop through.
Option Explicit On
Imports System
Imports System.IO
Imports System.Text
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Dim appXL As Excel.Application
Dim wbXl As Excel.Workbook
Dim shXL As Excel.Worksheet
Dim FldPath As String
Dim PartID As String
Dim RefCard As String
Dim timeUpDate As Double
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'Dispaly Brembo Logo
picLogo.SizeMode = PictureBoxSizeMode.StretchImage
End Sub
Private Sub Wait()
Threading.Thread.Sleep(600000)
ReferenceCardDataPull()
End Sub
Private Async Sub ReferenceCardDataPull()
'Read File Source with part number ******************
PartID = ("19.N111.10")
' Start Excel and get Application object.
appXL = CreateObject("Excel.Application")
appXL.Visible = False
'Open Reference Card*************************************************************************************
FldPath = ("\\HOMESHARE01\Public\Kaizens\Kaizen 44 - Missing Parts\Reference Cards\Completed Reference Cards by Part Number" & "\" & PartID)
If System.IO.Directory.Exists(FldPath) Then
wbXl = appXL.Workbooks.Open(FldPath & "\" & PartID & ".xlsm")
shXL = wbXl.Worksheets("Sheet1")
' Copys Reference Card Data by Cell To App labels
lblCODE.Text = shXL.Cells(6, 5).Value
lblREV.Text = shXL.Cells(3, 5).Value
lblDate.Text = shXL.Cells(9, 5).Value
lblCustomer.Text = shXL.Cells(3, 1).Value
lblPart.Text = shXL.Cells(6, 1).Value
lblSpindleType.Text = shXL.Cells(9, 1).Value
lblPaintType.Text = shXL.Cells(12, 1).Value
lblDunnageType.Text = shXL.Cells(15, 1).Value
lblPartsLayer.Text = shXL.Cells(3, 3).Value
lblLayers.Text = shXL.Cells(6, 3).Value
lblTotalParts.Text = shXL.Cells(9, 3).Value
lblPackagingInstructs.Text = shXL.Cells(12, 3).Value
'Pulls pictures from designated part folder
If System.IO.File.Exists(FldPath & "\" & "PicSpindle" & PartID & ".JPG") Then
picSpindle.Image = Image.FromFile(FldPath & "\" & "PicSpindle" & PartID & ".JPG")
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
Else
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicRotorTop" & PartID & ".JPG") Then
picRotorTop.Image = Image.FromFile(FldPath & "\" & "PicRotorTop" & PartID & ".JPG")
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicRotorBottom" & PartID & ".JPG") Then
picRotorBottom.Image = Image.FromFile(FldPath & "\" & "PicRotorBottom" & PartID & ".JPG")
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicDunnageFinal" & PartID & ".JPG") Then
picDunnageFinal.Image = Image.FromFile(FldPath & "\" & "PicDunnageFinal" & PartID & ".JPG")
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicDunnageLayer" & PartID & ".JPG") Then
picDunnageLayer.Image = Image.FromFile(FldPath & "\" & "PicDunnageLayer" & PartID & ".JPG")
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
End If
' Close objects
shXL = Nothing
wbXl.Close()
appXL.Quit()
appXL = Nothing
Else
lblCODE.Text = ("Error")
lblCODE.ForeColor = Color.Red
lblREV.Text = ("Error")
lblREV.ForeColor = Color.Red
lblDate.Text = ("Error")
lblDate.ForeColor = Color.Red
lblCustomer.Text = ("Error")
lblCustomer.ForeColor = Color.Red
lblPart.Text = ("Error")
lblPart.ForeColor = Color.Red
lblSpindleType.Text = ("Error")
lblSpindleType.ForeColor = Color.Red
lblPaintType.Text = ("Error")
lblPaintType.ForeColor = Color.Red
lblDunnageType.Text = ("Error")
lblDunnageType.ForeColor = Color.Red
Lable49.Text = ("Error")
Lable49.ForeColor = Color.Red
lblLayers.Text = ("Error")
lblLayers.ForeColor = Color.Red
lblTotalParts.Text = ("Error")
lblTotalParts.ForeColor = Color.Red
lblPackagingInstructs.Text = ("Error")
lblPackagingInstructs.ForeColor = Color.Red
lblError.Visible = True
End If
timeUpDate = 599
tmrUpdate.Start()
Application.DoEvents()
Await Task.Run(Sub()
Wait()
End Sub)
ReferenceCardDataPull()
End Sub
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles tmrUpdate.Tick
Dim hms = TimeSpan.FromSeconds(timeUpDate)
Dim m = hms.Minutes.ToString
Dim s = hms.Seconds.ToString
If timeUpDate > 0 Then
timeUpDate -= 1
lblTimer.Text = (m & ":" & s)
Else
tmrUpdate.Stop()
lblTimer.Text = "Updating"
End If
End Sub
End Class
Updated Code using Marshal.objectrelease
Imports System
Imports System.IO
Imports System.Text
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.ComponentModel
Public Class Form1
Dim appXL As Excel.Application
'Dim wbXl As Excel.Workbook**** Archive
'Dim shXL As Excel.Worksheet**** Archive
Dim wbXls As Excel.Workbooks
Dim wbXl As Excel.Workbook
Dim shXL As Excel.Worksheet
Dim FldPath As String
Dim PartID As String
Dim RefCard As String
Dim timeUpDate As Double
Dim OpenFolder As Object = CreateObject("shell.application")
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'Dispaly Brembo Logo
picLogo.SizeMode = PictureBoxSizeMode.StretchImage
ReferenceCardDataPull()
End Sub
Private Sub Wait()
Threading.Thread.Sleep(10000)
End Sub
Private Async Sub ReferenceCardDataPull()
'Prepare For Load
lblTimer.Text = "Updating"
lblError.Visible = False
'Read File Source with part number ******************
PartID = ("19.N111.10")
' Start Excel and get Application object.
appXL = CreateObject("Excel.Application")
appXL.Visible = False
'Open Reference Card*************************************************************************************
FldPath = ("\\HOMESHARE01\Public\Kaizens\Kaizen 44 - Missing Parts\Reference Cards\Completed Reference Cards by Part Number" & "\" & PartID)
If System.IO.Directory.Exists(FldPath) Then
If System.IO.File.Exists(FldPath & "\" & PartID & ".xlsm") Then
'wbXl = appXL.Workbooks.Open(FldPath & "\" & PartID & ".xlsm")**** Archive
wbXls = appXL.Workbooks
wbXl = wbXls.Open(FldPath & "\" & PartID & ".xlsm")
shXL = wbXl.Worksheets("Sheet1")
' Copys Reference Card Data by Cell To App labels
lblCODE.Text = shXL.Cells(6, 5).Value
lblREV.Text = shXL.Cells(3, 5).Value
lblDate.Text = shXL.Cells(9, 5).Value
lblCustomer.Text = shXL.Cells(3, 1).Value
lblPart.Text = shXL.Cells(6, 1).Value
lblSpindleType.Text = shXL.Cells(9, 1).Value
lblPaintType.Text = shXL.Cells(12, 1).Value
lblDunnageType.Text = shXL.Cells(15, 1).Value
lblPartsLayer.Text = shXL.Cells(3, 3).Value
lblLayers.Text = shXL.Cells(6, 3).Value
lblTotalParts.Text = shXL.Cells(9, 3).Value
lblPackagingInstructs.Text = shXL.Cells(12, 3).Value
Else
lblCODE.Text = ("Error")
lblREV.Text = ("Error")
lblDate.Text = ("Error")
lblCustomer.Text = ("Error")
lblPart.Text = ("Error")
lblSpindleType.Text = ("Error")
lblPaintType.Text = ("Error")
lblDunnageType.Text = ("Error")
Lable49.Text = ("Error")
lblLayers.Text = ("Error")
lblTotalParts.Text = ("Error")
lblPackagingInstructs.Text = ("Error")
lblError.Visible = True
' Close objects**** Archive
' shXL = Nothing**** Archive
' wbXl.Close()**** Archive
'appXL.Quit()**** Archive
'appXL = Nothing**** Archive
End If
Else
'File not found Error
lblCODE.Text = ("Error")
lblREV.Text = ("Error")
lblDate.Text = ("Error")
lblCustomer.Text = ("Error")
lblPart.Text = ("Error")
lblSpindleType.Text = ("Error")
lblPaintType.Text = ("Error")
lblDunnageType.Text = ("Error")
Lable49.Text = ("Error")
lblLayers.Text = ("Error")
lblTotalParts.Text = ("Error")
lblPackagingInstructs.Text = ("Error")
lblError.Visible = True
End If
'Pulls pictures from designated part folder
If System.IO.File.Exists(FldPath & "\" & "PicSpindle" & PartID & ".JPG") Then
picSpindle.Image = Image.FromFile(FldPath & "\" & "PicSpindle" & PartID & ".JPG")
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
Else
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicRotorTop" & PartID & ".JPG") Then
picRotorTop.Image = Image.FromFile(FldPath & "\" & "PicRotorTop" & PartID & ".JPG")
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicRotorBottom" & PartID & ".JPG") Then
picRotorBottom.Image = Image.FromFile(FldPath & "\" & "PicRotorBottom" & PartID & ".JPG")
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicDunnageFinal" & PartID & ".JPG") Then
picDunnageFinal.Image = Image.FromFile(FldPath & "\" & "PicDunnageFinal" & PartID & ".JPG")
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicDunnageLayer" & PartID & ".JPG") Then
picDunnageLayer.Image = Image.FromFile(FldPath & "\" & "PicDunnageLayer" & PartID & ".JPG")
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
End If
' Close objects
wbXl.Close()
wbXls.Close()
appXL.Quit()
'Release Objects
releaseObject(shXL)
releaseObject(wbXl)
releaseObject(wbXl)
releaseObject(wbXls)
releaseObject(appXL)
timeUpDate = 9
tmrUpdate.Start()
Application.DoEvents()
Await Task.Run(Sub()
Wait()
End Sub)
ReferenceCardDataPull()
End Sub
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles tmrUpdate.Tick
Dim hms = TimeSpan.FromSeconds(timeUpDate)
Dim m = hms.Minutes.ToString
Dim s = hms.Seconds.ToString
If timeUpDate > 0 Then
timeUpDate -= 1
lblTimer.Text = (m & ":" & s)
Else
tmrUpdate.Stop()
lblTimer.Text = "Preparing Update"
End If
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
Dim intRel As Integer = 0
Do
intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Loop While intRel > 0
'MsgBox("Final Released obj # " & intRel)
Catch ex As Exception
MsgBox("Error releasing object" & ex.ToString)
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class
解决方案
Workink with COM in .Net requires to release com objects. More than that, you cannot use dots when working with excel interop (or any other COM object) in .Net since between the dots, temporarly objects are created behind the scene and need to be released. For example, you wrote:
appXL.Workbooks.Open
You need to split it to
Dim workbooks as Excel.Workbooks
workbooks = appXL.WorkBooks
workbooks.Open
...
When time to release, you need to call Marhsal.ReleaseComObject(workbooks).
You must do it for all the excel objects you have in the code. An example of the usage of excel in VB.Net:
Public Function PrintExcel(sPath As String, iFrom As Integer) As String Implements IPrint.PrintExcel
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Try
xlApp = New Excel.Application
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(sPath)
xlWorkSheets = xlWorkBook.Sheets
xlWorkSheet = xlWorkSheets(1)
' DO SOMETHING
xlWorkBook.Close()
xlWorkBooks.Close()
xlApp.Quit()
Catch ex As Exception
Finally
releaseObject(xlWorkSheet)
releaseObject(xlWorkSheets)
releaseObject(xlWorkBook)
releaseObject(xlWorkBooks)
releaseObject(xlApp)
End Try
Return s
End Function
Private Sub releaseObject(ByVal obj As Object)
Try
If obj IsNot Nothing Then
Marshal.ReleaseComObject(obj)
End If
Catch ex As Exception
Finally
obj = Nothing
End Try
End Sub
' ----------------------
Following your code with the fix, not checked:
Option Explicit On
Imports System
Imports System.IO
Imports System.Text
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Dim appXL As Excel.Application
Dim wbXls As Excel.Workbooks
Dim wbXl As Excel.Workbook
Dim shXLs As Excel.Sheets ' FIX 1: Sheets instead of WorkSheets
Dim shXL As Excel.Worksheet
Dim FldPath As String
Dim PartID As String
Dim RefCard As String
Dim timeUpDate As Double
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'Dispaly Brembo Logo
picLogo.SizeMode = PictureBoxSizeMode.StretchImage
ReferenceCardDataPull()
End Sub
Private Sub Wait()
Threading.Thread.Sleep(600000)
End Sub
Private Async Sub ReferenceCardDataPull()
'Read File Source with part number ******************
PartID = ("19.N111.10")
' Start Excel and get Application object.
appXL = CreateObject("Excel.Application")
appXL.Visible = False
'Open Reference Card*************************************************************************************
FldPath = ("\\HOMESHARE01\Public\Kaizens\Kaizen 44 - Missing Parts\Reference Cards\Completed Reference Cards by Part Number" & "\" & PartID)
If System.IO.Directory.Exists(FldPath) Then
wbXls = appXL.Workbooks
wbXl = wbXls.Open(FldPath & "\" & PartID & ".xlsm")
shXLs = wbXl.Worksheets
shXL = shXLs("Sheet1")
' Copys Reference Card Data by Cell To App labels
lblCODE.Text = shXL.Cells(6, 5).Value
lblREV.Text = shXL.Cells(3, 5).Value
lblDate.Text = shXL.Cells(9, 5).Value
lblCustomer.Text = shXL.Cells(3, 1).Value
lblPart.Text = shXL.Cells(6, 1).Value
lblSpindleType.Text = shXL.Cells(9, 1).Value
lblPaintType.Text = shXL.Cells(12, 1).Value
lblDunnageType.Text = shXL.Cells(15, 1).Value
lblPartsLayer.Text = shXL.Cells(3, 3).Value
lblLayers.Text = shXL.Cells(6, 3).Value
lblTotalParts.Text = shXL.Cells(9, 3).Value
lblPackagingInstructs.Text = shXL.Cells(12, 3).Value
'Pulls pictures from designated part folder
If System.IO.File.Exists(FldPath & "\" & "PicSpindle" & PartID & ".JPG") Then
picSpindle.Image = Image.FromFile(FldPath & "\" & "PicSpindle" & PartID & ".JPG")
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
Else
picSpindle.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicRotorTop" & PartID & ".JPG") Then
picRotorTop.Image = Image.FromFile(FldPath & "\" & "PicRotorTop" & PartID & ".JPG")
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorTop.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicRotorBottom" & PartID & ".JPG") Then
picRotorBottom.Image = Image.FromFile(FldPath & "\" & "PicRotorBottom" & PartID & ".JPG")
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
Else
picRotorBottom.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicDunnageFinal" & PartID & ".JPG") Then
picDunnageFinal.Image = Image.FromFile(FldPath & "\" & "PicDunnageFinal" & PartID & ".JPG")
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageFinal.SizeMode = PictureBoxSizeMode.StretchImage
End If
If System.IO.File.Exists(FldPath & "\" & "PicDunnageLayer" & PartID & ".JPG") Then
picDunnageLayer.Image = Image.FromFile(FldPath & "\" & "PicDunnageLayer" & PartID & ".JPG")
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
Else
picDunnageLayer.SizeMode = PictureBoxSizeMode.StretchImage
End If
' Close objects
' FIX 2: remove shXL = Nothing
wbXl.Close()
wbXls.Close()
appXL.Quit()
' FIX 3: remove appXL = Nothing
releaseObject(shXL)
releaseObject(shXLs)
releaseObject(wbXl)
releaseObject(wbXls)
releaseObject(appXL)
Else
lblCODE.Text = ("Error")
lblCODE.ForeColor = Color.Red
lblREV.Text = ("Error")
lblREV.ForeColor = Color.Red
lblDate.Text = ("Error")
lblDate.ForeColor = Color.Red
lblCustomer.Text = ("Error")
lblCustomer.ForeColor = Color.Red
lblPart.Text = ("Error")
lblPart.ForeColor = Color.Red
lblSpindleType.Text = ("Error")
lblSpindleType.ForeColor = Color.Red
lblPaintType.Text = ("Error")
lblPaintType.ForeColor = Color.Red
lblDunnageType.Text = ("Error")
lblDunnageType.ForeColor = Color.Red
Lable49.Text = ("Error")
Lable49.ForeColor = Color.Red
lblLayers.Text = ("Error")
lblLayers.ForeColor = Color.Red
lblTotalParts.Text = ("Error")
lblTotalParts.ForeColor = Color.Red
lblPackagingInstructs.Text = ("Error")
lblPackagingInstructs.ForeColor = Color.Red
lblError.Visible = True
End If
timeUpDate = 599
tmrUpdate.Start()
Application.DoEvents()
Await Task.Run(Sub()
Wait()
End Sub)
ReferenceCardDataPull()
End Sub
Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles tmrUpdate.Tick
Dim hms = TimeSpan.FromSeconds(timeUpDate)
Dim m = hms.Minutes.ToString
Dim s = hms.Seconds.ToString
If timeUpDate > 0 Then
timeUpDate -= 1
lblTimer.Text = (m & ":" & s)
Else
tmrUpdate.Stop()
lblTimer.Text = "Updating"
End If
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
If obj IsNot Nothing Then
Marshal.ReleaseComObject(obj)
End If
Catch ex As Exception
Finally
obj = Nothing
End Try
End Sub
End Class
这篇关于Excel工作簿，文件夹和图片文件未完全关闭/结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
