none
Activeworkbook keeps changing in Excel 2013 RRS feed

  • Question

  • In my Excel
    vsto in 2013, I am finding that the vsto code is not sure what the active
    workbook is if the vsto code opens a new workbook. Everything works fine
    in Excel 2010 and 2007 however.

    It seems that
    in Excel 2013 globals.thisaddin.application.activeworkbook is specific to the
    class/module that the code is currently in.

    For example:

    I am in
    Workbook1 and I select a button that starts my code:

    In Sub1 I
    call another subroutine, Sub2 which is in another class object 

    In Sub2 I
    open a new workbook: workbook2

    At this point
    the Watch shows that globals.thisaddin.application.activeworkbook.name is the
    same as the workbook I have just opened: workbook2. This is correct

    When sub2
    finishes, control passes back to Sub1, now the globals.thisaddin.application.activeworkbook.name
    is now back to displaying workbook1 - although I have done nothing to change
    the activeworkbook

    Because the
    rest of the code in Sub1 should apply to the activeworkbook, in Excel 2013
    it updates workbook1 whereas in Excel 2010 and 2007 it updates workbook2.

    Currently my fix is to check I am in the workbook I think I am before I can do anything.

        Public Sub ActivateWorkbook(ByVal strName As String)
            For Each wkbk As Excel.Workbook In app.Workbooks
                If wkbk.Name.ToUpper = strName.ToUpper Then
                    wkbk.Activate()
                    Exit For
                End If
            Next
        End Sub

    For example when
    I use wks.select  I now have to first check whether the activeworkbook is
    the parent object of wks.

    Am I doing something wrong or will I have to rewrite my vsto for Excel 2013, ensuring the
    activeworkbook is always set back to the one I thought it was?



    • Edited by MerlinXL Sunday, February 8, 2015 2:02 PM
    Sunday, February 8, 2015 1:49 PM

Answers

  • I have worked out why I was seeing the Activeworkbook change in Excel 2013 after using VB.NET to open a new workbook. This is an old thread but hopefully my answer with help someone, someday.

    It was because I use a form to display a progress bar to update the user with the name of the worksheet currently being processed.

    The circumstances of my issue were:

    Initially the user is in book1.xlsx and selects a ribbon button to start the process.

    A form is displayed, but because of the new single document interface in Excel 2013 I had  to assign the form a windows handle using code below otherwise the form is hidden when a new workbook is opened

    PublicxlMain AsNewNativeWindow()

    xlMain.AssignHandle(NewIntPtr(app.Hwnd))

    frm.show(xlMain)

    If code opens a workbook (book2.xlsx) while the form is displayed, the newly opened workbook becomes the activeworkbook.

    I caused myself problems because  as I was looping through each sheet in the newly opened workbook I update the progressbar in the opened form.

    By using a call to update the value of the progressbar in the form, the activeworkbook (Globals.ThisAddIn.Application.Activeworkbook) switches back to referring to the workbook that was the activeworkbook when the form was initially opened i.e. book1.xlsx. This is probably because the form was assigned the Windows Handle linking it to activewindow when it was opened.

    My quick solution was not to update the progressbar as I loop through the sheets in the new workbook.

    This issue does not occur in Excel prior to Excel 2013.

    • Marked as answer by MerlinXL Friday, November 27, 2015 9:44 AM
    Friday, November 27, 2015 9:44 AM

