Solved: excel 2007 saving to 97-2003 compatible

[mperrah]

Thanks in advance to all the help you folks have given to get this far.
My office has 24 trainers that use this file we put together and they are blown away. They wonder why I still work there and don't run an excell business.
If it wasn't for this forum, I would be no where near that possibility.

Now for my new test.
Excell 2007 lets me save as a macro enbled format which works great if you use the 2007 version. Most of my co-workers are 2003 and aren't planning to upgrade soon. So I can export as 97-2003 compatible. For the most part that is ok, but I like to work in the 2007 version for the features.

One macro I run lets the user save a file that deletes several sheets and pulls a name from a named range (with .xls extension).
On my version (in .xlsm) clicking this macro uses the save as and adds the .xls to an .xlsm file. When reopenning this causes problems.
And if this is openned on the older version (2003) it looks like greek code.

I know I can change the output extension to .xlsm, but the other users wont ever see or use that version.

Is there a way to test what version excell is in use (2002,2003,2007) and the format of the working file (.xls, .xlsm), and save as can keep the correct format based on what is in use?

ie If I open in .xlsm the save as will use .xlsm - 2007 compatible
If I open in .xls the save as will use .xls 93-2003 compatible

I am having to work in compatiblity mode and making sure I have saved the right format when working with other offices is leaving room for error.

