File Converision, (a lot of them)!

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I want to find and convert all excel 2000 files to excel
95-97 and 2000 that are on my pc so they can be read with
excel 95. I have about 400 files to convert! Is there a
program that will convert all of my files or do I have to
do them one at a time?
 
Try FILE>SAVE AS and then open the file type box and choose "Excel 5.0/95
Workbook".

--
RMC,CPA


I want to find and convert all excel 2000 files to excel
95-97 and 2000 that are on my pc so they can be read with
excel 95. I have about 400 files to convert! Is there a
program that will convert all of my files or do I have to
do them one at a time?
 
Try FILE>SAVE AS and then open the file type box and choose "Excel 5.0/95
Workbook".
Click to expand...

Ummm... I think he knows how to do that, Richard. He's asking if
there's a way of automating the process. I don't think that I'd care
to do Save As on 400 files...

You can use a simple VBA macro to do it. Bear in mind that this may
take a while to run, so you may want to leave it running overnight or
at least over lunch time. Also I'd recommend that you do it with
copies of the files rather than the originals just in case; if
anything goes wrong, you're covered. Finally, bear in mind that the
multi version files will be larger than their Excel 97 onwards
cousins, since the file has to contain data for both formats. (I'd
really consider whether you should still be using 95; it's just too
old now. However that's your call.) Finally, this assumes that all
files are in one directory. That's the easiest way of doing it.

Sub ConvertAllFiles()

Dim l_FileCounter As Long
Dim s_CurrentFileName As String

'Change the following to the drive and path
'containing the files
ChDrive "H"
ChDir "H:\EXCEL\Tests_Development\FilesToConvert"

'Get the first xls file in the directory.
s_CurrentFileName = Dir("*.xls", vbNormal)

'This will prevent the "Are you sure" dialog from appearing
'when you SaveAs the new format.
Application.DisplayAlerts = False

'This will make the code faster.
Application.ScreenUpdating = False

Do While s_CurrentFileName <> ""

l_FileCounter = l_FileCounter + 1
'You can tell how many files there are through
'Windows Explorer. The status bar will tell you
'how many have been done.
Application.StatusBar = "Converting file no. " _
& CStr(l_FileCounter) & " (" & s_CurrentFileName & ")"

'Open the file.
Application.Workbooks.Open s_CurrentFileName

'Save in the older format
ActiveWorkbook.SaveAs Filename:= _
"H:\EXCEL\Tests_Development\FilesToConvert\" _
& s_CurrentFileName, FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:= _
False, CreateBackup:=False

'Close it.
ActiveWorkbook.Close False

'Get the next name.
s_CurrentFileName = Dir

Loop

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub
 
Hi, Hank:

A couple of comments:

This one's trivial ... If the OP's goal is to allow the files to be read with XL95, why not just
save in XL5/95 format? That should produce less "bloat".

This one isn't. You use the Dir$() function to get the file names. That function "gets confused"
if you do anything that changes the directory between calls to the function. Unfortunately,
opening a file and resaving it, even under the same name, falls into this category.

You need to (a) use a separate function to get all of the file names into an array before you
start to convert them, or (b) use FileSystemObject to get the file list. (c) I don't mention
FileSearch because it's flaky on Excel XP, but if he's using XL2000, it may be OK.

To that end, here's a function that will get all of the file names into an array. You would call
this first, then read the names from the array and process the files. The function returns the
number of found files.

Function GetFileList(Pattern As String, FileNames() As String) As Long
Dim f As String
Dim n As Integer

n = 0
Erase FileNames()

f = Dir$(Pattern)
Do While Len(f)
n = n + 1
ReDim Preserve FileNames(1 To n) As String
FileNames(n) = f
f = Dir$()
Loop

GetFileList = n

End Function 'GetFileList



Myrna Larson
 
On Sun, 10 Aug 2003 01:33:54 -0500, Myrna Larson

Hi Myrna,
Hi, Hank:

A couple of comments:

This one's trivial ... If the OP's goal is to allow the files to be read with XL95, why not just
save in XL5/95 format? That should produce less "bloat".
Click to expand...

True. I seemed to recall that there WAS a reason for using the dual
format type in preference to the earlier type alone when you would be
working in both environments. Yes, yes, vague, I know... but it was
something that I read quite a while ago now and I can't quite recall
the specifics, or even where I saw it. If I can find the relevant
reference in the black hole that passes for my library and filing
system, I'll post what it was.
This one isn't. You use the Dir$() function to get the file names. That function "gets confused"
if you do anything that changes the directory between calls to the function. Unfortunately,
opening a file and resaving it, even under the same name, falls into this category.
Click to expand...

I think it may be more a case of "can" get confused than "will" get
confused. I tested it with 20 files and it performed as I expected,
but granted that's a long way from the 400 that the OP is looking at.
Thanks for the tip; I hadn't heard of that problem before.
You need to (a) use a separate function to get all of the file names into an array before you
start to convert them, or (b) use FileSystemObject to get the file list. (c) I don't mention
FileSearch because it's flaky on Excel XP, but if he's using XL2000, it may be OK.
Click to expand...

I'm quite partial to the FSO myself these days (it's amazingly
powerful, with my one grumble being the lack of support for wildcard
searches except through "brew-your-own" code in conjunction with the
Files collection), though I seldom answer questions in those terms;
there are too many old, old Win95 installations still floating around,
even in major corporations.
To that end, here's a function that will get all of the file names into an array. You would call
this first, then read the names from the array and process the files. The function returns the
number of found files.
Click to expand...

