CODEWITHSUNDEEP
vbaexcelfso
Paul
Paul

Reputation: 53

VBA Script fails when trying to save as .XLSX

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

Upvotes: 1

Views: 2046

Answers (1)

YowE3K
YowE3K

Reputation: 23974

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.

Upvotes: 1

Related Questions