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

ID: Q123269

The information in this article applies to:

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 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. Click the file name below to download the file:

Xsclean.exe

For more information about how to download files from the Microsoft Download Center, please visit the Download Center at the following Web address

http://www.microsoft.com/downloads/search.asp

and then click How to use the Microsoft Download Center.

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 in this article 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:

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.

On the Edit menu, click Delete, and then click OK.

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:

Save and close any open workbooks, and then create a new workbook.

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.

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

In Excel, open the workbook converted from WK4 format.

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 Lotuscln format cleaner XSFormatCleaner.xla

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