XL: Visual Basic Macro to Convert Lotus Files to MS Excel

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

SUMMARY

Microsoft Excel versions 5.0 and later can read Lotus 1-2-3 worksheets. In addition, you can create a Visual Basic for Applications procedure to automatically open each Lotus 1-2-3 file in a specified folder (directory) and save it as a Microsoft Excel workbook file (.XLS).

The macro example in the "More Information" section of this article demonstrates a looping procedure you can use to perform this action. The example provided does not delete the original Lotus files, and it will prompt you if a file with the same name is about to be overwritten.

NOTE: If you are using Microsoft Excel version 5.0 or 5.0c, you need to use the "Lotus 1-2-3 WK4 File Converter" in order to read Lotus 1-2-3 version 4.0 files. This converter is available as an Application Note. For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q122583
   TITLE     : XL5: AppNote: Lotus 1-2-3 WK4 File Converter (WE1130)

MORE INFORMATION

Microsoft provides examples of Visual Basic procedures 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 Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

To insert a Visual Basic module into a workbook in Microsoft Excel versions 5.x and 7.x, click the Insert menu, point to Macro, and click Module.

To insert a Visual Basic module into a workbook in Microsoft Excel 97, click the Tools menu, point to Macro, and click Visual Basic Editor. Then, click Module on the Insert menu.

Once you have inserted a module into your workbook, enter the following code into the module:

   Sub LotusConverter()

       'Set "filenam" to the first matching file in the current folder.
       filenam = Dir("*.wk*")

       'Loop to open each matching file in the current folder.
       Do While Len(filenam) > 0
 
           On Error Resume Next

           'Opens the file.
           Workbooks.Open Filename:=filenam

           'Continues execution if there was no error opening the file.
           If Err = 0 Then

               'Creates "newname" based on original Lotus file name.
               newnam = Left(filenam, InStr(1, filenam, ".") - 1) & ".xls"
               'Saves the new file as a Microsoft Excel normal file.
               ActiveWorkbook.SaveAs Filename:=newnam, FileFormat:=xlNormal
               'Closes the current file.
               ActiveWorkbook.Close
           Else

               'Display message if opening filenam was unsuccessful.
               MsgBox "Unable to Open file: " & CurDir() & "\" & filenam
               'Resets error checking.
               Err = 0

           End If
   
           'Gets the next file name
           filenam = Dir()
           'Repeats the loop for all matching files in the current folder.
       Loop

   End Sub

The macro will proceed without your intervention unless a duplicate file name is found (in which case you will be prompted to change the duplicate file name).