Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save as v95 kills features (EXCEL 97/2000)

    Someone saved a sheet but his EXCEL version let him do this as EXCEL95 (I guess). Now all my validation & cond. formatting is gone... booo

    Anyway, how do I prevent this from happening in VBA?

    Erik Jan

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Use the workbook_beforesave event to cancel the save (Cancel=True) and tell your user to use a macro button to do the save as you would want it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Yeah, but how do I find out IF that user is actually saving as older version in the first place...

    Erik Jan

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    What Jan Karel means is that you should stop the user from saving by the usual methods, and only allow them to save via a macro button of your own.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    The following code should prevent overwriting the original file if the XL version is lower than 97. If the version is 97 or greater but the user has the default format set tto XL95 for some reason, the file will be saved as 97/2000 depending on the version in use. <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not SaveAsUI Then
    If Val(Application.Version) < 8 Then
    MsgBox "Unable to overwrite file" & vbCrLf & _
    "Use Save As to save the file with a new name"
    Cancel = True
    Else
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=ActiveWorkbook.FullName, _
    FileFormat:=xlNormal
    Application.DisplayAlerts = True
    End If
    End If
    End Sub</pre>

    Andrew C

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Andrew: I haven't tried it, but I don't think your code will work for several reasons:

    1- You are only executing the code for Save, not SaveAs, and it is not possible to change the file version on a Save.

    2- On a SaveAs, the SaveAs dialog box is not displayed until after the BeforeSave event routine has executed. Therefore, the version check will not catch the change in versions.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Code like the following in the Workbook BeforeSave event routine should do what you want:

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim strFileName As Variant
    If SaveAsUI Then
    strFileName = Application.GetSaveAsFilename
    If strFileName <> False Then
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    ActiveWorkbook.SaveAs Filename:=strFileName
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End If
    Cancel = True
    End If
    End Sub
    </pre>

    Legare Coleman

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Legare,

    I had not tested it myself, and so to see what would happen I ran the code using XL2000 and XL97, and File Save was intercepted and prevented where XL97 was saving an XL2000 file, (I used Version 9 as the version cut off) .

    Allowing FileSave As would not overwrite the original file by accident, and so should not cause a problem. I also substituted xlFileFormat:= xlExcel9795, and that seemed to work fine, in that the file version saved was XlExcel9795 (=43). The code actually intercepts the Save, but uses FileSave As.


    Andrew

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Well, I just tested it on my Excel 2000, and it does not seem to do what was being asked for. I opened an Excel 2000 workbook and inserted your code into the Workbook BeforeSave event routine. I then put a debug interrupt on the first If statement in your code. I went back to the worksheet and did a File/SaveAs. I got the interrupt in you code and stepped through to see that the first IF statement was false and the rest of the code was skipped. Excel then went to a standard SaveAs dialog. I changed the file name and selected Excel 4.0 as the file type. I clicked on OK and got the dialog box saying that this file type does not support some features. I click OK, and the file was saved in Excel 4 format.
    Legare Coleman

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    I'm afraid Legare was correct. Your code does not trap a user saving to any other fileformat.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    OK, this seems the code I should use. I just don't understand why (and how) it works... I can see two cases:

    (1) if someone has set EXCEL97 to save files as EXCEL95 format (that works even if he uses a normal save and not save-as) will your code work?

    (2) if someone uses EXCEL95 (!) what happens then (or should I add code to Auto_Open to prevent further loading then?)

    Erik Jan

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Users with XL95 will not be able to open files saved as XL97 format at all.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Yep,

    You're right of course... that leaves possibility #1 (again: SaveAs is NOT done in that case)

    Also I still do not completely grasp how the code works when someone DOES do a SaveAs...

    Erik Jan

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    Above my skill level <img src=/S/smile.gif border=0 alt=smile width=15 height=15> but I was wondering if you could just intercept the SaveAs dialog box and present a custom one with only acceptable formats to choose from?

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Peter

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save as v95 kills features (EXCEL 97/2000)

    AFAIK if a user does a SAVE (or control-s), the workbook is saved in whatever format the *workbook* was opened from. To save as a different format, one allways needs to do a save_as, which is captured by using Legare's code.

    What legare's code does is:

    - ask for a filename (GetSaveAsFileName)
    - Save the file, in default format (which is the current version's format by default)
    - Cancel the Save_as by setting Cancel to true, since it has already been saved-as by the code.
    You can test the code by commenting out the Cancel=true. You'll see the save-as dialog appear twice, the first one from the code, the second one because you pressed Save-as.

    The enableevents stuff is there to prevent the event macro from going into an endless loop, because it does a save-as itself, which would also fire the event procedure.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •