时间戳

首页 > 解决方案 > 无法在后台杀死excel应用程序

excel - 无法在后台杀死excel应用程序

问题描述

我是编程新手。这是我做的第一个系统应用程序。一旦系统成功上传到存储库中，我正在努力关闭或终止 excel 应用程序。这是我的代码。我真的希望你能帮我解决它。先感谢您。

Private Sub bgw_DoWork(sender As Object, e As DoWorkEventArgs) 处理 bgw.DoWork

    srcXlApp = New Excel.Application
    dstXlApp = New Excel.Application

    Dim numToDo As Integer = CInt(e.Argument)
    Dim ComputerName As String
    Dim strMissingValues As String


    Try

        System.Runtime.InteropServices.Marshal.ReleaseComObject(srcXlWb)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(dstXlWb)

        isValidUploading = True
        strMissingValues = "Cannot upload there are missing fields: "

        bgwState = e

        strRemarks = txtRemarks.Text

        srcXlApp.DisplayAlerts = False
        srcXlWbs = srcXlApp.Workbooks
        srcXlWb = srcXlWbs.Open(srcFilePath)
        srcXlWrksht = srcXlWb.Worksheets(1)
        srcXlApp.Visible = False



        'Step 1 - open source
        bgw.ReportProgress(Convert.ToInt32((1 / numToDo) * 100))
        'System.Threading.Thread.Sleep(5000)
        ComputerName = System.Net.Dns.GetHostName
        'dstFilePath = "C:\Users\" & ComputerName & "\Desktop\Mazza\trunk\QA Task Monitoring_2020.xlsx"

        If ComputerName = "jonas" Then
            'MessageBox.Show(ComputerName)
            dstFilePath = "C:\Users\jonas.ONE-SOURCE\Desktop\Mazza\trunk\QA Task Monitoring_for_testing_only.xlsx"
        Else
            dstFilePath = "C:\Users\" & ComputerName & "\Desktop\Mazza\trunk\QA Task Monitoring_for_testing_only.xlsx"
        End If

        'dstFilePath = "C:\Users\jonas.ONE-SOURCE\Desktop\Mazza\trunk\QA Task Monitoring_2020.xlsx"
        'dstFilePath = "C:\Users\" & ComputerName & "\Desktop\Mazza\trunk\QA Task Monitoring_for_testing_only.xlsx"

        dstXlApp.DisplayAlerts = False
        dstXlWbs = dstXlApp.Workbooks
        dstXlWb = dstXlWbs.Open(dstFilePath)
        dstXlApp.Visible = False

        'Step 2 - open destination
        bgw.ReportProgress(Convert.ToInt32((2 / numToDo) * 100))
        'System.Threading.Thread.Sleep(5000)


        'Step 3 - Copy Range
        bgw.ReportProgress(Convert.ToInt32((3 / numToDo) * 100))
        'System.Threading.Thread.Sleep(3000)

        'Code for copying cells

        srcXlWrksht = srcXlWb.Worksheets(1)

        strTicketNumber = srcXlWrksht.Range("B3").Value
        strCID = srcXlWrksht.Range("B4").Value
        strIteration = srcXlWrksht.Range("B13").Value
        If Not IsNothing(strIteration) Then
            strIteration = strIteration.Substring(2)
        End If

        strSystem = srcXlWrksht.Range("B17").Value
        strAssignedBy = srcXlWrksht.Range("B10").Value
        strPMOBA = srcXlWrksht.Range("B9").Value
        strRequest = srcXlWrksht.Range("B5").Value
        strAssign = srcXlWrksht.Range("B6").Value
        strStart = srcXlWrksht.Range("B14").Value
        strEnd = srcXlWrksht.Range("B15").Value
        strStatus = srcXlWrksht.Range("C24").Value
        strTask = srcXlWrksht.Range("B16").Value


        If srcXlWb.Worksheets.Count >= 2 Then
            srcXlWrksht = srcXlWb.Worksheets(2)
            strDescription = srcXlWrksht.Range("D3").Value
        Else
            'strMissingValues = strMissingValues & vbCrLf & "Title/Description"
            isValidUploading = False
        End If


        'srcXlWrksht = srcXlWb.Worksheets(2)
        'strDescription = srcXlWrksht.Range("D3").Value


        If strTicketNumber = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Ticket ID"
            isValidUploading = False
        End If


        If strCID = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Change ID"
            isValidUploading = False
        End If


        If strIteration = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Iteration Number"
            isValidUploading = False
        End If


        If strSystem = "" Then
            strMissingValues = strMissingValues & vbCrLf & "System Name"
            isValidUploading = False
        End If


        If strAssignedBy = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Assigned By"
            isValidUploading = False
        End If


        If strPMOBA = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Assigned PMO/BA"
            isValidUploading = False
        End If


        If strRequest = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Requested Date"
            isValidUploading = False
        End If


        If strAssign = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Assigned Date"
            isValidUploading = False
        End If

        If strStart = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Start Date"
            isValidUploading = False
        End If

        If strEnd = "" Then
            strMissingValues = strMissingValues & vbCrLf & "End Date"
            isValidUploading = False
        End If


        If strStatus = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Status"
            isValidUploading = False
        End If


        If strTask = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Task type"
            isValidUploading = False
        End If


        If strDescription = "" Then
            strMissingValues = strMissingValues & vbCrLf & "Title/Description"
            isValidUploading = False
        End If

        'If CheckBox1.CheckState = CheckState.Checked And txtRemarks.Text = "" Then
        '    strMissingValues = strMissingValues & vbCrLf & "Empty Remarks"
        '    MessageBox.Show("Please input remarks", "Empty Remarks", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        '    isValidUploading = False
        'End If
        If Not isValidUploading Then
            MessageBox.Show(strMissingValues, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)

            srcXlWb.Close()
            dstXlWb.Close()
            srcXlApp.UserControl = True
            dstXlApp.UserControl = True
            srcXlApp.Quit()
            dstXlApp.Quit()


            Marshal.ReleaseComObject(dstXlWrksht)
            Marshal.ReleaseComObject(dstXlWb)
            Marshal.ReleaseComObject(dstXlWbs)
            Marshal.ReleaseComObject(dstXlApp)
            Marshal.ReleaseComObject(dstXlWrksht)
            Marshal.ReleaseComObject(srcXlWb)
            Marshal.ReleaseComObject(srcXlWbs)
            Marshal.ReleaseComObject(srcXlApp)

            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()

            bgw.CancelAsync()
            e.Cancel = True
            Exit Sub
        End If

        'strTicketNumber = srcXlWrksht.Range("B4").Value.ToString


        'code for pastespecial
        dstXlWb.Worksheets("Tasks").Range("A3").Select()
        Last_Row = dstXlWb.Worksheets("Tasks").Range("A3").End(Excel.XlDirection.xlDown).Row + 1
        dstXlWb.Worksheets("Tasks").Range("A" & Last_Row).Select()

        'Step 4 - Pasting values
        bgw.ReportProgress(Convert.ToInt32((4 / numToDo) * 100))

        'System.Threading.Thread.Sleep(3000)

        dstXlWb.Worksheets("Tasks").Range("C" & Last_Row).Value = strTicketNumber
        dstXlWb.Worksheets("Tasks").Range("B" & Last_Row).Value = strQAT
        dstXlWb.Worksheets("Tasks").Range("D" & Last_Row).Value = strCID
        dstXlWb.Worksheets("Tasks").Range("E" & Last_Row).Value = strIteration
        dstXlWb.Worksheets("Tasks").Range("F" & Last_Row).Value = strDescription
        dstXlWb.Worksheets("Tasks").Range("G" & Last_Row).Value = strSystem
        dstXlWb.Worksheets("Tasks").Range("H" & Last_Row).Value = strAssignedBy
        dstXlWb.Worksheets("Tasks").Range("I" & Last_Row).Value = strPMOBA
        dstXlWb.Worksheets("Tasks").Range("J" & Last_Row).Value = strSBU
        dstXlWb.Worksheets("Tasks").Range("K" & Last_Row).Value = strTypes
        dstXlWb.Worksheets("Tasks").Range("L" & Last_Row).Value = strProcess
        dstXlWb.Worksheets("Tasks").Range("M" & Last_Row).Value = strRequest
        dstXlWb.Worksheets("Tasks").Range("N" & Last_Row).Value = strAssign
        dstXlWb.Worksheets("Tasks").Range("O" & Last_Row).Value = strStart
        dstXlWb.Worksheets("Tasks").Range("P" & Last_Row).Value = strEnd
        dstXlWb.Worksheets("Tasks").Range("Q" & Last_Row).Value = strStatus
        dstXlWb.Worksheets("Tasks").Range("S" & Last_Row).Value = strRemarks
        dstXlWb.Worksheets("Tasks").Range("Y" & Last_Row).Value = strTask
        dstXlWb.Worksheets("Tasks").Range("W" & Last_Row).Value = "=+TEXT(QATM[[#This Row],[End Date]]," & """MM""" & ")"
        dstXlWb.Worksheets("Tasks").Range("X" & Last_Row).Value = "=+TEXT(QATM[[#This Row],[End Date]]," & """YYYY""" & ")"


        If CheckBox1.CheckState = CheckState.Checked Then
            dstXlWb.Worksheets("Tasks").Range("R" & Last_Row).Value = "=""FOR QAT"" " & "& " & "TEXT(" & dstXlWb.Worksheets("Tasks").Range("E" & Last_Row).Value + 1 & ", ""00"")"
        Else
            dstXlWb.Worksheets("Tasks").Range("R" & Last_Row).Value = "YES"
        End If

        'Step 5 - Saving
        bgw.ReportProgress(Convert.ToInt32((5 / numToDo) * 100))
        'System.Threading.Thread.Sleep(3000)

        dstXlApp.ActiveWorkbook.Save()

    Catch ex As Exception

        'MsgBox(ex.Message)
        Me.Cursor = Cursors.Default
        MessageBox.Show("Please check excel file.")
        'MsgBox("Error has occured. " & ex.Message, MsgBoxStyle.OkOnly + MsgBoxStyle.Critical, "Error")

        bgw.CancelAsync()
        Exit Sub

        srcXlWb.Close()
        dstXlWb.Close()
        srcXlApp.UserControl = True
        dstXlApp.UserControl = True
        srcXlApp.Quit()
        dstXlApp.Quit()

        Marshal.ReleaseComObject(dstXlWb)
        Marshal.ReleaseComObject(dstXlWbs)
        Marshal.ReleaseComObject(dstXlApp)
        Marshal.ReleaseComObject(srcXlWb)
        Marshal.ReleaseComObject(srcXlWbs)
        Marshal.ReleaseComObject(srcXlApp)

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

    Finally

        'srcXlWb.Close()
        'dstXlWb.Close()
        'srcXlApp.UserControl = True
        'dstXlApp.UserControl = True
        'srcXlApp.Quit()
        'dstXlApp.Quit()

        Marshal.ReleaseComObject(dstXlWb)
        Marshal.ReleaseComObject(dstXlWbs)
        Marshal.ReleaseComObject(dstXlApp)
        Marshal.ReleaseComObject(srcXlWb)
        Marshal.ReleaseComObject(srcXlWbs)
        Marshal.ReleaseComObject(srcXlApp)

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

    End Try




End Sub

标签： excelvb.netbackground-process

解决方案

推荐阅读