Error trying to save Excel worksheet as CSV via Excel VBA macro

Question

Using Excel 2010 on Windows 7. I have a VBA macro that saves the first worksheet of an excel workbook (.xlsm) into a CSV file. This had mostly worked in the past. Recently, I get error messages per the picture below, which state "Run-time error '1004': Microsoft Excel cannot open or save any more documents because there is not enough available memory or disk space."

So a couple things:

A common suggested solution from my Google searching is to set the file's location as a Trusted Location. This did not work.
I have enough disk space. That can't be the issue.
I'm not sure exactly what is meant by "available memory," but if it in any way refers to the Physical Memory figure listed in Windows Task Manager, that figure is 75%. The ultimate CSV file itself tends to be about 1,500KB.
I am always able to save this worksheet as a CSV manually without encountering any error messages, but when I do it via this VBA macro, I get the error message.

Picture of error message

My excel VBA save-as-CSV macro:

Sub saveAsCSV()
Application.DisplayAlerts = False

ThisWorkbook.Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:="dummyfilename.csv", FileFormat:=xlCSV, CreateBackup:=True
ActiveWorkbook.Close

Application.DisplayAlerts = True
End Sub

Try replacing ThisWorkbook.Sheets("Sheet1").Copy with ThisWorkbook.Sheets("Sheet1").Activate — Darrell H
I suspect that the error is caused by the absence of a path in your SaveAs instruction. Add a path to the file name and see if the error persists. — Variatus
To clarify, I do have a path in my SaveAs instruction. I'm just using a dummy as I don't feel the need to use the actual path here, but I now understand how that's misleading. — demyx999
On which drive do you save your file? And on which drive is AppData? In case these are different did you check both for free space? — Storax

GMalc GMalc · Accepted Answer · 2019-03-15T19:11:41

Try this, you don't have to use Copy or Activate. If your code is in the workbook that you want to save as a workbook you can use ThisWorkbook. Kudos to @Variatus for identifying need for a path.

ThisWorkbook.Sheets("Sheet1").SaveAs ThisWorkbook.Path & "/" & "dummyfilename" & ".csv", FileFormat:=6

Error trying to save Excel worksheet as CSV via Excel VBA macro

1 Answers