Someone sent me an email last week suggesting I have a Wishlist category where we could discuss features we hope MS will someday incorporate into Excel. I think that’s a fine idea. If you like, send me an email with your wish. If I find it sufficiently interesting, I’ll post it for others to comment.
For my first wish, I’d like a sheet in my workbook dedicated to External Data. I think someone on Simon’s blog suggested this in a comment once (maybe Harlan). The worksheet would be a recordset of an external data source and nothing else. Users would not be able to type anything outside the recordset or change much about the sheet. Here’s the kicker: Excel would keep the sheet and the external data source in sync. When you write to a cell on the sheet, the external data would be updated. If the recordset isn’t updateable, you wouldn’t be able to write to the cells. If you try to put a string in a cell that’s linked to an external data field designated Integer, you would get an error. Basically it would be like datasheet view in Access. Now I handle all this will class modules and ado, but it would be nice to stay within Excel’s object model to accomplish the same thing. Not to mention the UI.
One prediction I made at the recent Excel Users Conference in Sydney is that Excel and Access will be one product in our lifetime (well mine anyway). Have you seen Access 2007? Do those dropdowns in datasheet view look familiar? “But Access is a database”, you say. No, not really. Access is the front end to a database. In my world, Excel is usually the front end to a database. It’s not that I don’t like Access, there are just people who are better at it than me, so I generally avoid that kind of work. The recent datasheet view changes in Access 2007 brings that prediction one step closer. My external data worksheet wish would bring it closer still. The only question that remains is whether to call it Accel or Excess. I prefer the latter.
You can leave your own wish in a comment (or email as noted above), comment on my wish, or leave a comment with what you would name the new combination Excel/Access product.
Dick –
Excel + Access = Excess?
(Hope I’m the first) ;-)
…Michael
Dick, there is already the beginnings of an Excel Wishlist over at Excel Users Group. It is the V.Next forum at http://excelusergroup.org/forums/66.aspx.
Michael- Not the first my a long shot. Sorry.
Jon –
I knew…I knew ;-)
Dick: I agree!
I’ve always believed this was missing from vendor offerings.
We seem to have a good selection of reporting tools (eg. crystal reports, etc..), but not many choices for forms tools.
Usually I’ll resort to writing forms outside of Excel sheets (ie. ExcelVBA Form, or .NET Winforms).
The special sheet should be able to handle what Access already does:
– A simple way to edit in master-detail layout
– Filtering
– Sorting
– Grouping with Subtotals
I often ask the local access experts to show me 3 things that can be done in Access and just cannot be done using Excel….
I am still waiting for the list…
I can see why you would think Excel and Access would become one and the same application but I doubt that will happen. First of all, there are some differences between Excel and Access. For example, Access has the ability to ‘link’ tables and enforce referential integrity. Or if you look at a form in the VBA editor you can find some that are listed as a class object (forms created in the IDE) and some listed as forms like we know from Excel (created in the VBA IDE).
All of those can be overcome of course, MS could probably join Word and Excel as well (Wordcel or Worxell?). But the main reason why they wouldn’t link up Access and Excel is simply because (I assume) they make more money selling them apart. MS Office Pro is significantly priced higher than MS SBE or Basic. Why change that?
Hello,
I’m a big fan of the Excess concept.
Rembo, Word+Excel, cant make as much sense as Jet+Excel, surely?
In fact drop Jet and Access, keep and update the visual query builder, make reprots about 10000 times better and use sql X, bung in VSTA, and it’s a new super product, thats why MS will do it. – Maybe they will end make is “web service able” – i.e they could tire the product rather than the suite.
Excess could be the Excel of the 00’s. (or the Access of the 00’s) if you like.
I think MS might do this, maybe make a new product from the groud up, based on .Net, becuase with things like Blist around they might have too.
I’ve actually written to folks at Microsoft suggesting that the Table functionality be extended in ways such as follows:
1) Provide a new sheet object called a Table sheet, which will hold a single Table and act much like an Access 2007 datasheet.
2) Table columns will have new properties, such as explicit data type definition & indexing.
3) It should be possible to define relationships among multiple table sheets and set referential integrity for the relationships.
4) The column header should contain the Table’s sort and filter dropdown. Currently, if you scroll down a Table in Excel, the field names appear on the worksheet column header. However, you still have to navigate all the way to the top to access the sort and filter dropdown, so in my case, I end up freezing the field names.
5) You should be able to link form controls to fields in one or more Tables, even if the Table(s) exist in a closed workbook.
6) Table data should be linkable to external data sources, just like Access.
7) The ACE engine should recognize a Table name as a table in a workbook. Unbelievably, ACE currently doesn’t recognize Table names at all.
8) Of course, the ancient MS Query must be replaced with an integrated solution.
There’s a lot more, but I’ll stop here. Like Dick, I use a fair bit of ADO to back-end data entry forms created in Excel and this in part motivated my document to Microsoft. I may have also been inspired by the Access 2007 datasheet and wanting something to do with 1M rows!
Perhaps a better solution would be to bundle an Access “Express Edition” in the Standard version of Office, which would have deep integration with Excel. This edition would be without features designed for multi-user environments, such as the ability to create runtimes, ADPs and so on. Assuming that the database capacity in Access 2009 will be much larger, the Express Edition would have the size of the current database.
“From the ground up”
Really think so? That’s a major ten-year project. I can’t see the cost-benefit analysis allowing that. As someone pointed out, they get better revenue with two separate products than is likely with a new broader product. They couldn’t just replace Excel and Access with a new product, and as long as those are still available, people would be reluctant to buy this duplicate as well.
“3 things that can be done in Access and just cannot be done using Excel”
I can think of only two, but they are really major issues:
– Relational databases
– Multi user environment
Dick, I’m sure you’ve mentioned this before, but how do you create your databases/recordsets. Is it all done through VBA?
“in our lifetime (well mine anyway)” What are you trying to say here? How many years do I need to hang on for? :)
I think DK is generally correct that we will all end up with a single product, but it’s won’t be anything we recognize today.
It will be a SharePoint based tool that will be more like the web services we see now (A la Zoho, or Web Office). SharePoint is currently a billion dollar business for MS and I think they really see the hosted environment as the future.
Each version of Office will have “New” features that will be essentially move closer to that end. For example: Integration with SharePoint lists, XML file structures, tighter integration with SQL Server, Excel Services.
For my part, each new feature in Office I see, I will ask myself how this relates to the SharePoint path.
I think DK is generally correct that we will all end up with a single product, but it won’t be anything we recognize today.
I believe it will be a SharePoint based tool that will be more like the web services we see now (A la Zoho, or Web Office). SharePoint is currently a billion dollar business for MS and I think they really see the hosted environment as the future.
Each version of Office will have “New” features that will essentially move closer to that end. For example: Integration with SharePoint lists, XML file structures, tighter integration with SQL Server, Excel Services…etc.
For my part, each new feature in Office I see, I will ask myself how this relates to the SharePoint path.
Wishlist? Very, very long list… I suppose MS has already such a list, but they use it as ideas for new products. What would be nice – to have a new version not only having more features and possibilities, but also not less… Just to mention Report Manager, Track Down Data Tracking, WebForms (only in XL97) or Map Tool (some more about XL XP at http://cws.internet.com/article/1818-.htm). Map Tool is sold now as a separate product – MapPoint. So the MS way is not to merge products.
The next thing is ability to use all features with no need to buy their other products like Windows Server, Exchange, Sharepoint or something. Now you have to explore documentation thoroughly to get know that you need more than just Excel(=Office suit), Infopath or another single product to run certain features.
My suggestions for the wishlist: multiuser environment, enhanced safety options – just for the beginning.
“So the MS way is not to merge products.”
The MS way is to avoid having one product cannibalize sales of another. For a while with Office, especially Excel, new versions had little to attract new sales, and installed base was the largest competition for each new version. Office 2007 is just different enough, or perhaps just incompatible enough, to drive more new sales than usual.
Products are not merged so much as new ones are accreted onto old ones, like SharePoint and OneNote were duct-taped onto Office. Since Office already contains Excel and Access as stand-alone but integratable parts, merging makes no sense, as it removes some of the perceived benefits of higher end SKUs (where Access is bundled).
Harald
Re “Multi user environment”
Once you read from a Excel file and Add/modify/update an Excel file without “opening” it (using ADO) then we have it ready for a multi user environment
Re Relational databases
Can be done. Again via VBA + ADO
Sam
Sam
Yes, if you use ADO from app X then it doesn’t matter much whether the file extension is .xls or .mdb and nobody cares. And of course, one can do anything with Excel and a little code.
But we’re speaking applications: Excel can’t offer any assistance at all in these matters. It’s like teaching a cat to bark when you need a watchdog. Stunning if you manage, but still pointless imo.
I dunno about Sharepoint. I’ve installed and tested it to some extend but it wasn’t the walhalla I hoped for. The whole thing is based on SQL Server (yet another database in the equation) and you need a BIG computer to be able to run it properly. Not to mention a MS Server version. Although Sharepoint definately has some advantages for (working in) groups it also makes things less easy to manage. I know, a contradiction in terminis because isn’t the whole (Share)point to make the managing of documents in groups easier?
To the infidels that praise Sharepoint I say try solving a bug, fix a broken link or locate a missing document. Yes, that happens. Well, at least in the earlier versions. If you don’t know to work with SQL Server this can be a real pain in the posterior. It is far more easy to locate a physical Excel of Access file. I don’t see any Sharepoint like solution being used on home computers at all.
I agree with Harold that Access has some valuable assistance tools when creating database applications. You’d have to add them to Excel as well. Everything can be mixed up together but like Jon says; whey would M$ do that if they can make more money selling them apart?
Another logical question is if you would really want to have all the Access menus in my Excel program (or the other way around). It wouldn’t exactly make things easier to overlook.
>>Really think so? That’s a major ten-year project. I can’t see the cost-benefit analysis allowing that.
Yeah fair point, it would be a massive piece of work. Maybe then a drip feed type approach might work. But it would be much better to start a fresh, even if they can reuses a lot of the org code base. It’s a tricky one.
>>The next thing is ability to use all features with no need to buy their other products like Windows Server
Yes, I think that to use the new stuff MS is pumping out is too involved, theres to much that needs this, or needs this, or what have you.
Again though, you have to be fair to MS, their trying to make sure they have a foot in the door of the next big move. If web apps/sever base products become the norm, then they should be well placed. Sadly at the mo its just not working very well.
Basically I think it’s hard to be Microsoft!!! ;-)
Think about this, what is MS biggest strategic asset? .Net? I read some where that office makes MS 1 billion dollars a week? So MS get about 70% + of there dosh from office (I’d guess), but this is a com product?
How do those two things line up!?!
Looks to me like something will have to give?
For those who feel facinated by the schism between the relational world and the object world: take a look at this amazing .NET discussion:
http://www.panopticoncentral.net/archive/2005/03/25/8128.aspx
In terms of MENU COMMANDS, Excel is gaining more database functionality. But in terms of FORMULAS, Excel is stuck in mid-1980s functionality. The SQL.REQUEST add-in function used to be bundled with Excel, and it addressed this need for database functionality incorporated into formulas and automatic recalculation. But it hasn’t been bundled with Excel since Excel 2002, so it’d seem Microsoft is effectively if unofficially deprecating it.
Basically, it seems Microsoft is trying to turn Excel into a grossly oversized grid control.
Here is an interesting article on SharePoint that should give us a clue on where Office is going.
It says:
“Microsoft Office SharePoint Server (MOSS) 2007 is the fastest growing product in the company’s history and seems to have as many uses as a Swiss Army knife. Its six focus areas are collaboration, portal, search, enterprise content management (ECM), business process management and business intelligence.”
http://www.networkworld.com/news/2008/032608-microsoft-sharepoint.html?page=1
Pardon my ignorance.
How do you put a dropdown control object?
Please provide an ignorant user detailed instructions.
rmnslv@yahoo.com
I’ve spent literally half of last years’s work hours with MOSS2007. It is a great tool and it gets far better by each version. No 2000-XP-2003 upgrades.
We all remember how html was supposed to change office work, and then it was smart tags, and then it was xml. So I probably should know better, but I believe SharePoint will change office work and that the next version is a real killer app. Or the version after that.
Dick
links to relevant posts on SOS:
http://smurfonspreadsheets.wordpress.com/2007/02/22/structured-data-sheets/
http://smurfonspreadsheets.wordpress.com/2007/02/20/accel-or-excess/
I don’t know about them combining, I truly believe MS themselves don’t know what to do with Access -its the product that won’t die. And now they seem to be investing in it again.
If they updated MS query to something from this century that might be enough.