Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



Convert files from wr1. to xls

Posted by Mark on February 14, 2002 1:43 AM
Hi,
have some files (>400 files) which are in .wr1 format, I wish to convert them to .xls .
Convert one by one will be time consuming. Could anyone have an idea that possible to convert those file in one time.

Thank you.

Check out our Excel VBA Resources

Re: Convert files from wr1. to xls

Posted by DK on February 14, 2002 2:03 AM

What is wr1 format? I have code which can open large numbers of files, manipulate and then save them. The best way is to record a macro of how you converted one file and post the code for that. I'll then change it to allow you to convert all files in a folder automatically.

Regards,
D


Re: Convert files from wr1. to xls

Posted by Mark on February 14, 2002 5:25 AM
Hi DK,

Thank you for your reply. For your information wr1. file is symphony program file (Previous Lotus 123 version).

Mark


Re: Convert files from wr1. to xls

Posted by DK on February 14, 2002 9:31 AM

Here's code that will open all files in a specified folder and save them as Excel format. Let me know if you have any problems,
D

Option Explicit

Sub ConvertFiles()
'All WR1 files should be in the same folder in order for
'this code to work. There should be no other files other than
'the ones you want to convert.

Dim strFolder As String, lngFile As Long

'Change this to the folder containing the Wr1 files
strFolder = "C:\temp\wr1 files"

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = strFolder
.Execute

For lngFile = 1 To .FoundFiles.Count
'Here's where you need to put the code to open the file and convert it
Workbooks.Open .FoundFiles(lngFile)
ActiveWorkbook.SaveAs "C:\temp\converted files\file" & lngFile, xlExcel7
ActiveWorkbook.Close
Next lngFile
End With


End Sub



Re: Convert files from wr1. to xls

Posted by Mark L on February 14, 2002 7:40 PM
Hi Dk,
Thanks for the code. It's work well.
But after converted the wr1 file it have save and rename my file to such as file1, file2, file3... and etc. Whether can we remain back the original name (For e.g. 2001, 2002, 2003, 2004, 2005.....etc.

Thank you.

Mark L



Re: Convert files from wr1. to xls

Posted by DK on February 15, 2002 1:20 AM


Mark,

This modified code as long as each of your filenames only has one full stop (period) in it. i.e. this won't work very well on a filename such as myfile.2001.wr1. Let me know if it works...

D

Sub ConvertFiles()
'All WR1 files should be in the same folder in order for
'this code to work. There should be no other files other than
'the ones you want to convert.

Dim strFolder As String, lngFile As Long, strNewName As String
Dim FSObj As Object, FSFile As Object

Set FSObj = CreateObject("Scripting.FilesystemObject")

'Change this to the folder containing the Wr1 files
strFolder = "C:\temp\wr1 files"

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = strFolder
.Execute

For lngFile = 1 To .FoundFiles.Count
'Here's where you need to put the code to open the file and convert it
Workbooks.Open .FoundFiles(lngFile)
Set FSFile = FSObj.GetFile(.FoundFiles(lngFile))
strNewName = Left(FSFile.Name, InStr(1, FSFile.Name, ".") - 1)


ActiveWorkbook.SaveAs "C:\temp\converted files\" & strNewName, xlExcel7
ActiveWorkbook.Close
Next lngFile
End With


End Sub


Re: Convert files from wr1. to xls

Posted by Mark on February 15, 2002 2:13 AM

DK thanks for that...




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.