How to change save type from excel workbook to excel 97-2003 in excel2010 using vba?

Question

Due to activewoorkbook.close true my macro opens saveFileDialog but it select its save type by default as "Excel Workbook". which i need to change as "excel 97-2003 workbook".even i have changed my default format in excel save options. but it works only when i save file manualy. can any one suggest me some line?

Sub OpenAllWorkbooksnew() Set destWB = ActiveWorkbook Dim DestCell As Range

    Dim cwb As Workbook
    For Each cwb In Workbooks

        **Call donemovementReport**
        ActiveWorkbook.Close True
        ActiveWorkbook.Close False
    Next cwb
End Sub

Answer 1

You could call the SaveAs method (with DisplayAlerts = False) instead of Close. First change the target directory and in SaveAs specify the file format. Does this help you?

Dim targetDirectory As String
targetDirectory = "c:\temp\VBA\test\"
ChDir targetDirectory

Application.DisplayAlerts = False
Workbooks(ActiveWorkbook.Name).SaveAs ActiveWorkbook.Name, xlExcel8
Application.DisplayAlerts = True

ActiveWorkbook.Close False

Answer 2

Well I do not know if I understand. You could first display the file-dialog where you get the file name with its path and then call save-as method.

Sub test2()

    Dim targetDirectory As String
    targetDirectory = "c:\temp\VBA\test\"
    ChDir targetDirectory

    Dim excelFileName As Variant
    excelFileName = Application.GetSaveAsFilename(FileFilter:="Excel Files,*.xls,All Files,*.*", Title:="Save As File Name")
    If excelFileName = False Then Exit Sub
    If LCase$(Right$(excelFileName, 4)) <> ".xls" Then
      excelFileName = excelFileName & ".xls"
    End If

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs fileName:=excelFileName, FileFormat:=xlExcel8
    Application.DisplayAlerts = True

    ActiveWorkbook.Close False

End Sub

Or this way you can just display the save-as dialog:

Application.Dialogs(xlDialogSaveAs).Show "c:\temp\VBA\test\test.xls"