Bypassing File Version Dialog on Save

[Ken Loomis]

When I save a workbook, I often get the dialog box warning me that the file
was created with a later version of Excel.

It starts out:

This file was create using a latter version of Microsoft Excel. If you save
this file using Microsoft Excel 97
information created with features in the later version may be lost.

If I click "Yes," the file gets saved, but the dialog always cpomes back.

It's a nuisance for the users and I'd like to make it go away.

The original workbook was created with Excel 2003, but then I had to finish
it on Excel 97 since some of the users would be using that version of Excel
and those with the older version of Excel were getting errors. I got rid of
all those errors and everything except that nuisance dialog box worked
great.

Now, I find I am having to write a routine that will:

collect all the old file names

for each file in this list of files
open each file
correct a misspelling
save the file
close the workbook
delete that file name from the list
next file

I have most everything done and it works well, but it takes some time.

The only problem is that I get that version dialog warning for each file I
save and that dialog has to be answered by the user for every file, which is
not going to work.


So I have two questions about this problem:

1) How can I make that dialog go away for the users?

2) How can I bypass that dialog when I do this automatic correction routine?


Any body have any ideas?

TIA,
Ken

 

[STEVE BELL]

You can try to turn off the Alert in your code

Application.DisplayAlerts = False
' your code to save
Application.DisplayAlerts = True

(I am not absolutely sure it will work in this instance, but try it and you
might like it)...

 

[William Benson]

I think is that the user is getting this message when they click the save
button (save normally). I thought at first that your code could go in the
BeforeSave event but the message is occurring before the beforesave event
fires.

Strange conundrum.

 

[Ken Loomis]

I searched using Google for this phrase:

"This file was create using a latter version of Microsoft Excel"

and evidently, it is a problem with no apparent solution. At least, I
couldn't find a solution at Microsoft.com or any of the dozen or so web site
where others had already asked for help.

I seem to recall that I asked about this here a long time ago but didn't get
a solution then either.

If I could just figure out how to save the "master" file once as an Excel 97
format file, I think I could make this go away, but even that doesn't work.
The file always seems to know that it was originally created with a later
version of Excel.

I guess I could go in and start a new Excel 97 workbook and manually copy
everything over, but that seems so ridiculous.

Again, any other ideas are most welcome.

TIA,
Ken

 

[William Benson]

Open the file in Excel 97 and save it there ... That's what I did, I have
both versions.

Bill

 

[Ken Loomis]

I must be missing something, becasue that doesn't seem to work.

I open the file in Excel 97, click File>Save, but no matter what I do, I
always get the warning dialog.

Do I need to select a specific file format? I thought I had tried several,
but I know I am going around in circles on this by now.

Ken

 

[William Benson]

Sorry - you're right it just doesn't work whether you save the file down to
a prior version of Excel from 2003, or whether you open it in a earlier
version and save it there -- even if you rename the file each time. Don't
know what to tell you Ken, sorry for the bum steer. I got the same answer
from some other sites


http://www.mrexcel.com/archive2/74400/86321.htm and
http://www.pcreview.co.uk/forums/thread-942325.php

And NO ONE ever seemed to get a reply back saying "didn't work."

Bill

 

[STEVE BELL]

This code worked in Excel 2000 (recorded)

ChDir "C:\WUTemp"
ActiveWorkbook.SaveAs Filename:="C:\WUTemp\Book2.xls", FileFormat:= _
xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False

Be sure to provide for case where workbook already exists.

 

[Tom Ogilvy]

This would save both an xl97 and xl95 version of the workbook combined in
the same file. It would make the file twice as large.

Perhaps that isn't a consideration.

 

[William Benson]

Steve, I tried your code in Excel 2003. I made the target workbook the
active one, but ran the code from within another workbook's module. It saved
the file just fine (I believe Tom mentioned some extra filesize overhead to
be worried about). But for me, the chief issue is that after closing the
saved file, when opening in Excel 97 and attempting to re-save, I still got
the error message.

Conundrum continues. (Personally I would not work this way anyway, but Ken
has a legitimate need, as he explained).

 

[STEVE BELL]

William,

I am running Excel 2000 and am unable to investigate the issue further.

Sorry...

 

[William Benson]

OK, I was just giving you feedback - actually, I and others were just trying
to answer Ken Loomis's original concern, which I agree seems pretty
daunting -- glad I don't have the problem myself (of needing a workaround)
but who knows when I might be in the same boat.

Thanks Steve!

 

[STEVE BELL]

William,

Always appreciate the feedback...

Just wish I could have helped further...

be fun...

 

[Ken Loomis]

Thanks for all the comments.

I finally figured out a workaround. I use SaveAs with a new file name, then
delete the old file and finally rename the new file to the old file name. By
disabling the Application.DisplayAlerts and the Application.EnableEvents, it
goes thru without promting the user about the version that is being saved
and it avoids the opening message box that is displayed when a workbook is
opened.

Here is that code, still in testing, but it works: Any comments are
appreciated.

Sub CleanCodeInNextFile()
Dim OldFileName As String
Dim NewFileName As String
Dim OldFileTempName As String

' Final version needs to test cell A1 on Old Files sheet for empty

OldFileName = Sheets("OldFiles").Range("A1").Value

If Right(OldFileName, 4) = ".xls" Then
OldFileTempName = Left(OldFileName, Len(OldFileName) - 4)
NewFileName = OldFileTempName & "XXX.xls"
Else
MsgBox ("NO, the extension is NOT .xls")
End If

If FileExists1(OldFileName) Then
Application.DisplayAlerts = False
Application.EnableEvents = False
OpenProtectedFile (OldFileName)

DeleteAllCode
DeleteButtons
DeleteReplaceInfoSheet

ActiveWorkbook.SaveAs FileName:=NewFileName, FileFormat:=xlNormal,
_
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False

Kill (OldFileName)

ActiveWorkbook.Close
Name NewFileName As OldFileName

Application.DisplayAlerts = True
Application.EnableEvents = True

Else
MsgBox ("No, it did not find that file")
End If

' Delete cell A1 on the 'Old Files' sheet.

End Sub

 

[William Benson]

Ken,

I was only attacking the problem you expressed in this sentence:

" 1) How can I make that dialog go away for the users?"

I think you are saying you got Question 2 resolved, but are you saying that
this approach addresses what happens when a user saves a file on their own?

As I pointed out before, the warning message appears BEFORE the BeforeSave
event fires, upon saving. I am sure that displayalerts, appropriately set to
False early enough, will shield a subroutine from encountering that message
.... but if the residual file does not still give the USER an alert when THEY
save (naturally) then I am very surprised indeed -- and glad for you also.

Let me know how that part worked out.

Bill

 

[Ken Loomis]

You are right, Bill.

I have only solved question #2.

The user still has to answer that dialog box, which makes no sense to me.

I thought the purpose of the version warning was to give the user a chance
to NOT lose any features that make have been used from the later version of
Excel. But once, saved, I would have thought any of those newer features
would be gone from the workbook and there would be no need for further
warnings.

I ran into this problem months ago and asked on this forum and searched for
a solution to no avail. Searching the Internet for the phrase:

"This file was create using a latter version of Microsoft Excel"

I can only find others that are as frustrated with this as I am.

Thanks for your help and comments.

Ken