Excel: Cell Alignment Formats Cause Flatfile Export to Fail

• Microsoft Excel for Windows, versions 3.0, 4.0
• Microsoft Excel for the Macintosh, versions 3.0, 4.0

SUMMARY

In Microsoft Excel, exporting data from your spreadsheet to a text file with the Flatfile add-in macro (Flat File on the Macintosh) may fail if the selected range contains cells formatted with any of the following alignment formats: Fill, Justify, or Center Across Selection.

Typically, one of the following symptoms results:

• Some or all of your data is missing in the text file.

  -or-

• You receive the error message "Error writing to disk."

MORE INFORMATION

The Flatfile add-in macro provides a means for exporting worksheet data to a text file using flat file format. Flat file format is a text format in which the data is separated by spaces rather than commas or tabs. You can also choose to retain the cell formatting of the exported data.

Flatfile exports data using a fixed-width field. This means that any data that extends beyond the borders of the cell that contains it, is cut off at the whole number width of the cell. For example, from a cell with a column width of 12.5, only the first 12 characters are exported.

Workarounds:

Workaround 1

The easiest way to work around this limitation is to export from a copy of your spreadsheet that doesn't contain these alignment formats.

To create a copy to export from:

1. Select your spreadsheet, or the range of data you want to export.

2. From the Edit menu, choose Copy.

3. From the File menu, choose New.

4. Select Worksheet from the list, and then choose the OK button.

5. From the Edit menu, choose Paste Special.

6. In the Paste Special dialog box, select the Formulas option and then choose OK.

   Excel pastes your data to the new worksheet without any formats.

7. Format your new worksheet using Number, Date, Currency and Time formats with Right, Left and Center alignment formats only.

   Use the resulting formatted worksheet to export your data with Flatfile.

Workaround 2

You can modify the add-in macro file to make it compatible with the Fill, Justify and Center Across Selection alignment formats.

To modify Flatfile to export without error:

1. Open FLATFILE.XLA (Flat File on the Macintosh) while holding down the SHIFT key.

   Note: Holding down the SHIFT key causes Microsoft Excel to open the add-in file for editing.

2. Unhide FLATFILE.XLA (Flat File on Macintosh) by choosing Unhide from the Window menu and selecting Flatfile from the list of hidden documents.

3. Make the following change in cell B86 of FLATFILE.XLA (Flat File on Macintosh):

   Change:

   B86: =IF(GET.CELL(8,mcp01r.Cell)=1,

             IF(ISNUMBER(mcp01r.Cell),3,1),GET.CELL(8,mcp01r.Cell)-1)
   
      To:
   
      B86: =(IF(OR(GET.CELL(8,mcp01r.Cell)=1,GET.CELL(8,mcp01r.Cell)>4),
             IF(ISNUMBER(mcp01r.Cell),3,1),GET.CELL(8,mcp01r.Cell)-1))
   
   

REFERENCES

"User's Guide 2," version 4.0, page 152