Microsoft Excel - replacing ActiveX Controls
Posted on 2014-03-12
Our company develops a series of Excel workbooks that pull data from a client’s SQL database based on user-defined parameters. To implement the parameters, we currently use ActiveX controls, specifically text boxes and a “master” list box.
The “master” list box is populated from Excel tables (which are populated from SQL Server using typical master files (e.g., companies, projects, customers)) and the content of the “master” list box varies depending on the ActiveX textbox selected (using simple VBA code). Users can select single or multiple items from the “master” list box (as well as type in partial text strings including the use of wild cards); the items selected are then pushed to the Linked Cell associated with the ActiveX textbox. We then pass the values in those linked cells as parameters to SQL Server stored procedures to return the requested report data to Excel.
Here’s the dilemma: I have been tasked with removing all ActiveX controls from the workbooks for future product releases. According to my boss, Microsoft has stopped development of ActiveX controls for Office and these will soon be deprecated or no longer supported (Office 365)…btw, if anyone has any information on this subject that would be helpful. I can’t find anything that indicates that ActiveX controls are on the chopping block, but my boss is a really smart guy which is irrelevant because I have to do what he asks regardless of his intellectual capabilities.
My main considerations for evaluating a replacement technology include:
• compatibility with Office 2010 (at least for the next 2-3 years), Office 2013 and Office 365
• the application is currently a on-premise desktop application, but will quickly have to be available as a web-based application, including on mobile devices
I’m knee-deep waist-deep into researching this and have learned a lot about how much I have yet to learn (geez, who can keep up?). Apps for Office, Task Pane vs. Content apps, XML customization of the Office Ribbon (my favorite if I can figure out how to do it) – so much to chew on.
Here’s my question (finally): what method / technology would you use to implement the above functionality in lieu of ActiveX controls?