Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Upgrading my .XLA addin to also work with Excel 2007


Reply
 
Thread Tools Display Modes
  #1  
Old 12-05-2007, 05:35 AM
a3b a3b is offline
Newcomer
 
Join Date: Nov 2007
Posts: 24
Default Upgrading my .XLA addin to also work with Excel 2007


I read in the forum on customizing the Ribbon.
I developped an add-in in Excel (.xla) that is triggered from my accounting program using COM. I use the built in VBA editor.
Since I'm a control-freak, I disabled the Excel menu's and toolbars, and replaced them with my own, but in Excel 2007 this has become a lot more difficult.
How can I add Ribbon functionality to an existing .xla source while maintaining compatibility for earlier versions of Excel.
Is there an article somewhere, where I can see how this works ? I hope you can help me with this, since my customers are pushing me for Excel 2007 compatibility.
Reply With Quote
  #2  
Old 12-05-2007, 06:00 AM
the master's Avatar
the master the master is offline
Tachikoma
 
Join Date: Mar 2003
Location: Mansfield, UK
Posts: 4,595
Default

Is this what you were looking for?

Please read the Posting Guidelines. If you think you have posted a thread in the wrong forum you should notify a moderator so they can move it for you
__________________
"That which seems simple is often overlooked" ~ me
Halloween 2014 Yard Haunt
Halloween Special FX
Reply With Quote
  #3  
Old 12-05-2007, 07:51 AM
a3b a3b is offline
Newcomer
 
Join Date: Nov 2007
Posts: 24
Default

Not quit.
Since my users don't know and see Excel while they are working with the application, they cannot alter settings. I just want to put a .XLA file in the users environment with the same functionality, that also supports Excel 2007. I know how I have to change the Ribbon itself by using XML. I just need to know how to trigger the code to implement the XML.
What I have found is the IRibbonExtensibility, but that only seems to work for Visual Basic 6 and not in VBA.
Reply With Quote
  #4  
Old 12-05-2007, 09:29 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Quote:
Originally Posted by a3b View Post
How can I add Ribbon functionality to an existing .xla source while maintaining compatibility for earlier versions of Excel?
Technically you cannot within the same .xla. This is because Excel 2007 requires XML to be within a .xlam file, while Excel 2003 could not read such a thing. So we will have to create a dual-workbook solution... Stay tuned...

Quote:
Originally Posted by a3b View Post
I know how I have to change the Ribbon itself by using XML. I just need to know how to trigger the code to implement the XML. What I have found is the IRibbonExtensibility, but that only seems to work for Visual Basic 6 and not in VBA.
The code to trigger the XML is loading the .xlsm or .xlam workbook in the first place. So you either open it, or don't open it, to trigger or not trigger the RibbonX code.

Putting two and two together, this is what I would suggest:

(1) Create an .xla that contains all your operating commands. "MyPrintItMacro()", "MyCopyRangeMacro()", etc., but no commands regarding the use of the Excel 2003 CommandBars, nor anythning regarding Excel 2007 RibbonX. Let's call this the "MyMacros.xla" workboook.

(2) Create a separate .xla that has a reference to the "MyMacros.xla" workbook (use the Tools/References dialog box) and contains Excel 2003 CommandBar commands to call those macros. Let's call this the "Excel2003.xla" workbook. Note that by having a reference to the "MyMacros.xla", opeining up the "Excel2003.xla" workbook will automatically open up the "MyMacros.xla" workbook for you.

(3) Create a third workbook, this time an Excel 2007 .xlam workbook that also contains a reference to the "MyMacros.xla" workbook and contains Ribbon XML. Let's call this the "Excel2007.xlam". The Ribbon XML for this workbook will call subs within the "Excel2007.xlam", which in turn call subs found within the "MyMacros.xla".

This way you have all the functional code contained within ONE workbook, but two separate workbooks for the various user interfaces required.

Now you can install either the "Excel2003.xla" along with the "MyMacros.xla" or the "Excel2007.xlam" with the "MyMacros.xla" depending on which Excel version is required.

Hope this helps!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 12-05-2007 at 07:38 PM.
Reply With Quote
  #5  
Old 12-17-2007, 04:05 AM
a3b a3b is offline
Newcomer
 
Join Date: Nov 2007
Posts: 24
Default

Thanks Mike.
Since we control things from within our main application, I can maintain a .XLA as well as a .XLAM add-in.
Controlling the QAT is our main concern now. I guess from what I've read sofar, I cannot get complete control over it. I hope Microsoft will change this in a future service pack.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->