GetSaveAsFilename X & ActiveWorkbook.SaveAs X

[Still_learning]

Thank you to the kind and intelligent for any help/suggestion.

When you do a Save &As... you get about six option to pick from.
1 Excel Workbook (2007)
2 Excel Macro-enabled Workbook (2007)
3 Excel Binary Workbook (2007)
4 Excel 97-2003 workbook
5 Find add-ins for other file formats
6 other formats

When I use the GetSaveAsFilename to retrieve file name & location.

Problem:

1) The Save as type: only as All Files (*.*). When you use SaveAs normal
you have a wide selection to pick from and when you pick one of the
selections it changes the file name accordingly. So, basically I want to
make the GetSaveAsFilename to have the functionality of a the SaveAs file
type list.

2) With the following code if you save a file name Test Book.xls. Using
the saveas in 2007 format but you will see the name as Test Book.xls, so I
changed it to Test Book3.xlsx. When you try to open the file via a double
click from Explore you will get the follow msg:

Excel cannot open the file 'Test Book3.xlsx' because the file format or file
extension is not valid. Verify that the file has not been corrupted and that
the file extension matches the format of the file.

My conclustion is that when I manally changed the extension the file didn't
upgrade to the desired format. Another differant from the normal SaveAs.

I have my code in VBAProject (PERSONAL.XLS)

My code:

Option Explicit
Dim MyFullName
Dim bInternalSave
Dim closing

Public WithEvents xl As Excel.Application

Private Sub Class_Terminate()
Set xl = Nothing
End Sub

Private Sub Class_Initialize()
On Error Resume Next
Set xl = Excel.Application
shCount = 0
Exit Sub
Resume Next
End Sub

Private Sub xl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)
Dim RC
Dim NewFullName As String

RC = "update"

If closing = ActiveWorkbook.Name Then
If ActiveWorkbook.saved = True Then
Else
testFooter (RC)
ActiveWorkbook.saved = True
End If
ActiveWorkbook.Save
Else
If bInternalSave Then bInternalSave = False: Exit Sub

Cancel = True '-- cancel original save
MyFullName = ActiveWorkbook.FullName

If SaveAsUI Then

NewFullName = Application.GetSaveAsFilename(MyFullName)
If NewFullName <> "False" Then
bInternalSave = True
On Error Resume Next '-- user may cancel on SaveAs
testFooter (RC)
ActiveWorkbook.SaveAs NewFullName '-- so causes this line
fails
If Err = 0 Then
Else
bInternalSave = False
End If
End If
Else
bInternalSave = True
testFooter (RC)
ActiveWorkbook.Save
End If

End If
closing = ""
End Sub

 

[Ron de Bruin]

See example (Copy_ActiveSheet_2)
http://www.rondebruin.nl/saveas.htm

 

[Still_learning]

Thank you with some trial and error I believe my macro is finished with the
exception of completion of testing upon many users.

Thank you Ron de Bruin