Hands on: Import and export data from Excel spreadsheets

Four options for dealing with the ins and outs of the software

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.

You are offered a wide range of options under Open on the File menu (Office, Open, in Excel 2007). You can import data straight from Access and dBase files. With other files just compare their extension with the numerous extensions offered by Excel. Some bring in files from other spreadsheets.

Depending on your version of Excel you might find options to import files from Lotus 1-2-3, Quattro Pro, Foxpro, Oracle, Paradox and Microsoft Works. Some formats are maintained from days gone by like SYLK, the Symbolic Link format used by Multiplan and DIF, the Data Interchange Format used by Visicalc. When all else fails most other programs will export a text file.

Choose the option, Text Files (*.prn; *.txt; *.csv), select the file and Excel will open the Text Import Wizard offering numerous options for laying out the file on a worksheet according to your wishes (see the attached picture). You’ll notice there are other options for importing data on the Data menu (Data tab in Excel 2007).

Data imported this way is linked to the original source, whereas data imported using File, Open is simply a copy of it. In versions of Excel prior to Excel 2007 you can interact with an HTML web page. You can open an HTML file in Excel 2007 but for security reasons and because the interactive feature didn’t work with browsers other than Internet Explorer, that’s it.

The interactive feature has been replaced by Excel Services, which requires Office Sharepoint Server 2007. For more information about this see, ‘Publish a workbook to Excel Services’ in the Excel 2007 Help files. If you want to import data from a source unrecognised by Excel you can probably find a driver or file converter from the publisher of the software.

Mail merging
One common way of exporting data from a spreadsheet is to use mail merging. It’s useful when you wish to send the same letter to a number of people, be they clients, club members, sales prospects or councillors. With Excel and Word it’s particularly easy because Office has a Mail Merge Wizard. Start by creating a mailing list on an Excel worksheet.

The top row will have headings such as Title, First Name, Surname, and so on. These are the fields of your database. Each row will be a record and contain the name and address of one recipient. It will be easier later if you include the house number in the same field as the street rather than make it a separate field. Save and close that file.

Complete a Word letter template and close that new document too. Open Word and choose Letters and Mailings, Mail Merge Wizard on the Tools menu. In Step 1 choose Letters. In Step 2 choose Start from existing document and pick your new letter from the displayed list. In Step 3 click, Use an existing list. Browse to your Excel file and then the right worksheet.

Oddly, the field names and their columns may be in a different order. You can drag them around into the correct order with the mouse but it hardly matters as Step 4 gives you an opportunity to match the field names Word expects in an address block with the field names you have used.
Follow through with the rest of the Mail Merge Wizard to Preview, Save and Print your letters.

Now hear this
The most common ways of exporting data from an Excel file are by saving it to a storage source or printing it out. But you can also have audio output. If your PC has a sound card, and your speakers are switched on, you can have Excel proof-read your cell entries back to you.
On the View menu choose Toolbars and then Text to Speech.

(By default, the facility isn’t available in Excel 2007 and is undocumented. To add it, click on the down arrow at the right of the Quick Access Toolbar at the top left of the screen. Choose More Commands, Commands Not in the Ribbon. Click Speak Cells and the Add button, then Stop Speaking, Add. Click OK.)

Highlight the range of cells you wish to have read back to you and, on the toolbar, click Speak Cells. If the voice is male and you would rather have a lady read to you go to Start, Control Panel and choose Speech. (Text to Speech in Excel 2007.) Under the Text to Speech tab you can change from Michael or Sam to Michelle. The quality is much better in Excel 2007.

In prior versions all the voices sound similar to Stephen Hawking’s familiar speech machine. The procedures described here may vary slightly in different versions of Excel but not in any significant way.