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
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.