我有以下代碼!問題就是程序運行結束後EXCEL進程還沒有死,導致儲存文件不能打開,我查了一下,原因是在其中加入了一段EXCEL宏代碼所致,請各位幫我看一下,有沒有什麼方法可以強行殺死EXCEL進程??Private Sub Command2_Click()
Dim WorkPath, fname As String
Dim RangeStr, TitleMon As String
Dim savefilename As String
WorkPath = app.Path
Dim I As Integer, j As Integer
Dim TotalRows As Integer
Dim TotalCols As Integer
Dim count, qty As Integer
Dim qty1, qty2 As Single
fname = WorkPath & "\report\sst_packinglist.xls"
Set MyExcel = New excel.Application
CommonDialog1.CancelError = True
On Error GoTo Error_Handle
CommonDialog1.Flags = cdlOFNHideReadOnly
CommonDialog1.Filter = "All Files (*.*)|*.*|Excel Files" & _
"(*.xls)|*.xls|Txt Files (*.txt)|*.txt"
CommonDialog1.FilterIndex = 2
CommonDialog1.DialogTitle = "選擇要儲存的檔案名稱"
CommonDialog1.ShowSave
savefilename = CommonDialog1.FileName
If Len(Trim(savefilename)) <> 0 Then
If Dir(savefilename, vbDirectory) <> "" Then
Kill savefilename
End If
FileCopy fname, savefilename
Set MyBook = MyExcel.Workbooks.Open(savefilename)
Else
Exit Sub
End If
Set MySheet = MyBook.Worksheets("Sheet1")
Set MyRange = MySheet.Range("A1")
MyRange.Cells(2, 2) = Trim(Text1(6).Text)
MyRange.Cells(3, 2) = Trim(Text1(7).Text)
MyRange.Cells(4, 2) = "King Long Technology(SuZhou) Limited"
MyRange.Cells(5, 2) = "No.288,FengLiStreet,Industrial Park,SuZhou,China"
MyRange.Cells(6, 2) = Trim(Text1(0).Text)
MyRange.Cells(6, 4) = Trim(Text1(4).Text)
MyRange.Cells(7, 2) = Trim(Text1(1).Text)
MyRange.Cells(7, 4) = Trim(Text1(5).Text)
MyRange.Cells(8, 2) = Trim(Text1(2).Text)
MyRange.Cells(9, 2) = Trim(Text1(3).Text) TotalCols = FlexGridMesg.Cols
TotalRows = FlexGridMesg.Rows
count = Trim(FlexGridMesg.TextMatrix(TotalRows - 1, 0))
qty = 0
qty1 = 0
qty2 = 0
S = FlexGridMesg.TextMatrix(1, 0) & "/" & count
MyRange.Cells(1 + 10, 0 + 1) = S
For I = 1 To TotalRows - 1
For j = 0 To TotalCols - 1
If j = 0 Then
If (I <> 1) Then
If S <> FlexGridMesg.TextMatrix(I, j) & "/" & count Then
MyRange.Cells(I + 10, j + 1) = FlexGridMesg.TextMatrix(I, j) & "/" & count
S = FlexGridMesg.TextMatrix(I, j) & "/" & count
Else
MyRange.Cells(I + 10, j + 1) = ""
End If
End If
Else
MyRange.Cells(I + 10, j + 1) = FlexGridMesg.TextMatrix(I, j)
End If
If j = 4 Then
qty = qty + CInt(FlexGridMesg.TextMatrix(I, j)) '總數
End If
If j = 6 And Trim(FlexGridMesg.TextMatrix(I, j)) <> "" Then
qty1 = qty1 + CSng(FlexGridMesg.TextMatrix(I, j))
End If
If j = 7 And Trim(FlexGridMesg.TextMatrix(I, j)) <> "" Then
qty2 = qty2 + CSng(FlexGridMesg.TextMatrix(I, j))
End If
Next j
Next I MyRange.Cells(TotalRows + 10, 1) = "Total"
for i=1 to 9 '我發現導致進程不死的原因就是這個FOR循環,這個循環功能是實現在9個元 ‘格上劃一條線。
MyRange.Cells(TotalRows + 10, i).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
next i
MyRange.Cells(TotalRows + 10, 5) = qty
MyRange.Cells(TotalRows + 10, 6) = qty
MyRange.Cells(TotalRows + 10, 7) = Mid(qty1, 1, 5)
MyRange.Cells(TotalRows + 10, 8) = Mid(qty2, 1, 5)
MyRange.Cells(TotalRows + 14, 3) = "OQC:"
MyRange.Cells(TotalRows + 14, 3).HorizontalAlignment = xlRight
MyRange.Cells(TotalRows + 14, 4) = "_____"
MyRange.Cells(TotalRows + 14, 5) = "Supervisor:"
MyRange.Cells(TotalRows + 14, 5).HorizontalAlignment = xlRight
MyRange.Cells(TotalRows + 14, 6) = "_____"
MyRange.Cells(TotalRows + 14, 7) = "Prepare:"
MyRange.Cells(TotalRows + 14, 7).HorizontalAlignment = xlRight
MyRange.Cells(TotalRows + 14, 8) = "_____"
MyBook.Save
MyExcel.Quit
Set MyExcel = Nothing
Set MyBook = Nothing
Set MySheet = Nothing
MsgBox "儲存完畢!"
Exit Sub
Error_Handle:
Select Case Err
Case 32755
Exit Sub
Case Else
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
Screen.MousePointer = 0
Exit Sub
End Select
End Sub
Dim WorkPath, fname As String
Dim RangeStr, TitleMon As String
Dim savefilename As String
WorkPath = app.Path
Dim I As Integer, j As Integer
Dim TotalRows As Integer
Dim TotalCols As Integer
Dim count, qty As Integer
Dim qty1, qty2 As Single
fname = WorkPath & "\report\sst_packinglist.xls"
Set MyExcel = New excel.Application
CommonDialog1.CancelError = True
On Error GoTo Error_Handle
CommonDialog1.Flags = cdlOFNHideReadOnly
CommonDialog1.Filter = "All Files (*.*)|*.*|Excel Files" & _
"(*.xls)|*.xls|Txt Files (*.txt)|*.txt"
CommonDialog1.FilterIndex = 2
CommonDialog1.DialogTitle = "選擇要儲存的檔案名稱"
CommonDialog1.ShowSave
savefilename = CommonDialog1.FileName
If Len(Trim(savefilename)) <> 0 Then
If Dir(savefilename, vbDirectory) <> "" Then
Kill savefilename
End If
FileCopy fname, savefilename
Set MyBook = MyExcel.Workbooks.Open(savefilename)
Else
Exit Sub
End If
Set MySheet = MyBook.Worksheets("Sheet1")
Set MyRange = MySheet.Range("A1")
MyRange.Cells(2, 2) = Trim(Text1(6).Text)
MyRange.Cells(3, 2) = Trim(Text1(7).Text)
MyRange.Cells(4, 2) = "King Long Technology(SuZhou) Limited"
MyRange.Cells(5, 2) = "No.288,FengLiStreet,Industrial Park,SuZhou,China"
MyRange.Cells(6, 2) = Trim(Text1(0).Text)
MyRange.Cells(6, 4) = Trim(Text1(4).Text)
MyRange.Cells(7, 2) = Trim(Text1(1).Text)
MyRange.Cells(7, 4) = Trim(Text1(5).Text)
MyRange.Cells(8, 2) = Trim(Text1(2).Text)
MyRange.Cells(9, 2) = Trim(Text1(3).Text) TotalCols = FlexGridMesg.Cols
TotalRows = FlexGridMesg.Rows
count = Trim(FlexGridMesg.TextMatrix(TotalRows - 1, 0))
qty = 0
qty1 = 0
qty2 = 0
S = FlexGridMesg.TextMatrix(1, 0) & "/" & count
MyRange.Cells(1 + 10, 0 + 1) = S
For I = 1 To TotalRows - 1
For j = 0 To TotalCols - 1
If j = 0 Then
If (I <> 1) Then
If S <> FlexGridMesg.TextMatrix(I, j) & "/" & count Then
MyRange.Cells(I + 10, j + 1) = FlexGridMesg.TextMatrix(I, j) & "/" & count
S = FlexGridMesg.TextMatrix(I, j) & "/" & count
Else
MyRange.Cells(I + 10, j + 1) = ""
End If
End If
Else
MyRange.Cells(I + 10, j + 1) = FlexGridMesg.TextMatrix(I, j)
End If
If j = 4 Then
qty = qty + CInt(FlexGridMesg.TextMatrix(I, j)) '總數
End If
If j = 6 And Trim(FlexGridMesg.TextMatrix(I, j)) <> "" Then
qty1 = qty1 + CSng(FlexGridMesg.TextMatrix(I, j))
End If
If j = 7 And Trim(FlexGridMesg.TextMatrix(I, j)) <> "" Then
qty2 = qty2 + CSng(FlexGridMesg.TextMatrix(I, j))
End If
Next j
Next I MyRange.Cells(TotalRows + 10, 1) = "Total"
for i=1 to 9 '我發現導致進程不死的原因就是這個FOR循環,這個循環功能是實現在9個元 ‘格上劃一條線。
MyRange.Cells(TotalRows + 10, i).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
next i
MyRange.Cells(TotalRows + 10, 5) = qty
MyRange.Cells(TotalRows + 10, 6) = qty
MyRange.Cells(TotalRows + 10, 7) = Mid(qty1, 1, 5)
MyRange.Cells(TotalRows + 10, 8) = Mid(qty2, 1, 5)
MyRange.Cells(TotalRows + 14, 3) = "OQC:"
MyRange.Cells(TotalRows + 14, 3).HorizontalAlignment = xlRight
MyRange.Cells(TotalRows + 14, 4) = "_____"
MyRange.Cells(TotalRows + 14, 5) = "Supervisor:"
MyRange.Cells(TotalRows + 14, 5).HorizontalAlignment = xlRight
MyRange.Cells(TotalRows + 14, 6) = "_____"
MyRange.Cells(TotalRows + 14, 7) = "Prepare:"
MyRange.Cells(TotalRows + 14, 7).HorizontalAlignment = xlRight
MyRange.Cells(TotalRows + 14, 8) = "_____"
MyBook.Save
MyExcel.Quit
Set MyExcel = Nothing
Set MyBook = Nothing
Set MySheet = Nothing
MsgBox "儲存完畢!"
Exit Sub
Error_Handle:
Select Case Err
Case 32755
Exit Sub
Case Else
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
Screen.MousePointer = 0
Exit Sub
End Select
End Sub
解决方案 »
- 想创建一个带窗体的DLL文件,不知道怎么在窗体中回头调用里面的cls中过程?
- 请问高手:Function和Sub的区别?我自己定义的函数,用这个2个好象都可以!
- 关于word书签的问题,急急急!!!
- 帮忙看一下有什么错!
- VB与皮带秤解码,问题
- 不使用Api,请问如何使窗体的 X 按钮失效(变灰色)?
- 关于用SQL语句对数据操作的问题,急!!!
- 哪位大哥帮翻译一句话!非常感谢!!!!!!!!!!
- vb 英文版exif处理代码,中文环境下改写问题001
- 请问:是不是有一个API函数可以调用默认的发送邮件的程序,应该怎么写?谢谢。
- 大哥大姐: 请教如何用VB 把如下的保存为Txt 型式的数据导入SQL Server,分不多了,救命
- *********那位能够提供45M左右的ftp空间 使用2个月左右?500分相送**********
1
Set MyExcel = Nothing
Set MyBook = Nothing
Set MySheet = Nothing
似乎应该改变顺序为
Set MySheet = Nothing
Set MyBook = Nothing
Set MyExcel = Nothing
2
在错误处理程序后面把这三句复制一份
建议你在Error_Handle里面也加上
Set MyExcel = Nothing
Set MyBook = Nothing
Set MySheet = Nothing
the object Range (MyRange) must be closed and released.
MyRange.Cells(2, 2) = Trim(Text1(6).Text)
MyRange.Cells(3, 2) = Trim(Text1(7).Text)
MyRange.Cells(4, 2) = "King Long Technology(SuZhou) Limited"
......
呢?
MyRange對象隻是表中的一列(即A1列),那為什麼可以引用到MyRange.Cells(4, 2)......等單元去了呢?是不是這裡出了錯,然後跳到錯誤處理語句,而在錯誤處理語句中又沒有關閉三級對象並Nothing掉,因而發生了這種情況?依樓主所要達到的功能來看,你完全可以把VBA寫在EXCEL中,這樣要比在EXCEL外完成功能快不知多少倍,穩定性也好,而且在EXCEL中寫VBA也不知方便多少倍,就象在VB6中寫程序一樣(還有語法提示,還可以隨時查VBA幫助,如果這些都不能讓你找到你要的功能,你還可以使用宏錄制的功能,當你想要某個功能而又不知語法時,你可以隨便開一個EXCEL然後開始宏錄制,然後按你想要的功能按步聚在EXCEL中完成,再把這些錄下的宏語句拷進你的VBA中再適當修改就OK了)。
set MyRange=nothing
画线那个循环可改为:
with range(MyRange.offset(TotalRows + 10, 0),MyRange.offset(TotalRows + 10, i).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
end with
MyBook.close false'还要关闭这个
Set MyExcel = Nothing
Set MyBook = Nothing
Set MySheet = Nothing
Dim Ex As Object 'Excel应用对象
Dim newbook As Workbook 'Excel工作簿 newbook.Close False
Set newbook = Nothing
Ex.Quit上面所说的对象你试试
没有的话close Excel