Visual Basic Macro to Convert Lotus Files to MS Excel (115343)

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.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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).