2
votes

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

1 Answers

0
votes

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