Microsoft KB Archive/123269

= 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 " 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:  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 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  In Excel, open the workbook converted from WK4 format. 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.

<div class="moreinformation_section">

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.

<div class="references_section">