CODEWITHSUNDEEP
excelvba
david_10001
david_10001

Reputation: 492

Excel macro is diplaying 'Run-time error '1004'

I understand there are many other threads for this but I don't know VBA code that well and I can't seem to understand the solutions provided. I been able to copy an existing macro from the web and use it on my computer with Excel 2013 fine, but once I try it on another computer with 2003 I get this error pop up. (Run-time error '1004' Method 'SaveAs' of object'_Workbook' failed) Would someone be able to let me know what I need to change in the code?

Sub ConvertToXlsx()
    Dim strPath As String
    Dim strFile As String
    Dim wbk As Workbook
    ' Path must end in trailing backslash
    strPath = "T:\"
    strFile = Dir(strPath & "*.xls")
    Application.DisplayAlerts = False
    Do While strFile <> ""
        If Right(strFile, 3) = "xls" Then
            Set wbk = Workbooks.Open(Filename:=strPath & strFile)
            wbk.SaveAs Filename:=strPath & strFile & "x", _
                FileFormat:=xlOpenXMLWorkbook
            wbk.Close SaveChanges:=False
        End If
        strFile = Dir
    Loop
Application.DisplayAlerts = True
End Sub

Upvotes: 0

Views: 237

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

The issue is here:

FileFormat:=xlOpenXMLWorkbook.

Excel 2003 does not recognize (nor have) the xlOpenXMLWorkbook file format.

xlExcel8 should work. Directly from the link:

These are the main file formats in Excel 2007:

51 = xlOpenXMLWorkbook (without macro’s in 2007, .xlsx)

52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007, .xlsm)

50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro’s, .xlsb)

56 = xlExcel8 (97-2003 format in Excel 2007, .xls)

Upvotes: 2

Related Questions