Click here to Skip to main content
15,667,014 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I wrote a procedure that allows me to check if a workbook is open. If the workbook is opened, then go to the specified sheet, otherwise, open the workbook and go to the sheet.

I am having 2 issues, one is that is opening the same workbook twice when the workbook is not already opened. Second, after it opens and I close the second instance, I get this error:

System.Runtime.InteropServices.COMException was unhandled
Message=Exception from HRESULT: 0x800A03EC

The error is on the bold line:

If IsWorkbookAlreadyOpen(xlApp, workbookName) Then
           xlBook = xlApp.Workbooks.Open(workbookName)

Here is the rest of my code:

Private Shared Function IsWorkbookAlreadyOpen(app As Excel.Application, workbookName As String) As Boolean

    Dim isAlreadyOpen As Boolean = True


    Catch theException As Exception

        isAlreadyOpen = False

    End Try

    Return isAlreadyOpen

End Function

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\Debug\2011.1004.Compensation Template.xlsx"
    If IsWorkbookAlreadyOpen(xlApp, workbookName) Then
        xlBook = xlApp.Workbooks.Open(workbookName)
        xlBook = xlApp.Workbooks.Open(workbookName)
    End If

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

End Sub

What am i doing wrong here?
ZurdoDev 3-Sep-13 12:03pm    
Isn't your logic backwards? If there is an error trying to open the workbook you are saying it is already open? If it can open then it is open? If there is an error it is possibly due to it already being open. Plus there are other causes (file not found). Seems like you need a better way.

1 solution

You correctly found out that the Excel Application object has a Workbooks object.
Now use it correctly: it is an IEnumerable. Enumerate all of the Workbooks in it, and check each Workbook object if it is yours.
Hint: use a for or foreach block.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900