VBA Script fails when trying to save as .XLSX

Question

The following script has been written to open all .xlsm file types within a folder and save them as .xlsx types in a new directory but the script keeps failing when trying to save as .xlsx.

The error message returned is a run-time error '1004'

All attempts to remedy this have failed, any help is much appriciated and I thank you in advance.

Set fso = CreateObject("scripting.filesystemobject")
Set fils = fso.GetFolder("FILE LOCATION").Files

FldrPicker = "FILE LOCATION"
With FldrPicker
myPath = FldrPicker
End With
myExtension = "*.xlsm"
myfile = Dir(myPath & myExtension)

filepath = "NEW FILE LOCATION"

Do While myfile <> ""
Set wb = workbooks.Open(Filename:=myPath & myfile)
Application.DisplayAlerts = False
wb.SaveAs = myfile & ".xlsx"
wb.Close Savechanges:=True
Application.DisplayAlerts = True
Loop

`wb.SaveAs` can't be assigned a value, you need to pass it a parameter - i.e. `wb.SaveAs myfile & ".xlsx"` (Note: If you had opened a file called "abc.xlsm" from the "C:\Temp1\Temp2" directory, you will be creating a file called "abc.xlsm.xlsx" in the active directory, which is probably where the Excel application is stored - so you probably really want 'wb.SaveAs myPath & Left(myfile, Len(myfile) - 5) & ".xlsx"`) — YowE3K, Oct 21 '16 at 09:49
I have added the following the following parameters yet the same error is being returned. `teamname = Left(myfile, Len(myfile) - 5) & ".xlsx"` `Destination = filepath & teamname` `wb.SaveAs = Destination` — Paul, Oct 21 '16 at 10:10
If the error is being triggered due to the warning that `Excel` **really** doesn't like you trying to save a `macro enabled` workbook as a plain file, add a `Application.DisplayAlerts = False` to the start of your code and a `Application.DisplayAlerts = True` as the end to force the issue. — Skip Intro, Oct 21 '16 at 10:15
Please read @YowE3K answer. You are still assigning a value to `wb.SaveAs` — Zac, Oct 21 '16 at 10:32

score 1 · Accepted Answer · answered Oct 21 '16 at 18:07

Because SaveAs is a Method of the Workbook object, it can't be assigned a value, so you can't have a statement of the form:

    wb.SaveAs = .....

The MSDN documentation for SaveAs shows that it can be passed many parameters, either by position or by name:

expression .SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

expression A variable that represents a Workbook object.

One of the valid syntaxes for what you have written is:

    wb.SaveAs myfile & ".xlsx"

However, if you had opened a file called "abc.xlsm" from the "C:\Temp1\Temp2" directory, you will be creating a file called "abc.xlsm.xlsx" in the current directory, which is probably where the Excel application is stored - so you probably really want

    wb.SaveAs FileName:=myPath & Left(myfile, Len(myfile) - 5) & ".xlsx"

or possibly

    wb.SaveAs FileName:=myPath & Left(myfile, Len(myfile) - 5) & ".xlsx", _
              FileFormat:=xlOpenXMLWorkbook

to force the Save to be in a non-macro-enabled format.

But the main point to remember is ... you cannot assign a value to a Method.

VBA Script fails when trying to save as .XLSX

1 Answers1