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

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

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

Lotuscln.exe

Q119591 How to Obtain Microsoft Support Files from Online Services

Method 2: Use the Cleanmac Utility

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

Method 3: Manually Delete Blank Rows and Columns

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

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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

4. In Excel, open the workbook converted from WK4 format.



5. 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

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