how do I change file extension on VBA that I already use

[megha]

I have a code that SaveAs excel file as MS 2003 (i.e. ".xls" extension). I changed the extension from .xls to .xlsx to make it SaveAs MS 2010. It worked but I get the following message when I tried to open the file:

"The file cannot open 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."

here is the colde I used:

Private Sub CommandButton1_Click()
  Dim sFileName As String
    Dim sPath As String
    CommandButton1.Enabled = False
    sFileName = Format(DateValue(Now()), "mmm_dd_yyyy") & "_" & _
    Format(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())), "hh_mm_ss_AM/PM")
    If Len(Dir("\\sptd.sabert.net/sites/NavLibrary/Completed_Reports\" & Format(DateValue(Now()), "mmm_yyyy"), vbDirectory)) = 0 Then
        MkDir "\\sptd.sabert.net/sites/NavLibrary/Completed_Reports\" & Format(DateValue(Now()), "mmm_yyyy")
    End If
    sPath = "\\sptd.sabert.net/sites/NavLibrary/Completed_Reports\" & Format(DateValue(Now()), "mmm_yyyy")
    sFileName = sFileName & "_" & ID.Value & "_" & console.Value & "_" & prod.Value & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=sPath & "\" & sFileName, FileFormat:=xlNormal, ReadOnlyRecommended:=False
     MsgBox "Your log has been saved"
    ThisWorkbook.Close SaveChanges:=False
    
End Sub

[Ago]

I have a code that SaveAs excel file as MS 2003 (i.e. ".xls" extension). I changed the extension from .xls to .xlsx to make it SaveAs MS 2010. It worked but I get the following message when I tried to open the file:

"The file cannot open 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."

here is the colde I used:

Private Sub CommandButton1_Click()
  Dim sFileName As String
    Dim sPath As String
    CommandButton1.Enabled = False
    sFileName = Format(DateValue(Now()), "mmm_dd_yyyy") & "_" & _
    Format(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())), "hh_mm_ss_AM/PM")
    If Len(Dir("\\sptd.sabert.net/sites/NavLibrary/Completed_Reports\" & Format(DateValue(Now()), "mmm_yyyy"), vbDirectory)) = 0 Then
        MkDir "\\sptd.sabert.net/sites/NavLibrary/Completed_Reports\" & Format(DateValue(Now()), "mmm_yyyy")
    End If
    sPath = "\\sptd.sabert.net/sites/NavLibrary/Completed_Reports\" & Format(DateValue(Now()), "mmm_yyyy")
    sFileName = sFileName & "_" & ID.Value & "_" & console.Value & "_" & prod.Value & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=sPath & "\" & sFileName, FileFormat:=51, ReadOnlyRecommended:=False
     MsgBox "Your log has been saved"
    ThisWorkbook.Close SaveChanges:=False
    
End Sub

Try as above, Fileformat:=51

[Kenneth Hobs]

The macro recorder is your friend. After recording, view the FileFormat value. I doubt it will be xlNormal.

[megha]

Thank you! I used xlsm for my extension string and 52 for fileformat. It is working fine. Thanks for the help!