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

ID: Q115343

The information in this article applies to:

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:

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

MORE INFORMATION

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 the Microsoft fee-based consulting line at (800) 936-5200. 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 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.

After 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

Before you run the macro, make sure the folder where the Lotus 1-2-3 files are located is set as the current folder. In Microsoft Excel 97, on the Tools menu, point to Macro, and click Macros. Then, click LotusConverter in the list of macros, and click Run. In Microsoft Excel version 5.0 or 7.0, on the Tools menu, click Macro, click LotusConverter in the list of macros, and then click Run.

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

Additional query words: 97 batch converter multiple files

Keywords : kbprg kbdta KbVBA xlwin
Version : WINDOWS:5.0,7.0
Platform : WINDOWS
Issue type :