Once upon a time there was DOS.

Everything worked in memory. Aka random access memory, or RAM. And relative to what we’re used to today, there wasn’t much. Although we didn’t think so at the time. (Today 8Gb is normal)

64k was a lot. 128k was even bigger!

The PC could only have one application running at any one time. So you worked with your Lotus 123 spreadsheet, or Microsoft Multiplan (as I did), or your word processor, which might have been Multimate or Work Perfect. Ashton-Tate dBase II to have your structured data. To switch from one to the other was a pain – because it meant closing one program and opening another. If you needed to copy some data across, we had to print it out on one of those 132-characters-wide dot matrix printers. Some people figured out how to print to a text file, but those were the rare geniuses of the day.

But we survived despite the inconvenience.

The Integration

Then in the mid-1980s the clever people at Microsoft and Lotus Development, and Ashton-Tate, came up with the idea of having all these different functionality within the RAM all at the same time. We were introduced to Integrated Software. So it’s all in one application, but with all the various different ‘paradigms’. Even Sir Clive Sinclair got in there with the Sinclair QL.

We had Lotus Symphony (an apt product name considering the rationale), and suites such as Smart (not much heard of today) and Ashton-Tate’s Frameworks. (Roger Gear-Evans, where are you?)

Around that time we also had memory-resident little tools like Sidekick and (I had) Lotus Metro. These helped in this move towards ‘integration’, because they sat in memory, quite lite. They were running in the background, even though some other application was running in your main work, and we could use it much like we use the Clipboard today.

Then came Windows, hard on the heels of the Apple Mackintosh that pioneered the graphical user interface (GUI).

An aside. Did you know that Multiplan, Microsoft’s predecessor to Excel, was the leading business software to go with the launch of the Apple Mackintosh? This was around 1984. The Mackintosh (I don’t think anyone said ‘Mac’. That branding came in the 1990s with the return of Steve Jobs to Apple) was already with a point and click with mouse, while the PC chaps were still in DOS with screens with green dots in 80 columns and 25 rows. When Excel was first released in 1988-ish PCs were still in DOS and Excel was only available on the Mackintosh. By Excel version 2 Windows was here, so the first version of Excel for Windows was version 2. There. Now you know.

Anyway, back to the plot.

The Disintegration

It’s now 1990. We’re getting to the good part.

With Windows, of course, we could have more than one application running at the same time. That was the point of Windows. So, what did that do to the Integrated Software idea?

It was no longer necessary to have spreadsheet, database, wordprocessor and graphics in the same application software suite. Why? Because the whole point about Windows is that the separate applications can all run as separate ‘packages’ all at the same time (yeah, we used to call them ‘packages’ too. Not only Linford Christie ran with a package!)

Meaning, we no longer had to close one and open another.

That meant that Silicon Valley could develop each of the various paradigms – spreadsheet, database, wordprocessor etc – without having to compromise as they necessarily had to in Integrated Software.

So Excel improved without having to also be a wordprocessor and database. And Word was designed and built as a wordprocessor. Access came along around 1993, to work as a database, and to specialise as a database. There was Harvard Graphics for graphics to give us the slides paradigm.

That’s great. But it also now brought back an old hurdle. Whereas when we had all of these Integrated we worked seamlessly between spreadsheet, database, word processing etc now that they were separate we were reduced to …. yes, you guessed it …. copy and paste!

Which is of course a manual exercise.

Oh dear!

The Reintegration

But this was quickly solved. If you were around in the mid-1990s you might remember Microsoft Query. This was functionality in Excel and other Office applications that enabled importing data from other applications.

Although great, like copy paste and the other import/export features, MS Query required filling in a wizard, which meant … manual work. The best automation we could do on this is to store the query and run it, but it was limited in practice because each time you use it you may want to change something, and this meant usually a new query.

So, Integration was there, but still … er … manual.

But, and here’s the biggie. Microsoft’s launch of Office 97 and 2000 made a big thing of this – the key ingredient of MS Query was made accessible to Excel users, and other Microsoft products which all had this little piece of magic shipped inside it.

It’s called ADO. It’s been in Excel for over 20 years. It has the plumbing that’s needed for any (or most) Microsoft products to connect with other software. That means, all your databases in your organisation, as well as your Excel and your Access, and Word and PowerPoint.

So, once again we had Integration. Or as I call it Re-Integration. At long last.

(By the way, ADO stands for ActiveX Data Objects.)

But, and here’s the big but – it’s been more than 20 years and not many people know that.

Much of my work in the past 25 years was made possible by this magic. Many of the solutions I have written about and demonstrate involve using ADO to connect. You’ll see how to add value, not just by making ourselves more productive, but by making everyone else more productive also.

It’s the integration features of Excel that makes powerful new features such as PowerQuery possible. But instead of just ‘GET’, the feature makes it possible to ‘PUT’ also. That means Updating, Inserting new data, and Deleting existing data. (See WTF to OMG! to see what this means to the price of apples! That’s an old English saying BTW)

What’s the point?

What does integration mean?

The answer would depend on whether or not you know how value is created by separating the data from the spreadsheets. A place to learn is my article The Magnificent Seven, and the Sunday Volunteers demo.

Then you will see that we no longer need to hold data in Excel for Excel to work with that data. In fact, it is Easier for Excel to work with data that is stored in tables located outside the spreadsheets – in Access tables, SQL Server, on the Cloud etc. In 2021, the connection of Excel with externally located tables is now complete.

To see this is action, take a look at the principles of Dynamic Spreadsheets (aka. The Magnificent Seven article, and the Sunday Volunteers demo model). Or attending an Excel Happening where you get to play.

See also:

Why it’s good that not many people know about ADO! (A bit cheeky)

How to get your boss promoted.

Hiran de Silva

View all posts

Add comment

Your email address will not be published. Required fields are marked *