any suggestions would greatly be appreciated
Thanks again for your continued support.
I'll attach the file so you can see all I've (we've) done
it's office 7 xlsm zipped
Mark

[vonpookie]

You could check the version number and act accordingly.

I don't have 2007 on this computer to test it there, but I think this may give you a general idea?

[vba]Dim VerNum As String

VerNum = Application.Version

Select Case VerNum
Case Is = "11.0" 'Excel 2003
'save as .xls
Case Is = "12.0" 'Excel 2007
'save as .xlsm
End Select
[/vba]

A better example can be found here:
http://www.dailydoseofexcel.com/arch...excel-version/

[Ken Puls]

Hi there,

I work in a smiliar enviornment. Personally, I would try not to mix file formats on your network as it just leads to confusion and frustration. To go to a single format, you do have a couple of options.

The first would be to go to the Office button and choose Excel Options. On the left, choose Save and you can set your default file format to Excel 97-2003 files. This way each new workbook you create will be in the correct format to begin with. At this point, everything seems to default to the 97-2003 versions and you're good to go.

If you are really in love with the openXML formats (xlsx and xlsm), you can download a compatibility add-in for Office 2003. I haven't actually tried this myself, and don't know if it works pre 2003, but the idea is that your users on 2003 could then open the xlsx and xlsm file formats.

Of you do decide to go the code route that Kristy mentioned, I'd be sure to use the Val route that is pointed out in the blog post she linked to. It's a good habit to get into, as versions don't always stay purely numerical.

HTH,

[mperrah]

I have 2002 excel and it prompted me to download the converter from microsoft which I did and it worked, (bloated the file though)
the conversion is 27mb was hoping for something code based.
I'll try the val and see what we get.
Does this method alter the format saved or just the extension?
Mark

[Ken Puls]

Actually, no... that's not going to work.

Testing the version of the application is only going to return you the version of the app you're running, not the version of the file. Sorry, I should have mentioned that before. I re-read your post and I think I see what you're after now.

What you could do is test the extension of the file using the following function (or a variant of it.)

[vba]Private Function GetFileSaveAsType(sExtension As String) As Long
Select Case LCase(sExtension)
'// Template Files
Case Is = ".xlt"
'17 Template
GetFileSaveAsType = xlTemplate
Case Is = "xlts"
'54 Open XML Template
GetFileSaveAsType = xlOpenXMLTemplate
Case Is = "xltm"
'53 Open XML Template Macro Enabled
GetFileSaveAsType = xlOpenXMLTemplateMacroEnabled
'// Workbooks
Case Is = ".xls"
'-4143 Workbook normal
GetFileSaveAsType = xlWorkbookNormal
Case Is = "xlsx"
'51 Open XML Workbook
GetFileSaveAsType = xlOpenXMLWorkbook
Case Is = "xlsm"
'52 Open XML Workbook Macro Enabled
GetFileSaveAsType = xlOpenXMLWorkbookMacroEnabled
'// Add-ins
Case Is = ".xla"
'18 Microsoft Excel 97-2003 Add-In
GetFileSaveAsType = xlAddIn8
Case Is = "xlam"
'55 Open XML Add-In
GetFileSaveAsType = xlOpenXMLAddIn
'// If in doubt...
Case Else
'-4143 Workbook normal
GetFileSaveAsType = xlWorkbookNormal
End Select
End Function[/vba]

Basically, you'd query this function to get the exisitng file type:
[vba]Dim lSaveAsFormat As Long
lSaveAsFormat = GetFileSaveAsType(Right(ActiveWorkbook.FullName, 4))[/vba]

Use the result in your SaveAs command as the fileformat:= portion.

Does that make sense?

[mperrah]

Yes it does,
Thank you Ken,
I love your website BTW
I did not think about it, but the file format tells me all I need to know.
If it's xlsm of course it's in 2007
and as long as I save to the same format, I should be good.
I'll post my results when I get a working model.
Thanks again.
Mark

[Bob Phillips]

Here is an alternative that I dug up from my archives.

It doesn't have 2007 in this code yet, but I bet (hope) 2007 is different)

[vba]

Function FileSavedIn()
Select Case ActiveWorkbook.FileFormat
Case -4143: FileSavedIn = "Excel 2000 et plus"
Case 43: FileSavedIn = "Excel 97/2000"
Case 39: FileSavedIn = "Excel 5"
Case 35: FileSavedIn = "Workbook Excel 4"
Case 33: FileSavedIn = "Sheet Excel 4"
Case 29: FileSavedIn = "Excel 3"
Case 16: FileSavedIn = "Excel 2.1"
Case Else: FileSavedIn = "Unknown"
End Select
End Function
[/vba]

[Bob Phillips]

In 2007, xlsm seems to be 52, xlsx is 51, and 2003 files are 56.

[Ken Puls]

Interesting. For some reason I didn't see 56 when I pulled my list together. (The rest are commented in my routine). I wonder if it's because I was working with the 97-2003 format, rather than 2003 specifically?

[mperrah]

Ken,
I tweaked you suggestion to add on xld's ideas.
Your code says #Value! error

I also tried xld's adding your list in and seems to list the format ok.
Question is,
how do I tie in the result to the save as?

This is how I altered youre code:
[vba]Private Function GetFileSaveAsType(sExtension As String) As Long
Dim lSaveAsFormat As Long
lSaveAsFormat = GetFileSaveAsType(Right(ActiveWorkbook.FullName, 4))

Select Case LCase(sExtension)
'// Template Files
Case Is = ".xlt"
'17 Template
GetFileSaveAsType = xlTemplate
Case Is = "xlts"
'54 Open XML Template
GetFileSaveAsType = xlOpenXMLTemplate
Case Is = "xltm"
'53 Open XML Template Macro Enabled
GetFileSaveAsType = xlOpenXMLTemplateMacroEnabled
'// Workbooks
Case Is = ".xls"
'-4143 Workbook normal
GetFileSaveAsType = xlWorkbookNormal
Case Is = "xlsx"
'51 Open XML Workbook
GetFileSaveAsType = xlOpenXMLWorkbook
Case Is = "xlsm"
'52 Open XML Workbook Macro Enabled
GetFileSaveAsType = xlOpenXMLWorkbookMacroEnabled
'// Add-ins
Case Is = ".xla"
'18 Microsoft Excel 97-2003 Add-In
GetFileSaveAsType = xlAddIn8
Case Is = "xlam"
'55 Open XML Add-In
GetFileSaveAsType = xlOpenXMLAddIn
'// If in doubt...
Case Else
'-4143 Workbook normal
GetFileSaveAsType = xlWorkbookNormal
End Select
End Function[/vba]



This how I altered XLD's
[vba]Function FileSavedIn()
Select Case ActiveWorkbook.FileFormat
Case 56: FileSavedIn = "Excel 2003" '.xls?
Case 55: FileSavedIn = "Excel 2007 Open XML Add-in" '.xlam
Case 54: FileSavedIn = "Excel 2007 Open XML Template" '.xlts
Case 53: FileSavedIn = "Excel 2007 Open XML Template Macro Enabled" '.xltm
Case 52: FileSavedIn = "Excel 2007 Open XML Macros Enabled" ' .xlsm
Case 51: FileSavedIn = "Excel 2007 Open XML" ' .xlsx
Case -4143: FileSavedIn = "Excel 2000 et plus" ' (workbook normal) .xls
Case 43: FileSavedIn = "Excel 97/2000" ' .xls?
Case 39: FileSavedIn = "Excel 5" ' .xls?
Case 35: FileSavedIn = "Workbook Excel 4" ' .xls?
Case 33: FileSavedIn = "Sheet Excel 4" ' .xls?
Case 29: FileSavedIn = "Excel 3" ' .xls?
Case 18: FileSavedIn = "Excel 97-2003 Add-in" ' .xla
Case 17: FileSavedIn = "Excel Template" ' .xlt
Case 16: FileSavedIn = "Excel 2.1" ' .xls?
Case Else: FileSavedIn = "Unknown"
End Select
End Function
[/vba]

This is my code to save as:
[vba]sFileName = Application.GetSaveAsFilename(InitialFileName:=Range("trnqcName"))

If sFileName = "False" Then
End If

ThisWorkbook.SaveAs sFileName[/vba]

[mperrah]

Implemented xld's method like below.
I used a named range to pull the value from this result.
Problem is... file size goes from 134kb to 16Mb
I think just keeping the extension correct doesn't impliment any compression used when saving as the correct type? do you agree

Is there a way to tell excel to save as the type rather than just changing the extension?

[VBA]Function FileSavedIn()
Select Case ActiveWorkbook.FileFormat
Case 56: FileSavedIn = ".xls" ' Excel 2003
Case 55: FileSavedIn = ".xlam" ' Excel 2007 Open XML Add-in
Case 54: FileSavedIn = ".xlts" ' Excel 2007 Open XML Template
Case 53: FileSavedIn = ".xltm" ' Excel 2007 Open XML Template Macro Enabled
Case 52: FileSavedIn = ".xlsm" ' Excel 2007 Open XML Macros Enabled
Case 51: FileSavedIn = ".xlsx" ' Excel 2007 Open XML
Case -4143: FileSavedIn = ".xls" ' Excel 2000 et plus (workbook normal)
Case 43: FileSavedIn = ".xls" ' Excel 97/2000 (correct?)
Case 39: FileSavedIn = ".xls" ' Excel 5 (correct?)
Case 35: FileSavedIn = ".xls" ' Workbook Excel 4 (correct?)
Case 33: FileSavedIn = ".xls" ' Sheet Excel 4 (correct?)
Case 29: FileSavedIn = ".xls" ' Excel 3 (Correct?)
Case 18: FileSavedIn = ".xla" ' Excel 97-2003 Add-in
Case 17: FileSavedIn = ".xlt" ' Excel Template
Case 16: FileSavedIn = ".xls" ' Excel 2.1 (Correct?)
Case Else: FileSavedIn = ".xls" ' Unknown
End Select
End Function[/VBA]

[Ken Puls]

Ken,
I tweaked you suggestion to add on xld's ideas.
Your code says #Value! error

First line:
[vba]
Dim lSaveAsFormat As Long
lSaveAsFormat = GetFileSaveAsType(Right(ActiveWorkbook.FullName, 4))[/vba]
This is trying to stuff a string into a long. I might have confused you with what looks like the same in my code. Where I'm setting things like "xlTemplate" into a long, "xlTemplate" is actually a constant that evaulates to 17.

As far as saving in the correct format, based on what I originally posted, (only because I was trying to figure out your #Value! error,) I can get the file to save in the appropriate format ilke this:

[vba]Sub test2()
ActiveWorkbook.SaveAs "C:\Test\Workbook", FileFormat:=GetFileSaveAsType("xlsm") 'or ".xls" etc...
End Sub[/vba]

[mperrah]

Ken,
I figured out why I was getting the #value! issue.
We had a formula that was testing the filetype.
By using yours and XLD's together I got it to work
In one cell I put xld,s and named the cell "ext"
In another cell I put yours and added "ext" in the brackets
With a file in 97-2003 format his cell shows ".xls"
and yours shows "-4143"

=FileSavedIn() ' named ext result is .xls
=GetFileSaveAsType(ext) result is -4143


="Full_" & OfficeName & "_" & TEXT(WkStart+7, "mmddyy")&ext
this is in a cell named "savefullname"

[VBA]Sub a_test()

Dim lSaveAsFormat As Long

lSaveAsFormat = GetFileSaveAsType(Right(ActiveWorkbook.FullName, 4))

sFileName = Application.GetSaveAsFilename(InitialFileName:=Range("SaveFullName"))

If sFileName = "False" Then

Exit Sub

End If


ActiveWorkbook.SaveAs sFileName, FileFormat:=lSaveAsFormat[/VBA]

This is what I have so far, but I'm not sure it is using the file type correctly?

Mark

[Ken Puls]

Hi Mark,

Sorry for the late reply here. I've been super busy and away. Did you get this resolved, or are you still trying to work through it?

[mperrah]

Ken ,
great to here from you,
I kind of found an answer, but not exactly.
Using your code I have the sub see what type it allready is and save as the same extension.

But if the file was a 2007 version in compatible mode that just saves as .xls the file size bloats.

Beyond this, I only have 1 out of 23 users that are in 2007 all esle are 2000-2003,
so the 2007 issues turn out to be a moo point
(who cares what a cow thinks...)
I was over working the error capturing without thinking through the cases

It would be nice to be prepared if the users start upgrading.
How can I limit the bloat on save.
Rather than just pick a name and extension on save, how do I actully fire the save as type procedure to enable any compresion that might be bypassed other wise.

Thanks for remembering me.
Mark

[Ken Puls]

Hi Mark,

I've heard of the bloat issue before, and I'm kind of curious if there is a bug with the way Excel handles files in compatibility mode. It might be time to try saving in both formats.

Let me see what I can dig up here..

[mperrah]

This is the main file I save first.
"SaveFullName" is a named range on one of the sheets

[VBA]Sub SaveForFull()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim sFileName As String
Dim Ws As Worksheet

sFileName = Application.GetSaveAsFilename(InitialFileName:=Range("SaveFullName"))

On Error Resume Next
Set Ws = Sheets("Raw")
If Ws Is Nothing Then
MsgBox "This task must be performed from Full version"
Exit Sub
End If

On Error GoTo 0

If sFileName = "False" Then

End If

ThisWorkbook.SaveAs sFileName

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub[/VBA]


This is a function called from in the sheet, and the cell the function is called from is named ext.
[VBA]Function FileSavedIn()
Select Case ActiveWorkbook.FileFormat
Case 56: FileSavedIn = ".xls" ' Excel 2003
Case 55: FileSavedIn = ".xlam" ' Excel 2007 Open XML Add-in
Case 54: FileSavedIn = ".xlts" ' Excel 2007 Open XML Template
Case 53: FileSavedIn = ".xltm" ' Excel 2007 Open XML Template Macro Enabled
Case 52: FileSavedIn = ".xlsm" ' Excel 2007 Open XML Macros Enabled
Case 51: FileSavedIn = ".xlsx" ' Excel 2007 Open XML
Case -4143: FileSavedIn = ".xls" ' Excel 2000 et plus (workbook normal)
Case 43: FileSavedIn = ".xls" ' Excel 97/2000 (correct?)
Case 39: FileSavedIn = ".xls" ' Excel 5 (correct?)
Case 35: FileSavedIn = ".xls" ' Workbook Excel 4 (correct?)
Case 33: FileSavedIn = ".xls" ' Sheet Excel 4 (correct?)
Case 29: FileSavedIn = ".xls" ' Excel 3 (Correct?)
Case 18: FileSavedIn = ".xla" ' Excel 97-2003 Add-in
Case 17: FileSavedIn = ".xlt" ' Excel Template
Case 16: FileSavedIn = ".xls" ' Excel 2.1 (Correct?)
Case Else: FileSavedIn = ".xls" ' Unknown
End Select
End Function[/VBA]

This is the named cell contents that pulls several other named cells together and notice the extension is pulling from the named cell as the result of the function FileSavedIn...

="Full_" & OfficeName & "_" & TEXT(WkStart+7, "mmddyy")&ext

A lot of steps to get the extension correct.
It does seem to bloat if the formats come from differnt sources...
If I work in 2003 and save and re-open in 2007 is where I have trouble.
I'm very tempted to revert to 2003...

Thank you again for your attention

Mark

[Ken Puls]

Okay, now this is a REALLY simple example set, but I basically just threw some random profit/loss numbers together for a month, and graphed it in 2003.

• I saved the file to an xls format in 2003 and it was 23.5KB
• I opened the file in 2007 and saved it back to 2003 (no other changes). It was 21.5KB
• I opened the file again in 2007 and saved it in an xlsx (2007) format. It was 14.1 KB
• I created a new 2007 file, copied in the data and chart from the 2003 file, and saved to an xls format. This one was 30 KB.

I'm curious what steps you take to create the bloat on your side?

[Ken Puls]

Ah! You posted while I posted. <g>

I'll follow up on this on Sunday, as I'm going to be away till then.

[mperrah]

Ah! You posted while I posted. <g>

I'll follow up on this on Sunday, as I'm going to be away till then.

Glad to see you're on the case.
I'm off till Monday and keep my eyes open for you.

I'll try to recreate what happens and log some numbers for you.
Mark