Checked if workbook is opened, if it is open sheet

Question

3.00/5 (1 vote)

See more:

Hello:

The below procedure allows me to check to see if Excel is opened. If it is not, then it opens Excel, goes to the selected workbook and activates the requested sheet. This works fine, but I jut ran into a problem, that is if the workbook is already opened I don,t want to open it again, simply go to the sheet requested. I have tried modifying my code below, but no luck yet. Here is what I have:

<pre>    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 Excel.Application

        Try
            'get an existing excel.application object
            xlApp = CType(GetObject(, "Excel.Application"), Application)
        Catch ex As Exception
            'no existing excel.application object - create a new one
            xlApp = New Excel.Application
        End Try

        Dim xlBook As Excel.Workbook
        Dim xlWBName As String = "2011.1004.Compensation Template"


        xlApp.Visible = True
        xlBook = xlApp.Workbooks.Open("F:\Test Environment\Compensation Workbook\Compensation Workbook\bin\Debug\" & xlWBName & ".xlsx")
        Dim xlSheet As Excel.Worksheet
        xlSheet = CType(xlBook.Sheets("SummaryWorksheet"), Worksheet)

        xlSheet.Activate()
  
    End Sub

Posted 11-Sep-13 5:23am

qbndl8

Add a Solution

Comments

ZurdoDev 11-Sep-13 12:27pm

Don't you just do xlBook.Sheets("SummaryWorksheet").Activate()

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-09-11T11:47:00

Something like that should do the job ;)

VB

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 Excel.Application

       Try
           'get an existing excel.application object
           xlApp = CType(GetObject(, "Excel.Application"), Application)
       Catch ex As Exception
           'no existing excel.application object - create a new one
           xlApp = New Excel.Application
       End Try

       Dim xlBook As Excel.Workbook
       Dim xlWBName As String = "2011.1004.Compensation Template"


       xlApp.Visible = True
       Try
               'get the opened workbook
                xlBook = xlApp.Workbooks(xlWBName & ".xlsx")
       Catch ex As Exception
               'open it
                xlBook = xlApp.Workbooks.Open("F:\Test Environment\Compensation Workbook\Compensation Workbook\bin\Debug\" & xlWBName & ".xlsx")
       End Try

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

       xlSheet.Activate()

   End Sub

Another way is to iterate through the collection of Workbooks ;)