OK Rick, do like Myrna says. 8^>

If you have any problem integrating her code with mine, just post
again and I'll do an integrated version of it for you.
 
On Sun, 10 Aug 2003 01:33:54 -0500, Myrna Larson

Hi Myrna,


True. I seemed to recall that there WAS a reason for using the dual
format type in preference to the earlier type alone when you would be
working in both environments. Yes, yes, vague, I know... but it was
something that I read quite a while ago now and I can't quite recall
the specifics, or even where I saw it. If I can find the relevant
reference in the black hole that passes for my library and filing
system, I'll post what it was.
Click to expand...

AHA!!! NOW I REMEMBER!!! Well, partly, anyway. I think that there was
a second, more significant reason, but this one is still bothersome
enough to warrant a mention.

If you save a file in xl5/95 format alone, then when you're working on
it in xl97+ the first time you go to save it, EVERY time you open it,
you'll get that bloody annoying "This file was saved in a previous
version, do you want to convert it" dialog. You don't get that with
the dual format version, and IIRC there's no option to turn the dialog
off. Obviously there's always a risk of the file being accidentally
converted back up to the later file format if you let that dialog run,
which would again make it inaccessible to the Excel 95 users until
someone with 97+ opened it and re-saved it in the 95 format.
 
Ummm, I guess you're right, Hank. I should have given the OP a more
thoughtful response, as you seem to be, ummm, suggesting. Yes, 400 files
would be a pain, manually. It is interesting that the OP asked for "a
program", and not specific code which he could have gotten in the
programming group. But, ummm, what do I know? At least he did get some good
code.
--
RMC,CPA


Try FILE>SAVE AS and then open the file type box and choose "Excel 5.0/95
Workbook".
Click to expand...

Ummm... I think he knows how to do that, Richard. He's asking if
there's a way of automating the process. I don't think that I'd care
to do Save As on 400 files...

You can use a simple VBA macro to do it. Bear in mind that this may
take a while to run, so you may want to leave it running overnight or
at least over lunch time. Also I'd recommend that you do it with
copies of the files rather than the originals just in case; if
anything goes wrong, you're covered. Finally, bear in mind that the
multi version files will be larger than their Excel 97 onwards
cousins, since the file has to contain data for both formats. (I'd
really consider whether you should still be using 95; it's just too
old now. However that's your call.) Finally, this assumes that all
files are in one directory. That's the easiest way of doing it.

Sub ConvertAllFiles()

Dim l_FileCounter As Long
Dim s_CurrentFileName As String

'Change the following to the drive and path
'containing the files
ChDrive "H"
ChDir "H:\EXCEL\Tests_Development\FilesToConvert"

'Get the first xls file in the directory.
s_CurrentFileName = Dir("*.xls", vbNormal)

'This will prevent the "Are you sure" dialog from appearing
'when you SaveAs the new format.
Application.DisplayAlerts = False

'This will make the code faster.
Application.ScreenUpdating = False

Do While s_CurrentFileName <> ""

l_FileCounter = l_FileCounter + 1
'You can tell how many files there are through
'Windows Explorer. The status bar will tell you
'how many have been done.
Application.StatusBar = "Converting file no. " _
& CStr(l_FileCounter) & " (" & s_CurrentFileName & ")"

'Open the file.
Application.Workbooks.Open s_CurrentFileName

'Save in the older format
ActiveWorkbook.SaveAs Filename:= _
"H:\EXCEL\Tests_Development\FilesToConvert\" _
& s_CurrentFileName, FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:= _
False, CreateBackup:=False

'Close it.
ActiveWorkbook.Close False

'Get the next name.
s_CurrentFileName = Dir

Loop

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub
 
Back
Top