PDA

View Full Version : [SOLVED] how do I change file extension on VBA that I already use



megha
05-08-2014, 08:19 AM
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
05-08-2014, 08:33 AM
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
05-08-2014, 08:51 AM
The macro recorder is your friend. After recording, view the FileFormat value. I doubt it will be xlNormal.

megha
05-08-2014, 09:40 AM
Thank you! I used xlsm for my extension string and 52 for fileformat. It is working fine. Thanks for the help!