XL: Large File Size After Saving WK4 File as Excel Workbook

WORKAROUND

To work around this problem, use any of the following methods.

Method 1: Use the Excess Formatting Cleaner Add-In

Use the Excess Formatting Cleaner Add-in for Microsoft Excel 97. This Excel add-in helps to remove excess formatting in workbooks. It can also help to avoid a large increase in the size of your imported Lotus 1-2-3 WK4 file. This add-in adds the "Clean Excess Formats in file name" command to the File menu of Excel.

The following file is available for download from the Microsoft Download Center:
For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

Method 2: Use the Cleanmac Utility

Use the Cleanmac utility from Lotus 1-2-3 to help remove corruption in Lotus 1-2-3 spreadsheet files (1-2-3 r5 & 97). This utility is a .wk4 workbook and therefore requires that you run it in Lotus 1-2-3. After running the utility, save the file and then open it in Excel.

This utility is available at the following location on the World Wide Web: The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Method 3: Manually Delete Blank Rows and Columns

To avoid a large increase in the size of your Lotus 1-2-3 WK4 file, follow these steps before you save the file in Excel:

1. Select all of the blank cells below and to the right of the last cell that contains data on your worksheet. There is no automatic way of locating the last cell on your worksheet that contains data.
2. On the Edit menu, click Delete, and then click OK.
3. Save and close the file.

Method 4: Use a Visual Basic Macro to Delete Blank Rows and Columns

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: To create a macro to remove the blank cells, follow these steps:

1. Save and close any open workbooks, and then create a new workbook.
2. In Excel 97, on the Tools menu, point to Macro, and click Visual Basic Editor. On the Insert menu, click Module. In earlier versions of Excel, on the Insert menu, click Macro, and then click Module.
3. Type the following code in the module:

   Sub ClearExcessRowsAndColumns()
      Dim ar As Range, r As Double, c As Double, tr As Double, tc As Double
      Dim wksWks As Worksheet, ur As Range, arCount As Integer, i As Integer
      Dim blProtCont As Boolean, blProtScen As Boolean, blProtDO As Boolean
      Dim shp As Shape
    
      On Error Resume Next
      For Each wksWks In ActiveWorkbook.Worksheets
         Err.Clear
         'Store worksheet protection settings and unprotect if protected.
         blProtCont = wksWks.ProtectContents
         blProtDO = wksWks.ProtectDrawingObjects
         blProtScen = wksWks.ProtectScenarios
         wksWks.Unprotect ""
         If Err.Number = 1004 Then
            Err.Clear
            MsgBox "'" & wksWks.Name & _
                  "' is protected with a password and cannot be checked." _
                  , vbInformation
         Else
            Application.StatusBar = "Checking " & wksWks.Name & ", Please Wait..."
            r = 0
            c = 0
            Set ur = Union(wksWks.UsedRange.SpecialCells(xlCellTypeConstants), _
                  wksWks.UsedRange.SpecialCells(xlCellTypeFormulas))
            If Err.Number = 1004 Then
               Err.Clear
               Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeConstants)
            End If
            If Err.Number = 1004 Then
               Err.Clear
               Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeFormulas)
            End If
            If Err.Number = 0 Then
               arCount = ur.Areas.Count
               For Each ar In ur.Areas
                  i = i + 1
                  tr = ar.Range("A1").Row + ar.Rows.Count - 1
                  tc = ar.Range("A1").Column + ar.Columns.Count - 1
                  If tc > c Then c = tc
                  If tr > r Then r = tr
               Next
               For Each shp In wksWks.Shapes
                  tr = shp.BottomRightCell.Row
                  tc = shp.BottomRightCell.Column
                  If tc > c Then c = tc
                  If tr > r Then r = tr
               Next
               Application.StatusBar = "Clearing Excess Cells in " & _
                     wksWks.Name & ", Please Wait..."
               wksWks.Rows(r + 1 & ":" & wksWks.Rows.Count).Clear   'Delete
               wksWks.Rows(r + 1 & ":" & wksWks.Rows.Count).RowHeight = _
                     wksWks.StandardHeight
               wksWks.Range(wksWks.Cells(1, c + 1), _
                     wksWks.Cells(1, 256)).EntireColumn.Clear   'Delete
               wksWks.Range(wksWks.Cells(1, c + 1), _
                     wksWks.Cells(1, 256)).EntireColumn.ColumnWidth = _
                     wksWks.StandardWidth
            Else
               Err.Clear
            End If
         End If
         'Reset protection.
         wksWks.Protect "", blProtDO, blProtCont, blProtScen
         Err.Clear
      Next
      Application.StatusBar = False
      MsgBox "'" & ActiveWorkbook.Name & _
            "' has been cleared of excess formatting." & Chr(13) & _
            "You must save the file to keep the changes.", vbInformation
   End Sub
   					

4. In Excel, open the workbook converted from WK4 format.
5. In Excel 97, point to Macro on the Tools menu, and click Macros. Select the appropriate macro name and then click Run.
   
   In earlier versions of Excel, click Macro on the Tools menu. Select the appropriate macro name, and then click Run.