Hands on: Import and export data from Excel spreadsheets

Bearing in mind the frequency with which new versions of software lack backwards compatibility, it was heartening to receive a message from Michael Hart: “Since the days of Multiplan, father of Excel, I have been running a portfolio system for share valuations with many intersheet links which depend on picking up share price information, using DDE links, from Updata’s Technical Analyst program.”

Fancy that. Imagine anything that would run with any version of Excel all the way back to its predecessor. It’s true that Michael was writing because he found that the DDE links wouldn’t work with Excel 2003. I referred him to the Office 2003 Editions Resource Kit in the Microsoft Support Knowledge Base.

But it got me thinking about other ways of entering data in a spreadsheet instead of using a keyboard. Dynamic Data Exchange (DDE) was very popular at one time. It was a protocol for exchanging data between Microsoft Windows-based programs that is now considered insecure.

The User’s Guide for Excel 4 described how to write DDE command macros to start, send data to and retrieve data from other applications. You could even direct Excel to send keystrokes to other applications. These days Microsoft recommends using object linking and embedding. It’s safer because self-written macros aren’t involved. The instructions are included in Excel’s code.

Alternatively, you can import or export data. Embedding and linking let you put a document from one Windows application into another. Both documents remain in their original format though.

If you embed an Excel table in a Word document you can still use Excel to edit the embedded table. If you use linking, the copied information can be refreshed when the original Excel table is changed. You might have a Powerpoint presentation that relies on information from an Excel worksheet.

When you update the data on the worksheet, the presentation can automatically be updated if they are linked. If you have no reason to combine documents but want to use data from one application in another then importing or exporting is more appropriate. You might have a list of names and addresses in a Word document but want to create a simple database from them.

You can either export them from Word to Excel or open Excel and import them.

Embedded reports
Embedding data from Excel into a Word document is easy. You might have a Word document that will eventually become a page in an annual report and an Excel chart of comparative sales figures. Open both files. Right-click on the chart and choose Copy. Switch to the Word document and click where you want the chart to appear. On the Edit menu choose, Paste Special.

In the displayed dialogue box choose Paste or Paste link and Microsoft Excel Chart Object. The chart will appear in the word document with a Smart Tag. This offers you the choice of having just a picture of the chart, an embedded chart, or a link to the original.

If the chart is linked, and you change a value on the original, the change will immediately be reflected on the chart in the Word document. If the chart is embedded, there is no link but Excel facilities are available so you can make changes to the chart in the Word document.

If you have chosen to have just a picture of the chart then it becomes a graphic and there are no Excel editing facilities.

Importing
Apart from DDE and OLE (Object Linking and Embedding) there are other ways of transferring data into an Excel file, including ODBC (Open Database Connectivity), OLAP (On-Line Analytical Processing), and SQL which is a computer language for retrieval of data developed by IBM in the 1970s.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!