All replies

  • Have you solved this somehow?

    I had almost the same issue, so i change code: 

    I had to rewrite all code from ActiveWorkbook to WorkBooks["name"]. Because Excel 2013 interacts differently with ActiveWorkbook..

    Wednesday, February 11, 2015 8:32 AM
  • No I have not solved it other than to use the code above to ensure the workbook I wanted was active before I action it.

    I have to select cells in the active workbook before I do certain things like Freeze Panes as it seems to me that  if the correct cell is not selected freeze panes does not work as required. You have to ensure the workbook is active before you can select cells in it.

    Hopefully Microsoft will either realise that there is a problem with the interop for Excel 2013 or publish the correct way to deal with the single document interface.

    Wednesday, February 11, 2015 9:43 AM
  • Hi
    >> in Excel 2013 globals.thisaddin.application.activeworkbook is specific to the
    class/module that the code is currently in.

    Add-in is an application-level Office solution, when loading an add-in, VSTO runtime will create an application domain. Each application domain is isolated, will not interrupt others. So the class/module is not  running on a specific workbook.
    >>control passes back to Sub1

     According to your description, I cannot understand your questions clearly.What do you man control passes back to Sub1 ? Can you share your sample project to clarify your issues clearly if it is convenient?

    Thanks for your understanding.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 12, 2015 1:23 AM
    Moderator
  • Just to clarify, as I initially misread your response, you have quoted an extract from my question, rather than make the assertion that  "the activeworkbook is specific to the class/module"

    By control passing back I mean that  part way through the code in Sub1 there is a line call Sub2 and when Sub2 finishes  the rest of the code in Sub1, below the call Sub2 line, is executed.

    At the end of Sub2 I have a line:

    System.diagnostics.debug.print  g(lobals.thisaddin.application.activeworkbook.name)  and this returns Book2

    But the first line of code in Sub1, below the call Sub2 is also System.diagnostics.debug.print  (globals.thisaddin.application.activeworkbook.name)   and this returns Book1

    So the activeworkbook appears to be different in different subs which are in different classes

    Unfortunately if I create a simple vsto application level addin and try to create an example by having code that opens a new workbook, I cannot recreate the issue as the activeworkbook behaves as expected, so there must be something I do in my main vsto that causes this issue

    My Excel vsto in which I get this issue is over 100,000 lines of code and I can't really upload the full application.

    I will try to work on my example vsto to see if I can recreate the circumstances that switches the Globals.thisaddin.application.activeworkbook as the running code moves between classes/modules.

    Thursday, February 12, 2015 9:38 AM
  • Hi Merlin (and Julius)

    The behavior you're seeing does make sense, from Excel's point of view. What I'd do, in your case, is work with Excel.Workbook objects, rather than the generic "ActiveWorkbook".

    For example, if you know that Sub2 is creating a new workbook with which Sub1 should continue to work, then make Sub2 a FUNCTION that returns the workbook as an object. Something like this:

      Dim newWb as Excel.Workbook
      newWb = Function2
     
      Function2 () As Excel.Workbook
        Dim wb as Excel.Workbook
        wb = Me.Application.Workbooks.Add()
        return wb
      End Function


    Cindy Meister, VSTO/Word MVP, my blog

    • Proposed as answer by L.HlModerator Tuesday, February 17, 2015 7:33 AM
    Friday, February 13, 2015 8:26 PM
    Moderator
  • Thanks for the suggestion and I am glad the behaviour makes sense.

    If I were to start now I would use the recommended approach you suggest, however  as the vsto was converted from a vba addin for Excel 2003 several years back when activeworkbook solved more problems than it caused, activeworkbook was the approach that was taken. Rewriting the code is not an option in the short term.

    So in short, in Excel 2013 you can't always be sure what the activeworkbook is if your code opens a workbook.

    Oh well, if it was easy to write a vsto add-in everyone would be doing it!

    Friday, February 13, 2015 8:56 PM
  • Hey,

    I don't think Excel 2013 can't be sure what the activewoerkbook as the condition that opeing a new workbook. I am using VB to test it serval times, all works fine. So I am curious about your code, in case we have different understanding.

    Sunday, February 15, 2015 10:12 AM
  • I have tracked down at least one of the places where the activeworkbook suddenly changes mid code.

    part way through my code I update the Excel statusbar and at this point the activeworkbook changes

    Globals.ThisAddIn.Application.StatusBar = "Updating ....."
    

     If I place a Watch on Globals.ThisAddIn.Application.Activeworkbook.Name  I can see that before the Statusbar line is run the activeworkbook.name in the Watch window is the newly opened workbook, but after the status bar line is run the watchwindow displays the original workbook name that was active BEFORE the workbook was opened.

    I cannot recreate this in a simple application, but it happens in my main app that is too large to share.

    Although I can fix this occurrence by removing the statusbar line, there are 3 other times that the active workbook switches back in my code but there are no other statusbar lines so I do not know what causes the switching of the activeworkbook in those cases.

    Thursday, February 19, 2015 11:30 AM
  • I have worked out why I was seeing the Activeworkbook change in Excel 2013 after using VB.NET to open a new workbook. This is an old thread but hopefully my answer with help someone, someday.

    It was because I use a form to display a progress bar to update the user with the name of the worksheet currently being processed.

    The circumstances of my issue were:

    Initially the user is in book1.xlsx and selects a ribbon button to start the process.

    A form is displayed, but because of the new single document interface in Excel 2013 I had  to assign the form a windows handle using code below otherwise the form is hidden when a new workbook is opened

    PublicxlMain AsNewNativeWindow()

    xlMain.AssignHandle(NewIntPtr(app.Hwnd))

    frm.show(xlMain)

    If code opens a workbook (book2.xlsx) while the form is displayed, the newly opened workbook becomes the activeworkbook.

    I caused myself problems because  as I was looping through each sheet in the newly opened workbook I update the progressbar in the opened form.

    By using a call to update the value of the progressbar in the form, the activeworkbook (Globals.ThisAddIn.Application.Activeworkbook) switches back to referring to the workbook that was the activeworkbook when the form was initially opened i.e. book1.xlsx. This is probably because the form was assigned the Windows Handle linking it to activewindow when it was opened.

    My quick solution was not to update the progressbar as I loop through the sheets in the new workbook.

    This issue does not occur in Excel prior to Excel 2013.

    • Marked as answer by MerlinXL Friday, November 27, 2015 9:44 AM
    Friday, November 27, 2015 9:44 AM