vb.net 如何测试应用程序和工作簿是否已打开

How to test if an application and workbook are opened

提问by Jose M.

I am looking for help on trying to figure out how to check if a particular workbook is opened. I have a button on my Windows form that when pressed, it will open Excel and go to a particular worksheet. However, I just ran into the problem that the user may already have that particular workbook open, so instead of creating a new instance of Excel and opening the workbook again, I want to check if that workbook is opened, if it is, then go to the selected worksheet.

我正在寻找有关尝试找出如何检查特定工作簿是否已打开的帮助。我的 Windows 窗体上有一个按钮，按下该按钮时，它将打开 Excel 并转到特定的工作表。但是，我刚刚遇到了这样一个问题，即用户可能已经打开了那个特定的工作簿，所以我不想创建一个新的 Excel 实例并再次打开该工作簿，而是想检查该工作簿是否已打开，如果是，则去到选定的工作表。

Here is the code I have so far:

这是我到目前为止的代码：

Private Sub btnMinSummaryWorksheet_Click(sender As Object, e As EventArgs) Handles btnMinSummaryWorksheet.Click
    'This procedure runs when the btnOpenSummaryWorksheet button is clicked. Calls the
    'Sub procedure opens the Summary Worksheet Dashboard

    Dim xlApp As New Excel.Application
    xlApp.Visible = True

    Dim xlBook As Excel.Workbook
    xlBook = xlApp.Workbooks.Open("F:\Test Environment\Compensation Workbook\Compensation Workbook\bin\Debug11.1004.Compensation Template.xlsx")

    Dim xlSheet As Excel.Worksheet
    xlSheet = CType(xlBook.Sheets("SummaryWorksheet"), Worksheet)
    xlSheet.Activate()

    Me.Close()
End Sub

回答by Karl Anderson

Try making a function that will check to see if the workbook is already open or not, like this:

尝试创建一个函数来检查工作簿是否已经打开，如下所示：

Private Shared Function IsWorkbookAlreadyOpen(app As Excel.Application, workbookName As String) As Boolean
    Dim isAlreadyOpen As Boolean = True

    Try
        app.Workbooks.get_Item(workbookName)
    Catch theException As Exception
        isAlreadyOpen = False
    End Try

    Return isAlreadyOpen
End Function

Then you can use it in your code like this:

然后你可以像这样在你的代码中使用它：

Private Sub btnMinSummaryWorksheet_Click(sender As Object, e As EventArgs) Handles btnMinSummaryWorksheet.Click
    'This procedure runs when the btnOpenSummaryWorksheet button is clicked. Calls the
    'Sub procedure opens the Summary Worksheet Dashboard

    Dim xlApp As New Excel.Application
    xlApp.Visible = True

    Dim xlBook As Excel.Workbook
    Dim workbookName = "F:\Test Environment\Compensation Workbook\Compensation Workbook\bin\Debug11.1004.Compensation Template.xlsx"
    If IsWorkbookAlreadyOpen(xlApp, workbookName) Then
        xlBook = xlApp.Workbooks.get_Item(workbookName)
    Else
        xlBook = xlApp.Workbooks.Open(workbookName)
    End If

    Dim xlSheet As Excel.Worksheet
    xlSheet = CType(xlBook.Sheets("SummaryWorksheet"), Worksheet)
    xlSheet.Activate()

    Me.Close()
End Sub

回答by HymanyLe

For somebody still have similar problem in 2018 :D.

对于有人在 2018 年仍然有类似的问题：D。

Imports System.IO

进口系统.IO

Function FileOpenTest(ByVal WorkBookName As String) As Boolean
    Dim fs As FileStream
    FileOpenTest= False
    Try
        fs = System.IO.File.OpenWrite(WorkBookName)
        fs.Close()
    Catch ex As Exception
        FileOpenTest= True
    End Try

    Return FileOpenTest
End Function

回答by Amit Vaidya

Imports Microsoft.Office.Interop Public Class Collection

导入 Microsoft.Office.Interop 公共类集合

Private moExcelApplication As Excel.Application
Private moExcelWorkBook As Excel.Workbook
Private moExcelWorkBook1 As Excel.Workbook
Private moWorkBooks As Excel.Workbooks
Private moActivesheet As Excel.Worksheet
Private moCurrentRange As Excel.Range



Public Function CloseExcelFile()
    Dim bprocess As Boolean
    Try
        bprocess = False
        If (Not moExcelWorkBook Is Nothing) Then
            If (Not moExcelApplication Is Nothing) Then
                moExcelApplication.DisplayAlerts = False
                bprocess = True
            End If
        End If

        If (Not moExcelApplication Is Nothing) Then
            moExcelApplication.DisplayAlerts = True
            bprocess = True

        End If

    Catch ex As Exception

    Finally
        recordclear(moCurrentRange)
        moExcelWorkBook.Close(False)
        recordclear(moExcelWorkBook)
        'moExcelApplication.Quit()
        recordclear(moExcelApplication)

        'System.Threading.Thread.Sleep(500)
        'MessageBox.Show("Excel Closed")
        CloseExcelFile = bprocess

    End Try
End Function
Private Sub recordclear(ByVal o As Object)
    Try
        Do Until _
        System.Runtime.InteropServices.Marshal.ReleaseComObject(o) <= 0
        Loop
    Catch

    Finally
        o = Nothing
    End Try


End Sub


