VB.NET Excel程序在完成后使EXCEL.EXE浮动

我正在编写一个循环遍历Excel工作簿中所有工作表的程序,并将每个工作表保存为自己的工作簿.事实证明它比我预期的要复杂一些,因为Sheet.Copy方法创建了一个奇怪的对象(请参阅此处的MSDN讨论,我相信这是相关的： http://msdn.microsoft.com/en-us/library/ms178779.aspx).

无论如何,我发现another Stack Overflow post让我到了我的位置,这基本上是完整的,在程序完成后留下的一个挂起的EXCEL.EXE进程之外(检查更新还有一个问题,但我认为它们是有关).

这是我的代码：

Imports System.Data
Imports System.IO
Imports Microsoft.Office.Interop
Imports Office = Microsoft.Office.Core
Imports xlNS = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Class Form1

Private Sub Button1_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button1.Click

    'Get information from text Boxes
    Dim InputLocation As String
    Dim OutputLocation As String

    InputLocation = InputLoc.Text & "\" & FileName.Text

    If OutputLoc.Text = "" Then
        OutputLocation = InputLoc.Text
    Else
        OutputLocation = OutputLoc.Text
    End If

    'Make file to save files in
    ' Get date and time in filename as well
    Dim TLDateTime As String
    Dim TLDay As String
    Dim TLMonth As Integer
    Dim TLYear As Integer
    Dim TLHour As Integer
    Dim TLMinute As Integer
    Dim TLDate As String
    Dim TLTime As String
    Dim TLSecond As Integer

    TLDay = DateTime.Now.Day
    TLMonth = DateTime.Now.Month
    TLYear = DateTime.Now.Year
    TLHour = DateTime.Now.Hour
    TLMinute = DateTime.Now.Minute
    TLSecond = DateTime.Now.Second

    Dim MyDate As New DateTime(TLYear,TLMonth,TLDay,TLHour,TLMinute,TLSecond)
    Dim MyString As String = MyDate.ToString("MMMddyyyy_HHmmss")
    TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString
    TLTime = TLHour.ToString + TLMinute.ToString
    TLDateTime = TLDate + "_" + TLTime

    Try
        Directory.CreateDirectory(OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime)
        OutputLocation = OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime
    Catch
        MsgBox("Trying to create a file that exists,please delete it. If the file does not exist check to make sure your output location exists")
    End Try

    'Open up excel file with information in it

    Dim xlApp1 As Excel.Application
    Dim locs As Excel.Workbook

    Dim exportsheet As Excel.Worksheet
    xlApp1 = New Excel.Application
    xlApp1.Visible = True
    xlApp1.Application.DisplayAlerts = False
    locs = xlApp1.Workbooks.Open(InputLocation)

    'locsws = locs.ActiveSheet
    Dim wkshtcount = locs.Worksheets.Count - 1
    Dim fileNames As New ArrayList

    For counter = 1 To wkshtcount + 1
        'identify and copy sheet to move
        exportsheet = CType(locs.Worksheets(counter),Excel.Worksheet)
        fileNames.Add(exportsheet.Name)
        exportsheet.Copy(Type.Missing,Type.Missing)

        exportsheet = xlApp1.Workbooks("Book" & counter).Sheets(1)

        exportsheet.SaveAs(Filename:=OutputLocation & "\" & fileNames(counter - 1) & ".xlsx")

        'close excel and release com objects
        System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
        exportsheet = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.ActiveWorkbook)
        xlApp1.ActiveWorkbook.Close(False)

    Next
    'close excel and release com objects
    locs.Close(False)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(locs)
    locs = Nothing
    xlApp1.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1)
    xlApp1 = Nothing


End Sub
End Class

现在我认为问题来自循环结束,我尝试关闭导出文件和它创建的新工作表：

'close excel and release com objects
        System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
        exportsheet = Nothing
        xlApp1.Workbooks(fileNames(counter - 1)).Close(False)

我无法弄清楚如何为创建的新工作表释放ComObject.我一直在尝试各种各样的事情,但是当我这样做时它总是抛出一个COM错误,并且如果我试图将其定义为没有(就像我对exportheet一样)是说它是默认只读,所以我可以’做到了.看起来应该是这样简单：

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.Workbooks(fileNames(counter - 1)))

但事实并非如此.我尝试了一些这方面的变种,我认为它与上面的MSDN链接有关,但我不能完全理解该怎么做.因此代码适用于我的目的,除了在完成后留下一个EXCEL.EXE.

至于一个测试文件,我只是使用一个3页的Excel文件,我在每个文件上放了一些信息并更改了工作表名称,因此很容易看出它是否正常工作.

任何想法将不胜感激,谢谢.

更新：我刚刚从我的主Excel应用程序关闭了可见性,但仍然会弹出一些东西,这让我相信我使用Copy的方式是创建一个新的Excel应用程序,但我不确定如何引用它.如果有人知道如何关闭那里的能见度,那将非常感激.

最后更新：关于一些可怜的灵魂遇到我遇到的同一问题的机会,第一次更新应解决它,但同样重要的是要注意excel.exe将挂起,直到你关闭应用程序.我将自动化代码报告为Windows窗体应用程序(因此同事可以提供文件位置等),并且在您关闭程序弹出窗口之前,将会运行excel.exe进程.也许垃圾收集在关闭应用程序窗口之前不会运行,或者由于某些其他原因它只是挂起到excel.exe的实例.

下面是适合我的代码(请注意我释放对象的顺序,这很重要)

xlWorkBook.Close()
xlApp.Quit()
ReleaSEObject(xlWorkSheet)
ReleaSEObject(xlWorkBook)
ReleaSEObject(xlApp)

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

VB.NET Excel程序在完成后使EXCEL.EXE浮动

猜你在找的VB相关文章