Runtime Error '1004' - SaveAs method of workbook class failed

dvitale71

New Member
Joined
May 4, 2018
Messages
8
Hello,
I've been using this macro for months without issue but today I keep getting the same error. What the macro is doing is opening csv files and saving them as xlsx files. I have highlighted in Blue where the error occurs. I'm not sure how to fix... any help/guidance is much appreciated. Thanks everyone!

' Saves csv files as xls files
'
'
Public Sub csvToxls()
Dim FSO As Object
Dim folder As Object
Dim wb As Object

csvPath = "C:\Users\vitdom01\OneDrive\Desktop\ENVIRONMENT LIST\csv"
xlsPath = "C:\Users\vitdom01\OneDrive\Desktop\ENVIRONMENT LIST\xls"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set csvFolder = FSO.GetFolder(csvPath)

If FSO.FolderExists(xlsPath) = False Then
FSO.createFolder (xlsPath)
End If

Set xlsFolder = FSO.GetFolder(xlsPath)

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

For Each wb In csvFolder.Files
If LCase(Right(wb.Name, 3)) = "csv" Then
Set activeWB = Workbooks.Open(wb)
activeWB.SaveAs Filename:=xlsPath & "" & Left(activeWB.Name, Len(activeWB.Name) - 3) & "xlsx", FileFormat:=xlOpenXMLWorkbook
activeWB.Close True
End If
Next

With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With

' Mark as Complete
'
'
Range("E5").Select
ActiveCell.FormulaR1C1 = "Complete"
Range("E5").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12611584
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Range("E5").Select
End Sub
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
In my opinion the errored line should be
activeWB.SaveAs Filename:=xlsPath & "\" & Left(activeWB.Name, Len(activeWB.Name) - 3) & "xlsx", FileFormat:=xlOpenXMLWorkbook

Try...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,331
Messages
5,635,655
Members
416,870
Latest member
rikimon2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top