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

ID: Q123269


The information in this article applies to:


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 Lotus Cleaner Add-in

Use the Lotus File Cleaner add-in for Microsoft Excel 97. This Excel add-in helps to remove excess formatting 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.wk4" command to the File menu of Excel. The add-in makes this menu command available only after you open a Lotus spreadsheet.

The following file is available for download from the Microsoft Software Library:
Lotuscln.exe
For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:
Q119591 How to Obtain Microsoft Support Files from Online Services

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 discussed here are manufactured by vendors independent of Microsoft; we make no warranty, implied or otherwise, regarding these products' performance or reliability.

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 Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp
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:


  4. 
    Sub ClearExcessRowsAndColumns()
          Dim intLRow As Integer, intLCol As Integer, intRowCount As Integer
          Dim i As Integer, intLCell As Integer, wksWKS As Worksheet
          Dim blProtCont As Boolean, blProtScen As Boolean, blProtDO As Boolean
    
          On Error GoTo errHandler
          'Loop through each worksheet in the workbook.
          For Each wksWKS In ActiveWorkbook.Worksheets
             Application.StatusBar = "Finding Data Boundaries for " & _
             ActiveWorkbook.Name _
             & "!" & wksWKS.Name & ", Please Wait..."
             'Store worksheet protection settings and unprotect if protected.
             blProtCont = wksWKS.ProtectContents
             blProtDO = wksWKS.ProtectDrawingObjects
             blProtScen = wksWKS.ProtectScenarios
             wksWKS.Unprotect ""
             'Determine the last cell that Excel finds and determine its row.
             'If it is greater than 8192, limit the rows for variable memory
             'overload.
             If wksWKS.Cells.SpecialCells(xlLastCell).Row > 8192 Then
                'Last row for Lotus files is 8192.
                intRowCount = 8192
             Else
                intRowCount = wksWKS.Cells.SpecialCells(xlLastCell).Row
             End If
             'Loop through each row and determine the last cell with data.
             intLCell = 0
             intLCol = 0
             For i = 1 To intRowCount
                intLCell = wksWKS.Cells(i, 255).End(xlToLeft).Column
                If intLCell > intLCol Then intLCol = intLCell
             Next i
            'Loop through the columns and determine the last cell with data.
            intLCell = 0
            intLRow = 0
            For i = 1 To wksWKS.Cells.SpecialCells(xlLastCell).Column
               intLCell = wksWKS.Cells(8194, i).End(xlUp).Row
               If intLCell > intLRow Then intLRow = intLCell
            Next i
            'Delete the Excess rows and columns.
            wksWKS.Range(wksWKS.Columns(intLCol + 1), _
               wksWKS.Columns(255)).Delete
            wksWKS.Range(wksWKS.Rows(intLRow + 1), _
               wksWKS.Rows(wksWKS.Rows.Count)).Delete
            'Reset protection.
            wksWKS.Protect "", blProtDO, blProtCont, blProtScen
         Next wksWKS
         Application.StatusBar = False
    
         Exit Sub
    
    errHandler:
             MsgBox "An error occurred." & Chr(13) & Err.Number & " " & _
                Err.Description, vbCritical
        End Sub
       
  5. In Excel, open the workbook converted from WK4 format.


  6. In Excel 97, on the Tools menu, point to Macro, and click Macros. Select the appropriate macro name and then click Run. In earlier versions of Excel, on the Tools menu, click Macro, and 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."

For more information about obtaining the Lotus 1-2-3 WK4 File Converter, please see the following article in the Microsoft Knowledge Base:

Q122583 XL5: Lotus 1-2-3 Release 4 for Windows 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


Keywords          : kb3rdparty kbdta kbconversion kbdtacode 
Version           : WINDOWS:5.0,5.0c,7.0,97
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: July 16, 1999