Run time error 1004

4
votes

When trying to run the below I get the following error:

"This extension can not be used with the selected file type. Change the file extension in the file name text box or select a different file type by changing the save as type."

Code:

Dim strPath As String
Dim strFolderPath As String


strFolderPath = "Y:\

strPath = strFolderPath & _
Sheet1.Range("A1").Value & _
Sheet1.Range("B1").Value & ".xlsx"


ActiveWorkbook.SaveAs Filename:=strPath
2
vbaexcel
If the ActiveWorkbook contains macros, you need to SaveAs with the "xlsm" extension, and the argument FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled. – Doug Glancy

2 Answers

11
votes

The error means that the ActiveWorkbook is trying to save as a different file format then ".xlsx". To force it to save as .xlsx, you also have to pass the fileformat.

ActiveWorkbook.SaveAs Filename:=strPath, FileFormat:=xlOpenXMLWorkbook
0
votes

I had the same problem when I was trying to convert a macro enabled workbook (xlsm) into a normal workbook (xlsx)! I finally gave up using the ActiveWorkbook.SaveAs method and used the following code instead:

' Code from http://www.mrexcel.com/forum/excel-questions/516366-saving-xlsm-file-xlsx-using-visual-basic-applications.html#post4478019
sub saveAsXlsx
Dim mySheetList() As String
ReDim mySheetList(0 To (ThisWorkbook.Sheets.Count) - 1)
Dim a As Integer
a = 0
For Each ws In ActiveWorkbook.Worksheets
    mySheetList(a) = ws.Name
    a = a + 1
Next ws

'actually save
Worksheets(mySheetList).Copy
ActiveWorkbook.SaveAs fileName:=flenme 'default ext

The code is originally from here.