O'Reilly logo

Excel Annoyances by Curtis D. Frye

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

IMPORTING DATA ANNOYANCES

COPYING A WORD TABLE INTO EXCEL 97 INTRODUCES BLANK ROWS

The Annoyance:

When I copy a table from a Word document into an Excel 97 worksheet, Excel insists on assigning each cell in the Word table to two cells in the worksheet (shown in Figure 1-7). Worse yet, it merges some of the cells. Please tell me there’s some way I can stop Excel from merging the cells! If not, can I at least undo the merges and delete the resulting blank rows after I paste my table?

For some reason, you get two rows for one when you bring a Word table into Excel 97.

Figure 1-7. For some reason, you get two rows for one when you bring a Word table into Excel 97.

The Fix:

David and Raina Hawley, the authors of Excel Hacks: 100 Industrial-Strength Tips and Tools (O’Reilly), wrote a great macro that removes blank rows from a selection. I added a section at the top of the procedure to remove text wrapping and cell merges from the imported list so that each row will be separate, allowing Excel to remove the blank rows. This macro assumes the data you imported is selected. If the list isn’t selected, click any cell in the list, press Ctrl- to select the list, and run this macro to clean up your data:

   Sub FixWordTableInExcel97()
   'Removes all cell merges and text wrapping from
   'the pasted table and then deletes all blank
   'rows added by the paste.

    With Selection
    .WrapText = False
    .MergeCells = False
    End With

   Dim Rw As Range
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False

    Selection.SpecialCells(xlCellTypeBlanks).Select

    For Each Rw In Selection.Rows
    If WorksheetFunction.CountA(Selection. _
    EntireRow) = 0 Then
    Selection.EntireRow.Delete
    End If
    Next Rw

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
   End Sub

David and Raina Hawley originally published the body of this macro (everything after Dim Rw as Range) at http://www.ozgrid.com/VBA/VBACode.htm. It is used here with permission.

DATA IN WORD FILES ISN’T AVAILABLE FOR IMPORT

The Annoyance:

I saved a file with comma-separated values in a Microsoft Word (.doc) file that I’d like to import into Excel, but Word files don’t show up when you select File Open and open the “Files of type” list. Why don’t they? And what can I do about it?

The Fix:

The reason you can’t import data from a Word file is because Word files contain a lot more data than what you see on the screen. If Excel actually imported a Word file, it would almost certainly stumble over formatting and other confusing data that only looks like a series of comma-separated values—and your worksheet would go haywire. The trick is to delete from your document anything that isn’t a comma-separated value. So, instead of saving the file in Word’s DOC format, choose File Save As. In the “Save as type” drop-down menu, select Text Only or Plain Text (depending on your version of Word), and click Save. This saves the document as a text file, which Excel will happily import.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required