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?
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.
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 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.