Excel Annoyances

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?

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.

ASAP: HANDY UTILITY FOR IMPORTS

If you import a lot of data into Excel, whether it’s text, database, or Excel files, you’re probably tired of wrangling with the Text Import Wizard just to set a few options. Instead of running the wizard every time, or taking the time to create a macro for the most common tasks, download the free ASAP Utilities collection from eGate Internet Solutions at http://www.asap-utilities.com/.

ASAP, written by Bastien Mensink, contains VBA routines that import data with the appropriate standard delimiting characters, import several space-delimited text files into a workbook, or combine space-delimited text files into a single text file. The package also contains a wide range of additional tools that can help you select cells that meet a given criterion, copy a worksheet’s print settings, quickly export a selection to HTML, delete all the numbers in a set of cells (while leaving all alphabetical characters intact) or vice versa, and more.

USE THAT HARD-TO-EXTRACT DATA

Data from any program that can export comma-delimited text files is fair game for importing into Excel. But some applications (particularly mainframe apps, non-Windows-based programs, and applications with data security features) produce datafiles that are difficult to bring into Excel. If you work with programs that frustrate your importing desires, turn to the Import Wizard add-in from http://www.beside.com. What is the Import Wizard’s clever trick? When just about any application prints, it spools the file to disk. The Import Wizard cracks into that print spool file, and generates an Excel table from the data. You have to define an import model for each type of report, but the program has a wizard that walks you through that process. Import Wizard lets you filter data before it’s imported, define non-Excel date and number formats, import data from multiple rows into a single row, and use VBA functions to create your own import macros. You can download a 30-day fully working copy; after that, a single-user license will set you back $149.