Solved: Saving as xlExcel5 from Excel 2000

[johnske]

I have a number of workbooks saved in the xlExcel5 ('95) format that contain only data. They're automatically opened, read, written to, saved, and closed from code in an Excel 2000 workbook - no problems there, using VBA bypasses the AutoMacros...

However, if these workbooks have been opened and edited manually, and if the user then decides to save the changes they get the following message...

<filename> is a Microsoft Excel5.0/95 Workbook. Do you want to overwrite it with the latest Excel format?
To overwrite it, click Yes.
To save it in the current format, click No.

This message is unwanted, I've got a custom close as a 'fix' of sorts for this (below), i.e. it works fine for 'Yes' and 'No', the only real bug is if you select 'Cancel', the AutoMacro comes up again with the exact same 'Save?' prompt (a bit of an annoyance having to cancel twice) however, another minor bug is that if you choose 'Yes' for the 2nd AutoMacros msgbox, the '...Do you want to overwrite...' message comes up - anyone got any ideas on getting around this AutoMacro? :dunno


Option Explicit

Sub Auto_Close()

Dim Prompt As VbMsgBoxResult

If ThisWorkbook.Saved = False Then
With Application
.DisplayAlerts = False
Prompt = MsgBox("Do you want to save the changes you made to '" & _
ThisWorkbook.Name & "'?", vbExclamation + vbYesNoCancel)
Select Case Prompt
Case vbYes
ThisWorkbook.SaveAs _
FileName:=ThisWorkbook.FullName, _
FileFormat:=xlExcel5
Case vbNo
ThisWorkbook.Close False
Case vbCancel
Exit Sub
End Select
.DisplayAlerts = True
End With
End If
End Sub

[johnske]

BTW, I should mention that I've got everything else sorted except for the AutoMacros problem in the post above - the complete code so far is...
Option Explicit

Sub Auto_Open()
Application.DefaultSaveFormat = xlExcel5
ThisWorkbook.OnSave = "SpecialSave"
End Sub

Sub SpecialSave()
If ThisWorkbook.Saved = False Then
With Application
.DisplayAlerts = False
ThisWorkbook.SaveAs _
FileName:=ThisWorkbook.FullName, _
FileFormat:=xlExcel5
.DisplayAlerts = True
End With
End If
End Sub

Sub Auto_Close()

Dim Prompt As VbMsgBoxResult

If ThisWorkbook.Saved = False Then
ThisWorkbook.OnSave = "Auto_Close"
With Application
.DisplayAlerts = False
Prompt = MsgBox("Do you want to save the changes you made to '" & _
ThisWorkbook.Name & "'?", vbExclamation + vbYesNoCancel)
Select Case Prompt
Case vbYes
ThisWorkbook.SaveAs _
FileName:=ThisWorkbook.FullName, _
FileFormat:=xlExcel5
Case vbNo
ThisWorkbook.Close False
Case vbCancel
Exit Sub
End Select
.DisplayAlerts = True
End With
End If
End Sub

[JimmyTheHand]

:hi:

I made experiments on this with some partial success. Here's the modified code.
Public NoExec As Boolean '<--- here's a new line
Sub Auto_Open()
Application.DefaultSaveFormat = xlExcel5
ThisWorkbook.OnSave = "SpecialSave"
End Sub

Sub SpecialSave()
If ThisWorkbook.Saved = False Then
With Application
.DisplayAlerts = False
ThisWorkbook.SaveAs _
FileName:=ThisWorkbook.FullName, _
FileFormat:=xlExcel5
.DisplayAlerts = True
End With
End If
End Sub

Function Auto_Close() As Boolean '<---- changed to function
Dim Prompt As VbMsgBoxResult
If ThisWorkbook.Saved = False Then
ThisWorkbook.OnSave = "Auto_Close"
With Application
.DisplayAlerts = False
Prompt = MsgBox("Do you want to save the changes you made to '" & _
ThisWorkbook.Name & "'?", vbExclamation + vbYesNoCancel)
Select Case Prompt
Case vbYes
ThisWorkbook.SaveAs _
FileName:=ThisWorkbook.FullName, _
FileFormat:=xlExcel5
Case vbNo
NoExec = True '<---- here's a new line
ThisWorkbook.Close False
Case vbCancel
Auto_Close = True '<---- here's a new line
Exit Function
End Select
.DisplayAlerts = True
End With
End If
End Function

There's one more thing to it. I needed the workbook's BeforeClose event.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If NoExec Then Exit Sub
Cancel = Auto_Close
NoExec = False
End Sub This, according to my experiments, works just fine, the only problem is that the BeforeClose event isn't saved with the workbook, probably because of the '95 format, so I had to add it manually each time I opened the workbook.

But if you could manage to create this event handler sub in run time, with the Auto_Open, for example, that could solve the problem. I know you can add subroutines runtime, I saw it in the knowledge base.

What do you think?


Jimmy

[johnske]

Thanks Jimmy,

Sorry, no, I got compile errors with that. The Workbook_BeforeClose event works with a 'Cancel = True', but unfortunately Excel95 won't accept these sort of event procedures, and Excel95 also won't accept code to programmatically write the procedure into the ThisWorbook module either :(

I can't find anywhere on the net with a solution to this problem so it looks like I'll just have to live with it. The best I could come up with was...

Option Explicit

Sub Auto_Open()
Application.DefaultSaveFormat = xlExcel5
End Sub

Sub Auto_Close()
Dim Prompt As VbMsgBoxResult
If ThisWorkbook.Saved = False Then
With Application
.DisplayAlerts = False
Prompt = MsgBox("Do you want to save the changes you made to '" & _
ThisWorkbook.Name & "'?", vbExclamation + vbYesNoCancel)
Select Case Prompt
Case vbYes
ThisWorkbook.Save
Case vbNo
ThisWorkbook.Close False
Case vbCancel
Exit Sub
End Select
.DisplayAlerts = True
End With
End If

End Sub
which works OK except for the "double-cancel" glitch.

Thanks anyway,
John :)

[johnske]

Oops, sorry, what I said was wrong - you can add it programmatically, but not with CreateEventProc like I was trying before (Excel95 didn't have Event procedures) - thanx for the idea tho - this works perfectly :thumb

Option Explicit

Sub Auto_Open()
Application.DefaultSaveFormat = xlExcel5
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines 2, "Private Sub Workbook_BeforeClose(Cancel As Boolean)"
.InsertLines 3, "Dim Prompt As VbMsgBoxResult"
.InsertLines 4, " If ThisWorkbook.Saved = False Then"
.InsertLines 5, " With Application"
.InsertLines 6, " .DisplayAlerts = False"
.InsertLines 7, " Prompt = MsgBox(""Do you want to save the changes you made to " & _
ThisWorkbook.Name & " ?"", vbExclamation + vbYesNoCancel)"
.InsertLines 8, " Select Case Prompt"
.InsertLines 9, " Case vbYes"
.InsertLines 10, " ThisWorkbook.Save"
.InsertLines 11, " Case vbNo"
.InsertLines 12, " ThisWorkbook.Close False"
.InsertLines 13, " Case vbCancel"
.InsertLines 14, " Cancel = True"
.InsertLines 15, " End Select"
.InsertLines 16, " .DisplayAlerts = True"
.InsertLines 17, " End With"
.InsertLines 18, " End If"
.InsertLines 19, "End Sub"
End With
ThisWorkbook.Saved = True
End Sub