Create custom Ribbon button if idMSO fails

Add-in Express™ Support Service
That's what is more important than anything else

Create custom Ribbon button if idMSO fails
 
Posts 1 - 10 of 12
First | Prev. | 1 2 | Next | Last 
Ken Puls




Posts: 34
 Joined: 2014-12-17
Hi there,

I'm trying to create a button in my ribbon that uses the idMso through the Ribbon designer. It works great on Office 2019 and higher, but unfortunately that idMso didn't exist in Excel 2016, so I have to provide a custom button and hack the abilities by leveraging the old commandbars object in order to show the pane I'm looking for. For a variety of reasons, the custom route is less than ideal.

What I'd like to do is create the button using the idMso, but if it fails, revert to the custom button instead. Is that possible? There's no way that I can see through the designer that I can do this, so I'm wondering if someone can provide me a method.

In a simplistic overview, I'm looking to do this: 

try
     createidMsoButton
Catch ex as exception
     createCustomButton
end try


Alternately, should I create both buttons and set up a getVisible callback for the idMso version to set the custom version to false? I suppose if the idMso button never gets created, it can't trigger the callback to control the other one?

Open to ideas!
Ken Puls, FCPA, FCMA, MS MVP
Posted 28 Apr, 2020 13:20:46 Top
nwein




Posts: 577
 Joined: 2011-03-28
Since you know the button only exists in 2019, you can check the host version (yourCOMAddinModule.HostMajorVersion) and based on that decide which button you want to create.
Posted 28 Apr, 2020 15:16:12 Top
Ken Puls




Posts: 34
 Joined: 2014-12-17
Appreciate this, but I'm struggling with it. I can't find anything in our project that support the .HostMajorVersion object... (code modules, winforms, or the global project.) The only example I can see in the AddinExpress documentation seems to be related to taskpanes, which we aren't using.

I am curious as well, if .HostMajorVersion returns a different value than the application version reported by Excel, as that has reported 16 for Excel 2016, Excel 2019 and Office 365. And that is actually the crux of the issue.
Ken Puls, FCPA, FCMA, MS MVP
Posted 28 Apr, 2020 15:46:41 Top
nwein




Posts: 577
 Joined: 2011-03-28
The HostMajorVersion is a property of the ADXAddinModule object which is what your add-in implements, so you definitely "have" it :)
I'd be surprised if Excel 2019 reports 16 as the version, though crazier things have happened before.
Posted 28 Apr, 2020 16:35:26 Top
Ken Puls




Posts: 34
 Joined: 2014-12-17
Strange... Is it a property that you have to add to the AddInModule at some point, as mine doesn't seem to come up with anything in intellisense. Right now, just to test it I want to try a really simple:

MsgBox(AddinExpress.MSO.ADXAddinModule.HostMajorVersion.ToString, vbokonly)

But I get no relevant hits after ADXAddinModule (just things like ADXRegister, GetProperty, etc...)

Could it be because we target multiple versions of Excel that this property isn't available?

As an an aside on the application version, just checked and xlApp.Application.Version does return 16 for Office365. (I wrote a blog post on trying to get the correct version number. It's here if you're interested: https://www.excelguru.ca/blog/2019/02/11/check-the-application-version-in-modern-office/)
Ken Puls, FCPA, FCMA, MS MVP
Posted 28 Apr, 2020 17:26:27 Top
Ken Puls




Posts: 34
 Joined: 2014-12-17
I think I have a method for this, given that I'm not super optimistic about testing the version anyway.

I created two versions of the button:
-ADXRibbonButton27 is the one I really want to use
-ADXRibbonButton4 is the "fall back" if I can't find 27

I then added a PropertyChanging event to test the status of ADXRibbonButton27 like this: 


Private Sub AdxRibbonButton4_PropertyChanging(sender As Object, e As ADXRibbonPropertyChangingEventArgs) Handles AdxRibbonButton4.PropertyChanging
        Select Case e.PropertyType
            Case ADXRibbonControlPropertyType.Visible
                Try
                    'Try to connect to the modern button
                    e.Value = Not AdxRibbonButton27.Visible

                Catch ex As Exception
                    'Must not have found the button, so show this one
                    e.Value = True

                End Try
        End Select
End Sub


It seems to work, with Excel 2016 getting the old one, and 2019 plus getting the new one.

:)
Ken Puls, FCPA, FCMA, MS MVP
Posted 28 Apr, 2020 18:55:11 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hello Ken and Nir,

The property is on your add-in module: just check this property in the code of the module. If you need to access it outside of the add-in module, write {project name such as MyAddin1}.AddinModule.CurrentInstance.HostMajorVersion. If IdMso not found, your Ribbon XML produces an error which you can see if you select the check box described in section Get Informed about Errors in Ribbon markup; see the PDF file in the folder {Add-in Express}\Docs on your development PC. That is, although your approach may work, it produces an error that the user may see.

Also check https://www.add-in-express.com/forum/read.php?FID=5&TID=15302.


Andrei Smolin
Add-in Express Team Leader
Posted 29 Apr, 2020 04:13:32 Top
Ken Puls




Posts: 34
 Joined: 2014-12-17
Hi Andrei,

Thanks for this. Two observations:

1) I was able to query the AddinModule.CurrentInstance.HostMajorVersion property (I guess I was missing CurrentInstance). And, as expected, it returns 16 for Office 2016, 2019 and O365.

2) Surprisingly, I don't believe that my method actually triggered an error even with the "Show Add-in User Interface Errors Checked" option. Will have to do further testing now that you say it should, but I think I may have a working route there.

Will post back if I find I'm wrong though. I've been having a heck of a time getting a VM to stay Office 2016... it seems to keep on upgrading me. Crazy frustrating when you can't even rely on your test environment!
Ken Puls, FCPA, FCMA, MS MVP
Posted 29 Apr, 2020 16:57:31 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hello Ken,

Ken Puls writes:
Surprisingly, I don't believe that my method actually triggered an error even with the "Show Add-in User Interface Errors Checked" option. Will have to do further testing now that you say it should, but I think I may have a working route there.


Please let me know whatever test results you get.


Andrei Smolin
Add-in Express Team Leader
Posted 30 Apr, 2020 04:39:11 Top
Ken Puls




Posts: 34
 Joined: 2014-12-17
Hi Andrei,

Ugh. Testing this stuff is so hard. I discovered that my virtual machine with my Office 2016 install (installed from Office 2016 media) was reporting itself as Office 2016, but was in fact updating to Office 365. Presumably it did this because automatic updates were checked, and it looks at my the subscription level on my account. Microsoft seriously can't make this any harder, can they?

I've recreated a new VM and disabled updates for testing, so can now feed back some more accurate results.

Okay, so here's the fun part. For my Ribbon, I'm trying to add a two very specific buttons, and both have some issues.

The first is the "New Query" or "Get Data" button from the Data tab. As it turns out, Microsoft has provided three potential versions of this button, depending on the version of Excel (2016/2019+) and the based on subscription (perpetual/office 365). And as you know, testing for the app version isn't a reliable indicator any more as to what is happening.

To solve this, I've actually added 3 idMso buttons to my Ribbon:
-FlyoutAnchorPowerQueryNewQuery (Excel 2016)
-PowerQueryGetTransformDataMenu (Excel 2019 perpetual)
-PowerQueryGetTransformDataMenuSubscription19 (Office 365)

In these cases, I have done no special handling. I just add them to the ribbon, and the correct one shows up. All others just... don't.

Interestingly enough, when launching in 2016, only the PowerQueryGetTransformDataMenu triggers an error if "Show Add-in User Interface Errors Checked" option is checked. This makes sense, as Excel 2016 was released with a subscription menu, which they seemed to have called PowerQueryGetTransformDataMenuSubscription19.

That's irritating, but I don't really know an easy way to reliable way to not even try creating that ribbon button if it's not Excel 2019 or higher. I understand Ming's testing approach, I just don't see anything in the documentation on creating a contextual button.

Honestly, I'd LOVE it if you guys could devise the tests to get Application.HostMajorVersion (or add us a new property called .ExcelMajorVersion to avoid messing up anyone who relies on .HostMajorVersion) which returns 16,19,365 based on that logic. (Excel's new UNIQUE() function should be a good indicator that you're on O365 today, not 2019, although it might need to be checked once Office vNext comes out...). If you could do that and give us a property on the Ribbon UI that says "Create in version", that would be brilliant as it would save us a lot of pain.

The second button I'm trying to get to is the Show Queries button (2016) vs Queries & Connections button (2019+). And as it turns out, my method doesn't trigger any UI errors, but it also doesn't seem to show me the ADXRibbonButton4 either. I'm going to need to look at this one a bit more closley.
Ken Puls, FCPA, FCMA, MS MVP
Posted 01 May, 2020 13:08:49 Top

Posts 1 - 10 of 12
First | Prev. | 1 2 | Next | Last