none
Procedure to check of opened workbook, getting error. RRS feed

  • General discussion

  • Hello:

    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
      ErrorCode=-2146827284
      HResult=-2146827284
      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
    
            Try
                app.Workbooks.Open(workbookName)
    
            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)
            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

    Tuesday, September 3, 2013 2:50 PM

All replies

  • Hi,

    I tried to reproduce your issues but failed. I wrote a programming to open a workbook twice and use your method “IsWorkbookAlreadyOpen” to judge whether the workbook is opened. But the workbook opened fine and there is no exception when the workbook is opened.

    private void Button1_Click(object sender, RibbonControlEventArgs e)
    {
        object missing = Type.Missing;
        Globals.ThisAddIn.Application.Workbooks.Open(@"C:\test.xlsx",
            missing, missing, missing, missing, missing, missing, missing,
            missing, missing, missing, missing, missing, missing, missing);
        Globals.ThisAddIn.Application.Workbooks.Open(@"C:\test.xlsx",
            missing, missing, missing, missing, missing, missing, missing,
            missing, missing, missing, missing, missing, missing, missing);
    }

    In fact, if the workbook which is first opened hasn’t been modified, you can try to open it any times and only one workbook will be opened at last.

    Could you provide us more information about your operation which caused the error?

    In addition, I found there is an inconsistency between your description and your code.

    From your description “If the workbook is opened, then go to the specified sheet, otherwise, open the workbook and go to the sheet”. In your code, no matter the workbook is opened or not, the workbook will be opened again.

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

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 5, 2013 3:11 AM
    Moderator
  • Thanks for the comments. I see where I made the error between the code and the comment. I was able to fix the code and get it to work.
    Friday, September 13, 2013 2:47 AM