Knowledge Base |

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

PSS ID Number: 123269

Article Last Modified on 7/10/2003

The information in this article applies to:

- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95 7.0
- Microsoft Excel for Windows 5.0
- Microsoft Excel for Windows 5.0c

This article was previously published under Q123269

## SYMPTOMS

In the products listed at the beginning of this article, when you open a Lotus 1-2-3 WK4 file and you save the file in the Microsoft Excel Workbook format, the file size may increase by a large amount.

## CAUSE

This behavior occurs if you apply formatting to an entire column or row in a Lotus 1-2-3 WK4 file, open the file in Excel, and then save the file as an Excel workbook. For example, if you select column C on a worksheet in Lotus 1-2-3, apply a Currency format, and then enter values in a few of the cells in column C, the file size may increase by a large amount when you open and save the file in Excel. This behavior does not occur when you apply formatting to an entire column or row in the file while it is open in Excel.

## 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:

Xsclean.exe

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

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:

http://www2.support.lotus.com/ftp/pub/desktop/123/utils/cleanmac.zip

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:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

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.

## MORE INFORMATION

To open Lotus 1-2-3 WK4 files in Excel version 5.0, you must obtain the Lotus 1-2-3 WK4 File Converter. The file converter is available in Application Note "WE1130 Lotus 1-2-3 WK4 File Converter."
Lotus 1-2-3 is manufactured by Lotus Corporation, a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance or reliability.

## REFERENCES

For more information about opening a document created by another program, search on the following text in Excel Help: opening files.

Additional query words: 5.00c big grow huge xl97 Lotuscln format cleaner XSFormatCleaner.xla xsclean exe

Keywords: kb3rdparty kbconversion kbdtacode kbprb KB123269
Technology: kbExcel500 kbExcel500c kbExcel95 kbExcel95Search kbExcel97Search kbExcelSearch kbExcelWinSearch kbZNotKeyword3