Re: Bypassing File Version Dialog on Save

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <winken@xxxxxxxxxxxx> wrote in message
news:u34MxvBgFHA.3428@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
> "STEVE BELL" <AYNrand451@xxxxxxxxxxx> wrote in message
> news:iMWxe.5776$Yb4.2643@xxxxxxxxxxx
>> William,
>>
>> Always appreciate the feedback...
>>
>> Just wish I could have helped further...
>>
>> be fun...
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>> news:ev99%23CAgFHA.1048@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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" <AYNrand451@xxxxxxxxxxx> wrote in message
>>> news:HZVxe.5469$Yb4.2109@xxxxxxxxxxx
>>>> William,
>>>>
>>>> I am running Excel 2000 and am unable to investigate the issue further.
>>>>
>>>> Sorry...
>>>>
>>>> --
>>>> steveB
>>>>
>>>> Remove "AYN" from email to respond
>>>> "William Benson" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>>>> news:OFRcaj$fFHA.1148@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> 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" <AYNrand451@xxxxxxxxxxx> wrote in message
>>>>> news:iqUxe.19496$Fn4.12353@xxxxxxxxxxx
>>>>>> 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.
>>>>>>
>>>>>> --
>>>>>> steveB
>>>>>>
>>>>>> Remove "AYN" from email to respond
>>>>>> "Ken Loomis" <winken@xxxxxxxxxxxx> wrote in message
>>>>>> news:enJ77N3fFHA.1148@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in message
>>>>>>> news:OaD7rI3fFHA.3656@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>> Open the file in Excel 97 and save it there ... That's what I did,
>>>>>>>> I have both versions.
>>>>>>>>
>>>>>>>> Bill
>>>>>>>>
>>>>>>>> "Ken Loomis" <winken@xxxxxxxxxxxx> wrote in message
>>>>>>>> news:esgBND3fFHA.3692@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>>>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" <wbenson1(SPAMSUCKS)@nycap.rr.com> wrote in
>>>>>>>>> message news:%23bOaGu2fFHA.3460@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>>>>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.
>>>>>>>>>>
>>>>>>>>>> "STEVE BELL" <AYNrand451@xxxxxxxxxxx> wrote in message
>>>>>>>>>> news:hlGxe.12518$Fn4.5037@xxxxxxxxxxx
>>>>>>>>>>> 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)...
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> steveB
>>>>>>>>>>>
>>>>>>>>>>> Remove "AYN" from email to respond
>>>>>>>>>>> "Ken Loomis" <winken@xxxxxxxxxxxx> wrote in message
>>>>>>>>>>> news:%23VLT6%231fFHA.1248@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>>>>>>>> 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
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: String doesnt auto dup on modification
    ... I'd consider a logger object as a sort of stream. ... do exactly the same thing to a string. ... You seem to be saying this is bad ... because normally objects do not mutate strings which are passed ...
    (comp.lang.ruby)
  • Re: C# Nullable types
    ... string x = null; ... compiler that by using the language specific word called "null". ... so we are given a keyword to state our intentions as the ... I am saying that I don't want x to have any value and I am "ok" with that. ...
    (microsoft.public.dotnet.framework)
  • Re: Access Query Expression - Extremely Urgent!!!
    ... > What you're really saying is you want the first occurrence of 5 numerals ... > in the ItemNumber string. ... Your InPack item is concatenating the result of Expr1. ... > Keith wrote: ...
    (microsoft.public.access.queries)
  • Re: Fundamental theorems, dilemmas, fitness, and information.
    ... I'm not saying YOU are wrong. ... > it totally contradicts for ME what does make sense to me. ... > makes absolute sense to me I have written at another string. ... I did not mean to say the statement "species evolve, ...
    (sci.bio.evolution)
  • Re: Bypassing File Version Dialog on Save
    ... I finally figured out a workaround. ... Dim OldFileName As String ... >>> steveB ...
    (microsoft.public.excel.programming)