Solved: excel 2007 saving to 97-2003 compatible

I'm definitely curious. If you can create it on demand, then I can take it to MS and see what they think of the issue. :)

I have had so many revisions, I'm not sure which one I had ws]as causing the bloat. I've gone through many but mostly the xlsm is about 233kb and the xls is about 625kb. This is not excessive. I understand 2007 has better compression. I was using excel diet, but found it incompatible with 2007. It was looking at the old row and column max which are larger in 2007, so I disabled that. I also found some artifacts in one of my sheets. Extra empty text boxes were being populated invisibly in one of my macros. I removed them form the master and most of the problems went away. I do think the bloat had to do with the excel diet going back and forth from 2002 and 2007. I stopped using the save

I never could get this function to work, not sure how to call it in the sheet, or is it intended for a sub call?
[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]

This is the one I ended up using
[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]

Quote:

---

Originally Posted by mperrah

I never could get this function to work, not sure how to call it in the sheet, or is it intended for a sub call?

---

As it's been declared Private, you would only be able to call it from a sub or function within the same VBA module. If you axe the Private keyword, or replace it with Public, you will be able to call it from a worksheet.

I did notice that, I tried removing the private.
Do I have to put an argument in the parenthases?
=getfilesaveastype(thisworkbook...) or (activeworkbook)...
or just =getfilesaveastype ?
Mark

In VBA it would be
[vba]sType=GetFileSaveAsType(Right(Thisworkbook.name),4)[/vba]

You need to feed the function a 4 character string. Legacy version files will therefore have the "." sent with them (.xls), while the new formats wouldn't (xlsx).

Call the function from a worksheet would work in the same way, but you'd want to feed it a reference to a cell that holds the workbook name, or use a formula to provide it.

Thanks Ken, I thought I had to send something through the () but wasn't sure how to word it.
I did try "thisworkbook" at one time, but from excel not vba

Finally
This is one of the three versions I'm using now.
The workbook generates 2 reports and I save different sheets for each
I also save a full version with all the sheets for an archive.
This procedure sets the name and prompts for the save to folder...

Thanks to all
[VBA]Sub SaveForWPR()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim sFileName As String
Dim Ws As Worksheet
Dim sht, sarray

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

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

sarray = Array("QCDetail", "WQC", "Chart", "WPR", "MenuSheet", "Prompt")

For Each sht In ActiveWorkbook.Worksheets
If IsError(Application.Match(sht.Name, sarray, 0)) Then
sht.Delete
End If
Next sht

If sFileName = "False" Then

End If

ThisWorkbook.SaveAs sFileName

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
[/VBA]