Public Function openExcel()
    Dim bprocess As Boolean
    Try
        moExcelApplication = New Excel.Application
        moWorkBooks = moExcelApplication.Workbooks

        If Not (moExcelApplication Is Nothing) Then
            If Not moWorkBooks Is Nothing Then
                moExcelWorkBook = moWorkBooks.Open("C:\Documents and Settings\anand\Desktop\amit2.xlsx")
                moExcelApplication.Visible = True
            End If
        End If
    Catch ex As Exception
    End Try

    Return bprocess

End Function


Public Function isOpen()
    On Error Resume Next
    moExcelWorkBook.Open("C:\Documents and Settings\anand\Desktop\amit2.xlsx")
    If Not moExcelWorkBook Is Nothing Then
        moExcelWorkBook.Nothing() = False
        On Error GoTo -1
        If moExcelWorkBook.ConnectionsDisabled = True Then
            Return 1
        Else
            Return 0
        End If
    Else 'It is open
        moExcelWorkBook = Nothing
        On Error GoTo 0
        Return 1
    End If

End Function

End Class

结束班

回答by Amit Vaidya

jst Check it out this work properly

jst 检查它是否正常工作

Imports Microsoft.Office.Interop

导入 Microsoft.Office.Interop

Public Class Form1 Private moExcelApplication As Excel.Application Private moExcelWorkBook As Excel.Workbook Private moWorkBooks As Excel.Workbooks Private moActivesheet As Excel.Worksheet Private moWorkSheets As Excel.Sheets Private moCurrentRange As Excel.Range

公共类 Form1 私有 moExcelApplication 作为 Excel.Application 私有 moExcelWorkBook 作为 Excel.Workbook 私有 moWorkBooks 作为 Excel.Workbooks 私有 moActivesheet 作为 Excel.Worksheet 私有 moWorkSheets 作为 Excel.Sheets 私有 moCurrentRange 作为 Excel.Range

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ShowDirect.Click
    System.Diagnostics.Process.Start("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE")
End Sub

Private Sub Showobj_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Showobj.Click
    Dim sCount As String

    Try
        Cancel.Enabled = True
        If isopen() = 1 Then
            Call OpenExcelFile()
            moActivesheet = moExcelWorkBook.ActiveSheet
            moCurrentRange = moActivesheet.Range("a1", "e5")
            For i As Integer = 1 To 5
                For j As Integer = 1 To 5
                    sCount = i.ToString + j.ToString
                    moActivesheet.Cells(i, j).value = sCount
                Next
            Next
        Else

            MsgBox("already open")

        End If

    Catch ex As Exception

    End Try


End Sub
Private Declare Auto Function GetWindowThreadProcessId Lib "user32.dll" (ByVal hwnd As IntPtr, _
          ByRef lpdwProcessId As Integer) As Integer


Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click
    'Dim oclass As Collection
    'Try
    '    Showobj.Enabled = True
    '    oclass = New Collection
    '    oclass.moExcelApplication = moExcelApplication
    '    oclass.moExcelWorkBook = moExcelWorkBook
    '    oclass.moCurrentRange = moCurrentRange
    '    oclass.CloseExcelFile()


    'Catch ex As Exception

    'End Try

    Try
        If (Not moExcelWorkBook Is Nothing) Then
            If (Not moExcelApplication Is Nothing) Then
                moExcelApplication.DisplayAlerts = False
                moExcelWorkBook.Nothing() = True

            End If
        End If

        If (Not moExcelApplication Is Nothing) Then
            moExcelApplication.DisplayAlerts = True
            moExcelWorkBook.Nothing() = True

        End If

    Catch ex As Exception

    Finally

        If moExcelApplication.Visible = True Then
            moExcelWorkBook.Close(False)
        Else
            MsgBox("Excel already closed")
        End If
        recordclear(moCurrentRange)
        recordclear(moActivesheet)
        recordclear(moWorkSheets)
        recordclear(moExcelWorkBook)
        recordclear(moWorkBooks)
        moExcelApplication.Quit()
        recordclear(moExcelApplication)
        System.Threading.Thread.Sleep(500)
        MessageBox.Show("Excel Closed")
        Cancel.Enabled = False
    End Try



End Sub




Private Sub OpenExcelFile()
    Try
        moExcelApplication = New Excel.Application
        moWorkBooks = moExcelApplication.Workbooks

        If Not (moExcelApplication Is Nothing) Then
            If Not moWorkBooks Is Nothing Then
                moExcelWorkBook = moWorkBooks.Open("C:\Documents and Settings\anand\Desktop\amit2.xlsx")
                moExcelApplication.Visible = True
            End If
        End If
    Catch ex As Exception
    End Try
End Sub
Function isopen()

    On Error Resume Next
    moExcelWorkBook.Open("C:\Documents and Settings\anand\Desktop\amit2.xlsx")
    If Not moExcelWorkBook Is Nothing Then
        moExcelWorkBook.Nothing() = False
        On Error GoTo -1
        If moExcelWorkBook.ConnectionsDisabled = True Then
            Return 1
        Else
            Return 0
        End If
    Else 'It is open
        moExcelWorkBook = Nothing
        On Error GoTo 0
        Return 1
    End If

End Function

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Showobj.Enabled = True
    Cancel.Enabled = False
End Sub

Private Sub recordclear(ByVal o As Object)
    Try
        Do Until _
        System.Runtime.InteropServices.Marshal.ReleaseComObject(o) <= 0
        Loop
    Catch

    Finally
        o = Nothing
    End Try


End Sub

End Class

结束班