Solved: Save in both Excel 2003 and 2007

[CaptRon]

I am trying to configure this code so that the workbook can be saved when its reset regardless of whether the user employs Excel 2003 or Excel 2007. We have both users in our office.

Runs OK in 2007 but when I try to run it through 2003, I get a compile error: Variable Not Defined referring to xlOpenXMLWorkbookMacroEnabled. If I Dim xlOpenXMLWorkbookMacroEnabled as a variable in 2003 to avoid the error, the macro works in 2003, but not in 2007.

How can I rewrite this to make both versions happy?

[vba]Sub RESET_OAI_Wkly()
'On Error GoTo HandleErrors
Dim answer As String, rLName As Range, rMoYr As Range, fname As
Variant
Dim xlOpenXMLWorkbookMacroEnabled

Worksheets("NAME & ID INFO").Activate
Set rLName = Range("A107") 'Last name
Set rMoYr = Range("A109") 'Month and year

'Creates a new workbook, renames it, then clears out the old data
If MsgBox("You are about to create a new workbook for the next month." _
& vbCrLf + vbCrLf & "Your old workbook has already been saved." _
& vbCrLf + vbCrLf & "In this step, Excel will provide a recommended " _
& "file name as: MAR LastName BeginDate. " & vbCrLf + vbCrLf & _
"Are you ready to proceed?" & vbCrLf + vbCrLf, vbYesNo + vbCritical _
+ vbDefaultButton2, " NEXT STEP") = vbYes Then

'Procedure if saving in Excel 2000-2003
If Val(Application.Version) < 12 Then
fname = Application.GetSaveAsFilename _
(initialfilename:="OAI_MAR " & rLName & " " & rMoYr, _
filefilter:="Microsoft Excel Workbook (*.xls), *.xls")
If fname <> False Then
ActiveWorkbook.SaveAs Filename:=fname
End If
End If

'Procedure if saving in Excel 2007
If Val(Application.Version) = 12 Then
fname = Application.GetSaveAsFilename _
(initialfilename:="OAI_MAR " & rLName & " " & rMoYr, _
filefilter:="Microsoft Excel Workbook (*.xlsm), *.xlsm")
If fname <> False Then
ActiveWorkbook.SaveAs Filename:=fname, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End If
End If

'Continue with clearing out old data
answer = InputBox("You've created and named the new workbook." _
& vbCrLf + vbCrLf & "If you're ready to clear out the old data, " _
& " type the word YES below:" & vbCrLf + vbCrLf & "Be sure to use " _
& "UPPERCASE letters", " CONFIRMATION")
If answer = "YES" Then
KeepMy11
Eraser2
ClearAll
End If
If answer = "yes" Then
If MsgBox("Be sure to type YES in all uppercase letters.", _
vbRetryCancel + vbInformation, "Type Response in Capital " _
& "Letters") = vbRetry Then
answer = InputBox("You've created and named the new workbook. " _
& "If you're ready to clear out the old data, type the word " _
& "YES below:" & vbCrLf + vbCrLf & "Be sure to use UPPERCASE " _
& "letters", " CONFIRMATION")
If answer = "YES" Then
KeepMy11
Eraser2
ClearAll
End If
End If
End If
End If
Set rLName = Nothing
Set rMoYr = Nothing

'HandleErrors:
Application.EnableEvents = True
End Sub[/vba]
Ron

[Bob Phillips]

Put the 2007 specific code in its own procedure in a SEPARATE module, and call that procedure.

[CaptRon]

I'll give it a shot this evening. Thanks for the quick reply.

Ron

[CaptRon]

xld,

Tried your suggestion and it worked great! When I compile in the 2003 VBE, I still get a compile error on the xlOpenXMLWorkbookMacroEnabled, but I just commented that out, compiled, restored the offending phrase, and everything runs fine in both Excel 2003 and Excel 2007.

I must remember this solution for isolating code so it doesn't come into play until needed.

Distinguished Lord of VBAX....certainly deserved. Thanks.

Ron

[Bob Phillips]

I used to use that method foir implementing a callback emualtor in Excel 97, haven't had to use it since. What goes around ...

[CaptRon]

You mention "What goes around...". Just a few months ago, I decided to stop writing macros to accomodate the occasional differences with Excel 97 and Excel 2000-2003. I figured that Excel 97 was 11 years old (or so) and it was time to move on. With Excel 2007, I'm back in the same boat, just a larger ocean.

This technique will prove to be useful again, I'm sure, as I MUST now write to accommodate Excel 2000-2007 users within our organization.

I always appreciate your help.

Ron

[Bob Phillips]

Ron,

The one thing that raises my eyebrows here is you saying you had to comment some part out in Excel 2003. I never had to do that with the 97/2000 problem, so am unsure why you need to for 2003/2007.

Can you post the workbook for me to see.

[CaptRon]

To enter VBE - Lucille.

I used the macro recorder in the 2007 VBE to come up with the code to save the file in the .xlsm format, then imported that into the macro that used an if-then statement to evaluate for Excel version.

If version < 12 then ... use the 2003 code
If version = 12 then ... use the 2007 code

Worked great in 2007, but when I tried it out in 2003, I would get both a runtime error and a compile error of Variable Not Declared referring to the xlOpenXMLWorkbookMacroEnabled needed by 2007. I preface each module with Option Explicit. I tried to Dim the expression xlOpenXMLWorkbookMacroEnabled as a variant just for grins. That made it work in 2003, but not 2007.

That's where you came to the rescue again. Removing the save routines to another module, then calling them from there still causes a compile error, but not a runtime error.

Ron

[Artik]

Hi CaptRon
I missed a little to the response.

When you declare a variable as Variant:[vba]Dim xlOpenXMLWorkbookMacroEnabled [/vba]after you run the code it has the value "Empty".

In this piece of code...:[vba]If fname <> False Then
ActiveWorkbook.SaveAs Filename:=fname, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
End If[/vba] ...there is a classified conversion to 0 (zero). In XL2007, the build-in constant xlOpenXMLWorkbookMacroEnabled have value = 52. Therefore, this section does not work in XL2007.
If you declare in your procedure a constant:[vba]Const xlOpenXMLWorkbookMacroEnabled As Long = 52[/vba]you do not need to isolate parts of the code to another module. It should work properly in XL2003 and XL2007 and code should compile.

Artik

[CaptRon]

Thanks for the tip, Artik. I'll give this a try this week and see what happens. If it works out, I can abbreviate some of my code without losing any performance. It may even work better.

Ron