.xls file saved by VBA gives file format does not match file extension error message when opening

[Andyatwork]

Hi all,

I'm using the below code to prompt users to save the finished output from a macrobook and while the file can be opened safely, it throws up an error message;
"the file you are trying to open, "...filename here...<FILENAME>" is in a different format than specified by the file extension. Verify that the file is not corrupted".. etc

Is there a change I can make so that the format will match the file extension and the error message will not be generated when the file recipient tries to open the file? They are a panicky bunch and will scream "virus! kill it with fire!".

I'm using excel07 on Win XP to produce the file which must be saved as .xls.

Sub Save_file_as()
    Dim iMsg As Byte
    Dim iFileName As String     'initial file name
    Set wb = ActiveWorkbook
    
    iFileName = ThisWorkbook.Path & "\Report " & format(Date, "dd.mm.yyyy") & ".xls"
        iMsg = MsgBox("Report preparation complete." & vbCrLf & _
        "Do you want to save the file?", vbYesNo + vbQuestion, "Save completed report?")
    
    If iMsg = 6 Then
        repname = Application.GetSaveAsFilename(InitialFileName:=iFileName, _
        filefilter:="Excel files, *.xls")
        With opwb
            If repname <> "False" Then
                .SaveAs repname
            Else
                Exit Sub
            End If
        End With
      Else
        Exit Sub
    End If
End Sub

 

[Norie]

Try adding the FileFormat argument to SaveAs.

.Saves repname, FileFormat:=xlExcel8

 

[Andyatwork]

Perfect!

Much obliged.