PDA

View Full Version : Open Lotus 123 Files and save as Excel



Anne Troy
07-05-2005, 06:52 PM
Can somebody write some VBA that will open a folder full of Lotus files (*.123) and convert by saving as Excel (*.xls)?

That's all... just open and Save as.

Can we add it to the KB?

Anne Troy
07-05-2005, 06:52 PM
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21481296.html

sheeeng
07-05-2005, 09:29 PM
Is anyone undergoing on this vba? interesthing....


Sorry, DreamBoat.. I don't have a membership in expert exchange..Can't view the whole thread..:banghead:

Anne Troy
07-05-2005, 09:35 PM
Here's the question:


This question was posted back in 2002 with no solution posted - just emailed vbscript from what I can tell. I have over 1000 .123 files (not .wk? which excel will open) that need to be converted. We have a copy of Lotus 1-2-3 but not the time to manually open each 123 files and save as xls. I was able to find serval batch converters for wk? files but none for .123.

Anyone know of such a batch converter or script that can be used in Lotus or Excel or vb to batch convert all of these?

Here's the VBA somebody posted there:

Public Sub ConvertFiles()

Dim FileName As String

Const ConvertFolder = "C:\Documents and Settings\Kevin Jones\Desktop\Convert"

FileName = Dir(ConvertFolder & "\*.wk?")
While FileName <> ""
Workbooks.Open FileName:=ConvertFolder & "\" & FileName
ActiveWorkbook.SaveAs FileName:=ConvertFolder & "\" & Left(FileName, Len(FileName) - 3) & _
"xls", FileFormat:=xlNormal
ActiveWorkbook.Close False
FileName = Dir
Wend

End Sub

You can get a membership free, by the way. If you join, DO NOT PAY!! :)

sheeeng
07-05-2005, 09:40 PM
You can get a membership free, by the way. If you join, DO NOT PAY!! :)

Free? I thought it is written there that must pay before register? :doh:
Btw, I register for it, free of course.:rofl:


Lotus 1-2-3 is still available now? Any sample files? I'm interested in this research, but I do not have Lotus 1-2-3 in my pc..:banghead:

mjfernandes
03-22-2012, 12:13 PM
You can convert a .123 file using either
a) Lotus Symphony
b) OpenOffice.org (This is a freeware that you can download)

I have OpenOffice.Org version 3
From OpenOffice.Org Calc, you can open a .123 file and then click Menu option "File", "Save As".
In the Save As dialog box, select Save as Type to the version of Microsoft Excel file you need.

To Automate or run a Batch command and convert multiple files, you will need to perform additional steps:
1. Write a Macro in OpenOffice Calc to convert one file. It is VBA like, but different. The macro will take a filename as a parameter.
2. The next step is to be able to call the above macro in a loop.

I am working on both these above. For the second step, I plan to use a DOS batch command.

In all you will need a Freeware, write one or two macros. Takes time. No easy way around for a batch process.

Kenneth Hobs
03-22-2012, 01:05 PM
You should probably make a new thread rather than replying to a post that old.

If you had L123, I could make a LotusScript do that. I find that the conversions are not that good though. Lots of things can go wrong. Fonts are usually a problem.

Pilot5000
08-28-2013, 05:13 AM
how do you run this code can you provide some more explanation , is that code should be ruin from excel file or what sorry new to that

Kenneth Hobs
08-28-2013, 07:03 AM
There are many help sites that explain "How to run an Excel macro". Here is one: http://spreadsheets.about.com/od/advancedexcel/ss/excel_macro_5.htm

You can get similar help on how to add a Module. This is where you copy and paste the VBA code that Anne posted. http://www.jlathamsite.com/Teach/Excel_GP_Code.htm

Obviously, you need to change the string value for ConvertFolder. Note that that method only converts the WK? files in the ConvertFolder's path to XLS format. This works because you can do it manually. You can not change the WK? to 123 as that file format is not supported by Excel so the file would not open in Excel properly.

As was stated in this thread, you need to write and run a macro/script in another program to convert 123 files to XLS. The conversion will not be very good unless you have very simple data and simple formulas common to both applications.
1. LotusScript
2. OpenOffice Script

Here are a few things that will not convert and some tips to manually convert. http://www.bygsoftware.com/Lotus/LotusConversionCheckList.htm